Exporting Multiple New Records from MATLAB :: Using Functions in the Database Toolbox (Database Toolbox)

Database Toolbox

Exporting Multiple New Records from MATLAB

In this example, you import multiple records, manipulate the data in MATLAB, and then export it to a different table in the database. Specifically, you import sales figures for all products, by month, into MATLAB. Then you compute the total sales for each month. Finally, you export the monthly totals to a new table.

You learn to use these Database Toolbox functions:

insert
setdbprefs

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

If you did not already do so, set up the data source dbtoolboxdemo according to the directions in Setting Up a Data Source. This data source uses the tutorial database.
Check the properties of the tutorial database to be sure it is writable, that is, not read only.
Connect to the database by typing

conn = database('dbtoolboxdemo', '', '');

You define the returned connection object as conn. You do not need a username or password to access the dbtoolboxdemo database.

Specify preferences for the retrieved data by using the setdbprefs function. Set the data return format to numeric and specify that any NULL value read from the database is to be converted to a 0 in MATLAB.

setdbprefs...
({'NullNumberRead';'DataReturnFormat'},{'0';'numeric'})

Note that when you specify DataReturnFormat as numeric, the value for NullNumberRead must also be numeric, such as 0. For example, it cannot be a string, such as NaN.

Import the sales figures. Specifically, import all data from the salesVolume table. Type

curs = exec(conn, 'select * from salesVolume');
curs = fetch(curs);

To get a sense of the data you imported, view the column names in the fetched data set. Type

```
columnnames(curs)
```

MATLAB returns

ans =
    'StockNumber', 'January', 'February', 'March', 'April',
    'May', 'June', 'July', 'August', 'September', 'October',
    'November', 'December'

To get a sense of what the data is, view the data for January, which is in column 2. Type

```
curs.Data(:,2)
```

MATLAB returns

Get the size of the matrix containing the fetched data set, assigning the dimensions to m and n. In a later step, you use these values to compute the monthly totals. Type

```
[m,n] = size(curs.Data)
```

MATLAB returns
- ```
m =
    10
n =
    13
```

Compute the monthly totals by typing

for i = 2:n
    tmp = curs.Data(:,i);
    monthly(i-1,1) = sum(tmp(:));
end

where tmp is the sales volume for all products in a given month i, and monthly is the total sales volume of all products for the month i.

For example, when i is 2, row 1 of monthly is the total of all rows in column 2 of curs.Data, where column 2 is the sales volume for January.

To see the result, type
- ```
monthly
```
MATLAB returns
- ```
       25100
       15621
       14606
       11944
        9965
        8643
        6525
        5899
        8632
       13170
       48345
      172000
```

Create a string array containing the column names into which you are inserting the data. In a later step, we insert the data into the salesTotal column of the yearlySales table. The yearlySales table contains no data. Here we assign the array to the variable colnames. Type

```
colnames{1,1} = 'salesTotal';
```

Insert the data into the yearlySales table by typing

insert(conn, 'yearlySales', colnames, monthly)

View the yearlySales table in the tutorial database to be sure the data was imported correctly.
Close the cursor and database connection. Type

```
close(curs)
close(conn)
```

Replacing Existing Data in a Database from MATLAB Retrieving BINARY or OTHER Java SQL Data Types