Exporting Data from MATLAB to a New Record in a Database :: 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, and 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.

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

Note You can use the Visual Query Builder GUI instead of functions to export data from MATLAB to new rows in a database. See Visual Query Builder for details.

You learn to use these Database Toolbox functions:

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';
```

For more information about working with strings in MATLAB, see Characters and Strings in the MATLAB Programming documentation.

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 (edit mode for other databases). Close the table in Access and repeat the insert function. For example, the error might be
- ```
[Vendor][ODBC Product Driver] The database engine could not lock 
table 'TableName' because it is already in use by another person 
or process.
```

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 from MATLAB