Database Toolbox

Exporting Multiple Records from MATLAB

In this example, multiple records are imported, manipulated in MATLAB, and then exported to a database. Specifically, you import sales figures for all products, by month, into MATLAB. Then you compute the total sales for each month. Finally, you export the monthly totals to a new table.

You use these Database Toolbox functions:

• insert
• setdbprefs

If you want to see or copy the functions for this part of the tutorial, or if you want to run the set of functions, use the M-file matlab\toolbox\database\dbdemos\dbinsert2demo.m.

1. If you did not already do so, set up the data source dbtoolboxdemo according to the directions in Setting Up a Data Source. This data source uses the tutorial database.
2. Check the properties of the tutorial database to be sure it is writable, that is, not read-only.
3. Connect to the database - type

conn = database('dbtoolboxdemo', '', '');

You define the returned connection object as conn. You do not need a username or password to access the dbtoolboxdemo database.

4. Specify that any NULL value read from the database will be converted to a 0 in MATLAB by using the setdbprefs command.

setdbprefs ('NullNumberRead','0')

5. Import the sales figures. Specifically, import all data from the salesVolume table. Type

curs = exec(conn, 'select * from salesVolume');
curs = fetch(curs);

6. To get a sense of the data you imported, view the column names in the fetched data set - type

columnnames(curs)

MATLAB returns

ans =
    'Stock Number', 'January', 'February', 'March', 'April',
    'May', 'June', 'July', 'August', 'September', 'October',
    'November', 'December'

7. To get a sense of what the data is, view the data for January, which is in column 2 - type

curs.Data(:,2)

MATLAB returns

ans =
    [1400]
    [2400]
    [1800]
    [3000]
    [4300]
    [5000]
    [1200]
    [3000]
    [3000]
    [   0]

8. Get the size of the cell array containing the fetched data set, assigning the dimensions to m and n. In a later step, you use these values to compute the monthly totals. Type

[m,n] = size(curs.Data)

MATLAB returns

m =
    10
n =
    13

9. Compute the monthly totals - type

for i = 2:n
    tmp = curs.Data(:,i)
    monthly(i-1,1) = sum([tmp{:}]);
end

where tmp is the sales volume for all products in a given month i, and monthly is the total sales volume of all products for the month i. To compute monthly using sum, first convert tmp from a cell array to a numeric array using [tmp{:}] because sum will only work on numeric arrays.

For example, when i is 2, row 1 of monthly is the total of all rows in column 2 of curs.Data, where column 2 is the sales volume for January.

To see the result, type

monthly

MATLAB returns

       25100
       15621
       14606
       11944
        9965
        8643
        6525
        5899
        8632
       13170
       48345
      172000

10. To export the column of data, you must first convert it to a cell array - type

exdata = num2cell(monthly);

num2cell takes the data in monthly and assigns each row to a row in a new cell array, exdata, which you will export in a later step.

11. Create a string array containing the column names into which you are inserting the data. In a later step, we will insert the data into the salesTotal column of the yearlySales table; here we assign the variable colnames to the array. Type

colnames{1,1} = 'salesTotal';

12. Insert the data into the yearlySales table - type

insert(conn, 'yearlySales', colnames, exdata)

13. View the yearlySales table in the tutorial database to be sure the data was imported correctly.
    
    
14. Close the cursor and database connection. Type

close(curs)
close(conn)

Exporting Data from MATLAB, Replacing Existing Data in a Database

Accessing Metadata