Tutorial (Database Toolbox User's Guide)

Database Toolbox User's Guide

Exporting Data from MATLAB to a New Record in a Database

In this part of the tutorial, you retrieve a set of data, perform a simple calculation on the data using MATLAB, and export the results as a new record to another table in the database. Specifically, you retrieve freight costs from an orders table, calculate the average freight cost, put the data into a cell array to export it, and then export the data (the average freight value and the number of shipments on which the average was based) to an empty table.

You use these Database Toolbox commands:

get
insert

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

1.: Create a table in Microsoft Access into which you will export MATLAB results.

: Open the Northwind database in Access. Create a new table called Avg_Freight_Cost that has two columns, Calc_Date and Avg_Cost. For the Calc_Date field, use the default Data Type, which is Text, and for the Avg_Cost field, set the Data Type to Number. Be sure to close the table; Access then warns you that there is no primary key, but you do not need one.

: If you need more information about how to create a table in Access, see Microsoft Access help or written documentation.

Note
Although Access supports the use of spaces in table and column names, most other databases do not. Therefore the Database Toolbox does not allow spaces in table and column names so do not include them. Also, be sure not to name columns using the database's reserved words, such as DATE, or you will not be able to import data into the database - see Access help for a list of Access reserved words.

2.: If you are continuing directly from the previous part of the tutorial, skip this step. Otherwise, connect to the data source, SampleDB. Type:

connA = database('SampleDB', '', '')

3.: In MATLAB, import the data on which you will perform calculations. Specifically, import the freight column of data from the orders table. To keep the example simple, import only three rows of data. Type:

cursorA = exec(connA, 'select freight from orders');
cursorA = fetch(cursorA, 3)

4.: View the data you imported - type:

AA = cursorA.data

: MATLAB returns:

AA = 
    [32.3800]
    [11.6100]
    [65.8300]

5.: Calculate the average freight cost. First, assign the variable name rowsA to the number of rows in the array. Then convert the cell array to a vector and calculate the average, assigning the result to the variable meanA.

rowsA = rows(cursorA);
meanA = sum([AA{:}])/rowsA

meanA =
   36.6067

6.: Assign the variable D to the date on which these orders were shipped - type:

D = '1/20/98'

D =
1/20/98

7.: Create a cell array that will contain the data you are exporting. This example assigns the name C to the cell array and defines C as being 1 row by 2 columns.

C = cell(1,2)

C = 
     []     []

8.: Assign the date to the first cell and the mean to the second cell.

C(1,1) = {D}

C = 
    '1/20/98'    []

C(1,2) = {meanA}

C = 
    '1/20/98'    [36.6067]

9.: Define the names of the columns to which you will be exporting data. In this example, the columns names are those in the Access Avg_Freight_Cost table you created earlier, Calc_Date and Avg_Cost. Assign the variable colnames to the cell array containing the column names. Type:

colnames = {'Calc_Date','Avg_Cost'}

colnames = 
    'Calc_Date'    'Avg_Cost'

10.: Before you export data from MATLAB, determine the current status of the autocommit flag for the database. The status of the autocommit flag determines if the database data will be automatically committed or not - if the flag is off, you can undo an update.

: Verify the status of the autocommit flag using the get command - type:

get(connA, 'autocommit')

ans =
on

: The autocommit flag is set to on so exported data will be automatically committed. In this example, keep the autocommit flag on; for a Microsoft Access database, this is the only option.

11.: Export the data into the Avg_Freight_Cost table. For this example, type:

insert(connA, 'Avg_Freight_Cost', colnames, C)

: connA is the connection structure for the database to which you are exporting data. In this example, the database to which you are exporting data is SampleDB, for which there is already an open connection, connA. However, if you are exporting to a different database, use the database command to connect to it before exporting the data.

: Avg_Freight_Cost is the name of the table to which you are exporting data. In the insert command, you also include the colnames cell array and the cell array containing the data you are exporting, C, both of which you defined in the previous steps.

: insert appends the data as a new record at the end of the Avg_Freight_Cost table.

: If you get the following error, it is because the table is open in design mode in Access. Close the table in Access and repeat the insert command.

??? Error using ==> cursor/cursor
[Microsoft][ODBC Microsoft Access 7.0 Driver] Table
'Avg_Freight_Cost' is exclusively locked by user '' on machine ''

12.: In Microsoft Access, view the Avg_Freight_Cost table to verify the results.

: Note that the Avg_Cost value was rounded to a whole number to match the properties of that field in Access.

13.: Close the cursor - type:

close(cursorA)

: Always close a cursor when you are finished with it to avoid using memory unnecessarily and to ensure there are enough available cursors for other users.

14.: At this point, you can go to the next part of the tutorial. If you want to stop working on the tutorial now and resume with the next part at a later time, close the connection. Type:

close(connA)

: Do not delete or change the Avg_Freight_Cost table in Access because you will use it in the next part of the tutorial.

[ Previous | Help Desk | Next ]