Documentation

Export 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.

This example assumes that you are connecting to a Microsoft® Access™ database that contains tables named salesVolume and yearlySales. The table salesVolume contains the column names for each month. The table yearlySales contains the column named salesTotal.

To access the code for this example, see matlab\toolbox\database\dbdemos\dbinsert2demo.m.

  1. Create a database connection conn to the Microsoft Access database using the JDBC/ODBC bridge. Here, this code assumes that you are connecting to a data source named dbtoolboxdemo with blank user name and password.

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

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

  2. Ensure that the database is writable using conn.

    a = isreadonly(conn)
    a =
        0

    When the isreadonly function returns 0, the database is writable.

  3. Specify preferences for the retrieved data. Set the data return format to numeric. 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 be numeric.

  4. Execute the SQL query string sqlquery using conn to import all data from the salesVolume table. The cursor object curs contains the executed query. Import the data from the executed query using the fetch function.

    sqlquery = 'select * from salesVolume';
    
    curs = exec(conn,sqlquery);
    curs = fetch(curs);
    
  5. Display the names of the columns in the fetched data set.

    columnnames(curs)
    
    ans =
    
    	'StockNumber', 'January', 'February', 'March', 'April', 
    	'May', 'June', 'July', 'August', 'September', 'October', 
    	'November', 'December'
    
  6. Display the data for January. January data is in the second column of the fetched data set.

    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. Calculate monthly totals using m and n. 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
    
  9. Display the monthly totals.

    monthly
    
    ans = 
    
     25100
     15621
     14606
     11944
     9965
     8643
     6525
     5899
     8632
     13170
     48345
     172000
    
  10. Create a cell array colnames containing the column name for inserting the data.

    colnames{1,1} = 'salesTotal';
    
  11. Insert the data into the yearlySales table using conn, colnames, and the monthly totals monthly.

    datainsert(conn,'yearlySales',colnames,monthly)
    
  12. To verify the data import in Microsoft Access, view the yearlySales table from the tutorial database.

  13. After you finish working with the cursor object, close it. Close the database connection.

    close(curs)
    close(conn)
    

See Also

| | | | |

Related Examples

More About

Was this topic helpful?