Using Functions in the Database Toolbox (Database Toolbox)

Database Toolbox

Exporting Data from MATLAB to a New Record in a Database

In this example, 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. Then export the data (the average freight cost and the date the calculation was made) to an empty table.

You learn to use these Database Toolbox functions:

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

Check the properties of the northwind database to be sure it is writable, that is, not read only.
Open the northwind database in Microsoft Access.
Create a new table 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.
Save the table as Avg_Freight_Cost and close it. Access warns you that there is no primary key, but you do not need one. If you do designate a primary key, you can only run the example once because Access prevents you from inserting the same record twice.

If you need more information about how to create a table in Access, see Microsoft Access help or written documentation. Also, refer to the Note for information about the use of spaces in table and column names and the use of reserved words.

After creating the table in Access, if you want to run a set of functions similar to this example, use the M-file matlab\toolbox\database\dbdemos\dbinsertdemo.m.

If you are continuing from the previous example Viewing Information About the Imported Data, skip this step. Otherwise, connect to the data source, SampleDB. Type

```
conn = database('SampleDB', '', '');
```

In MATLAB, set the format for retrieved data to numeric by typing

setdbprefs('DataReturnFormat','numeric')

In this example, the returned data will contain only a column of numbers so the data format can be numeric, which is needed to perform calculations on the data.

Import the data on which you want to 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);

View the data you imported by typing

```
AA = curs.Data
```

MATLAB returns
- ```
AA =
   32.3800
   11.6100
   65.8300
```

Calculate the average freight cost. First, assign the number of rows in the array to the variable numrows. Then calculate the average, assigning the result to the variable meanA. Type

numrows = rows(curs);
meanA = sum(AA(:))/numrows

MATLAB returns
- ```
meanA =
   36.6067
```

Assign the date on which this calculation was made to the variable D by typing

```
D = '20-Jan-2002';
```

Assign the date and mean to a cell array, which you will export to the database. A cell array is required because the date information is a string. Unlike importing data, you do not specify the export format using setdbprefs, but instead use standard MATLAB operations to define it. Put the date in the first cell by typing

```
exdata(1,1) = {D}
```

MATLAB returns

```
    exdata = 
        '20-Jan-2002'
```

Put the mean in the second cell by typing

```
exdata(1,2) = {meanA}
```

MATLAB returns

    exdata = 
    '20-Jan-2002'    [36.6067]

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

```
colnames = {'Calc_Date','Avg_Cost'};
```

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 by typing
- ```
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.

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. Note that you do not define the type of data you are exporting; the data is exported in its current MATLAB format.

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

If you get an error, it may be because the table is open in design mode in Access. Close the table in Access and repeat the insert function.

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.

Close the cursor by typing

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

At this point, you can go to the next example. If you want to stop working now and resume with the next example 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 example.

Viewing Information About the Imported Data Replacing Existing Data in a Database MATLAB