Database Toolbox™

Exporting Multiple Records from the MATLAB Workspace

This example does the following:

1. Imports monthly sales figures for all products from the tutorial database into the MATLAB workspace.

2. Computes total sales for each month.

3. Exports the totals to a new table.

You use the following Database Toolbox functions:

• fastinsert

• setdbprefs

For more information on these functions, see matlab\toolbox\database\dbdemos\dbinsert2demo.m.

1. Ensure that the tutorial database is writable, that is, not read-only.

2. Use the database function to connect to the data source, assigning the returned connection object as conn. Pass the following arguments to this function:

   • dbtoolboxdemo, the name of the data source

   • username and password, which are passed as empty strings because no user name or password is required to access the database

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

3. Use the setdbprefs function to specify preferences for the retrieved data. Set the data return format to numeric and specify that NULL values read from the database are converted to 0 in the MATLAB workspace.

   setdbprefs... 
   ({'NullNumberRead';'DataReturnFormat'},{'0';'numeric'})
   

   When you specify DataReturnFormat as numeric, the value for NullNumberRead must also be numeric.

4. Import data from the salesVolume table.

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

5. Use columnnames to view the column names in the fetched data set:

   columnnames(curs)
   ans =
   	'StockNumber', 'January', 'February', 'March', 'April', 
   	'May', 'June', 'July', 'August', 'September', 'October', 
   	'November', 'December'
   

6. View the data for January (column 2).

   curs.Data(:,2)
   ans =
           1400
           2400
           1800
           3000
           4300
           5000
           1200
           3000
           3000
              0
   

7. Assign the dimensions of the matrix containing the fetched data set to m and n.

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

8. Use m and n to compute monthly totals. The variable tmp is the sales volume for all products in a given month c. The variable monthly is the total sales volume of all products for that month. For example, if c 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.

   for c = 2:n
   	tmp = curs.Data(:,c);
   	monthly(c-1,1) = sum(tmp(:));
   end
   

   View the result.

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

9. Create a string array containing the column names into which you want to insert the data, and assign the array to the variable colnames.

   colnames{1,1} = 'salesTotal';
   

10. Use fastinsert to insert the data into the yearlySales table:

    fastinsert(conn, 'yearlySales', colnames, monthly)
    

11. To verify that the data was imported correctly, view the yearlySales table in the tutorial database.

    

12. Close the cursor and the database connection.

    close(curs)
    close(conn)
    

Replacing Existing Data in Databases with Data Exported from the MATLAB Workspace

Retrieving BINARY or OTHER Sun Java SQL Data Types