Database Toolbox

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 functions:

• get
• insert

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\dbinsertdemo.m.

1. Create a table in Microsoft Access into which you will export MATLAB results.
1. Check the properties of the Northwind database to be sure it is writable, that is, not read-only.
2. Open the Northwind database in Microsoft Access.
3. Create a new table called Avg_Freight_Cost that has two columns, Calc_Date and Avg_Cost.
4. 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.
   
   
5. Close the table. Access 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. For Access, see Access help to determine the 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

conn = 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

curs = exec(conn, 'select freight from orders');
curs = fetch(curs, 3);

4. View the data you imported - type

AA = curs.Data

MATLAB returns

AA = 
    [12.7500]
    [10.1900]
    [52.8400]

5. Calculate the average freight cost. First, assign the variable name numrows to the number of rows in the array. Then convert the cell array AA to a vector and calculate the average, assigning the result to the variable meanA. Divide the sum by numrows, but note that you must convert numrows to a double precision value because the divide operator, /, requires it. Type

numrows = rows(curs);
meanA = sum([AA{:}])/double(numrows)

MATLAB returns

meanA =
   25.2600

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

D = '1/20/98';

7. Assign the date and mean to a cell array, which will be exported to the database. Put the date in the first cell by typing

exdata(1,1) = {D}

MATLAB returns

    exdata = 
        '1/20/98'

Put the mean in the second cell by typing

exdata(1,2) = {meanA}

MATLAB returns

    exdata = 
    '1/20/98'    [25.2600]

8. Define the names of the columns to which you will be exporting data. In this example, the columns names are those in the 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'};

9. 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 function - type

get(conn, 'AutoCommit')

MATLAB returns

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.

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

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

where conn is the connection object for the database to which you are exporting data. In this example, conn is SampleDB, which is already open. However, if you export to a different database that is not open, use the database function 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 function, you also include the colnames cell array and the cell array containing the data you are exporting, exdata, both of which you defined in the previous steps.

Running 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 function.

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

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

12. Close the cursor - type

close(curs)

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.

13. 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(conn)

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

Viewing Information About the Imported Data

Exporting Data from MATLAB, Replacing Existing Data in a Database