Exporting Multiple Records from the MATLAB Workspace

This example does the following:

  1. Imports monthly sales figures for all products from the dbtoolboxdemo data source 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:

For details about these functions, see matlab\toolbox\database\dbdemos\dbinsert2demo.m.

  1. Ensure that the dbtoolboxdemo data source 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', '', '');
    

    Alternatively, you can use the native ODBC interface for an ODBC connection. For details, see database.

  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, in Microsoft® Access™, view the yearlySales table from the tutorial database.

  12. Close the cursor and the database connection.

    close(curs)
    close(conn)
    
Was this topic helpful?