Exporting Data from the MATLAB Workspace to a New Record in a Database :: Using Database Toolbox Functions (Database Toolbox™)

Exporting Data from the MATLAB Workspace to a New Record in a Database

This example does the following:

Retrieves freight costs from an orders table.
Calculates the average freight cost and records the date on which the calculation was made.
Stores this data in a cell array.
Exports this data to an empty table.

You learn to use the following Database Toolbox functions:

For more information on these functions, see matlab\toolbox\database\dbdemos\dbinsertdemo.m.

Connect to the data source, SampleDB, if needed:
```
conn = database('SampleDB', '', '');
```
Use setdbprefs to set the format for retrieved data to numeric:
```
setdbprefs('DataReturnFormat','numeric')
```

Import three rows of data the freight column of data from the orders table.

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

Assign the data to the MATLAB workspace variable AA:

AA = curs.Data
AA =
   32.3800
   11.6100
   65.8300

Calculate average freight cost and assign the number of rows in the array to numrows:
```
numrows = rows(curs);
```
Calculate the average of the data and assign the result to the variable meanA:
```
meanA = sum(AA(:))/numrows
meanA =
   36.6067
```
Assign the date on which the calculation was made to the variable D:
```
D = '20-Jan-2002';
```
Assign the date and mean to a cell array to export to a database. Put the date in the first cell of exdata:
```
exdata(1,1) = {D}
exdata = 
		'20-Jan-2002'
```
Put the mean in the second cell of exdata:
```
exdata(1,2) = {meanA}
exdata = 
    '20-Jan-2002'    [36.6067]
```
Define the names of the columns to which to export data. In this example, the column names are Calc_Date and Avg_Cost, from the Avg_Freight_Cost table in the SampleDB database. Assign the cell array containing the column names to the variable colnames:
```
colnames = {'Calc_Date','Avg_Cost'};
```
Use the get function to determine the current status of the AutoCommit database flag. This status determines whether the exported data is automatically committed to the database. If the flag is off, you can undo an update; if it is on, data is automatically committed to the database.
```
get(conn, 'AutoCommit')
ans =
	on
```
The AutoCommit flag is set to on, so the exported data is automatically committed to the database.
Use the fastinsert function to export the data into the Avg_Freight_Cost table. Pass the following arguments to this function:
- conn, the connection object for the database
- Avg_Freight_Cost, the name of the table to which you are exporting data
- The cell arrays colnames and exdata
```
fastinsert(conn, 'Avg_Freight_Cost', colnames, exdata)
```
fastinsert appends the data as a new record at the end of the Avg_Freight_Cost table.
In Microsoft Access, view the Avg_Freight_Cost table to verify the results.

The Avg_Cost value was rounded to a whole number to match the properties of that field in Access.
Close the cursor.
```
close(curs)
```
Continue with the next example. To stop now and resume working with the next example at a later time, close the connection.
```
close(conn)
```

Viewing Information About Imported Data

Replacing Existing Data in Databases with Data Exported from the MATLAB Workspace

Recommended Products

Get MATLAB trial software

Includes the most popular MATLAB recorded presentations with Q&A sessions led by MATLAB experts.

Get the Interactive Kit