Tutorial (Database Toolbox User's Guide)

Database Toolbox User's Guide

Importing Data into MATLAB from a Database

In this part of the tutorial, you connect to and import data from a database. Specifically, you connect to the SampleDB data source and then import country data from the customers table in the Northwind sample database. You use these Database Toolbox commands:

If you want to see or copy the commands for this part of the tutorial, or if you want to run the set of commands, use the M-file matlab\toolbox\database\dbdemos\dbimportdemo.m.

1.: If you did not already do so, set up the data source according to the directions in "Setting Up a Data Source".

: Follow the instructions marked "For the tutorial." The data source you create, SampleDB, is used for the tutorial. SampleDB uses Northwind, a sample database distributed with Microsoft Access.

2.: In MATLAB, set the maximum time, in seconds, that will be allowed for the MATLAB session to connect to a database successfully. This prevents the MATLAB session from hanging up if a database connection fails.

: You must enter the command before you connect to a database.

: Type

logintimeout(5)

ans=
     5

: to specify the maximum allowable connection time as five seconds. If you are using a JDBC connection, the command syntax is different - for more information, see logintimeout in Chapter 4.

: When you use the database command in the next step to connect to the database, MATLAB tries to make the connection. If it cannot connect in five seconds, it stops trying.

3.: Connect to a database - type:

connA = database('SampleDB', '', '')

: In this example, you define a MATLAB variable, connA, to be the returned connection structure. This connection stays open until you close it with the close command.

: For the database command, you provide the name of the database, which is the data source SampleDB for this example. The other two arguments for the database command are username and password. For this example, they are empty strings because the SampleDB database does not require a user name or password.

: If you are using a JDBC connection, the command syntax is different - for more information, see the database reference pages.

4.: Check the connection status - type:

ping(connA)

: MATLAB returns information about the connection, indicating that the connection was successful:

Database Product Name     : ACCESS
Database Product Version  : 3.0
JDBC Driver Name          : JDBC-ODBC Bridge (odbcjt32.dll)
JDBC Driver Version       : 1.1001 (3.40.2829)
Max. Database Connections : 64
Current User Name         : admin
Database URL              : jdbc:odbc:SampleDB
Auto Commit Transactions  : True

5.: Open a cursor and execute an SQL statement - type:

cursorA = exec(connA, 'select country from customers')

: A cursor structure is returned and stored in a MATLAB cell array (cell arrays support mixed data types). In this example, you assign the MATLAB variable cursorA to the returned cursor structure.

: In the exec command, connA is the name of the connection structure. The second argument, select country from customers, is a valid SQL statement that selects the country column of data from the customers table.

6.: Import data into MATLAB - type:

cursorA = fetch(cursorA, 10)

: fetch is the command that imports data. It has the following two arguments in this example:

cursorA, the cursor structure returned by exec.
10, the maximum number of rows you want to be returned by fetch. The rowlimit argument is optional and if not included, MATLAB imports all remaining rows.

: In this example, fetch reassigns the variable cursorA to the cursor structure containing the rows of data returned by fetch.

7.: Display the data element in the cell array for cursorA - type:

AA = cursorA.data

AA = 
    'Germany'
    'Mexico'
    'Mexico'
    'UK'
    'Sweden'
    'Germany'
    'France'
    'Spain'
    'France'
    'Canada'

: The cursorA cell array contains an element, data, that points to the rows of data in the array. Here you assign the variable AA to the data element, cursorA.data.

8.: 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 cursor and the connection. Type:

close(cursorA)
close(connA)

[ Previous | Help Desk | Next ]