Database Toolbox

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 functions:

• database
• exec
• fetch
• logintimeout
• ping

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

1. If you did not already do so, set up the data source SampleDB according to the directions in Setting Up a Data Source.
2. In MATLAB, set the maximum time, in seconds, you want to allow the MATLAB session to try to connect to a database. This prevents the MATLAB session from hanging up if a database connection fails.

Enter the function before you connect to a database.

Type

logintimeout(5)

to specify the maximum allowable connection time as 5 seconds. If you are using a JDBC connection, the function syntax is different - for more information, see logintimeout.

MATLAB returns

ans=
     5

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

3. Connect to the database - type

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

In this example, you define a MATLAB variable, conn, to be the returned connection object. This connection stays open until you close it with the close function.

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

If you are using a JDBC connection, the database function syntax is different. For more information, see the database reference page.

For a valid connection, MATLAB returns information about the connection object.

conn =
 
       Instance: 'SampleDB'
       UserName: ''
         Driver: []
            URL: []
    Constructor:[1x1
                  com.mathworks.toolbox.database.databaseConnect]
        Message: []
         Handle: [1x1 sun.jdbc.odbc.JdbcOdbcConnection]
        TimeOut: 5
     AutoCommit: 'on'
           Type: 'Database Object'

4. Check the connection status - type

ping(conn)

MATLAB returns status information about the connection, indicating that the connection was successful.

        DatabaseProductName: 'ACCESS'
    DatabaseProductVersion: '3.50.0000'
            JDBCDriverName: 'JDBC-ODBC Bridge (odbcjt32.dll)'
         JDBCDriverVersion: '1.1001 (04.00.4202)'
    MaxDatabaseConnections: 64
           CurrentUserName: 'admin'
               DatabaseURL: 'jdbc:odbc:SampleDB'
    AutoCommitTransactions: 'True'

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

curs = exec(conn, 'select country from customers')

In the exec function, conn is the name of the connection object. The second argument, select country from customers, is a valid SQL statement that selects the country column of data from the customers table.

The exec command returns a cursor object. In this example, you assign the MATLAB variable curs to the returned cursor object.

curs =
        Attributes: []
              Data: 0
    DatabaseObject: [1x1 database]
          RowLimit: 0
          SQLQuery: 'select country from customers'
           Message: []
              Type: 'Database Cursor Object'
         ResultSet: [1x1 sun.jdbc.odbc.JdbcOdbcResultSet]
            Cursor: [1x1 com.mathworks.toolbox.database.sqlExec]
         Statement: [1x1 sun.jdbc.odbc.JdbcOdbcStatement]
             Fetch: 0

The data in the cursor object is stored in a MATLAB cell array. Cell arrays support mixed data types.

6. Import data into MATLAB - type

curs = fetch(curs, 10)

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

• curs, the cursor object returned by exec.
• 10, the maximum number of rows you want to be returned by fetch. The RowLimit argument is optional. If RowLimit is omitted, MATLAB imports all remaining rows.

In this example, fetch reassigns the variable curs to the cursor object containing the rows of data returned by fetch. MATLAB returns information about the cursor object.

curs =
        Attributes: []
              Data: {10x1 cell}
    DatabaseObject: [1x1 database]
          RowLimit: 0
          SQLQuery: 'select country from customers'
           Message: []
              Type: 'Database Cursor Object'
         ResultSet: [1x1 sun.jdbc.odbc.JdbcOdbcResultSet]
            Cursor: [1x1 com.mathworks.toolbox.database.sqlExec]
         Statement: [1x1 sun.jdbc.odbc.JdbcOdbcStatement]
             Fetch: [1x1
                      com.mathworks.toolbox.database.fetchTheData]

The curs object contains an element, Data, that points to the rows of data in the array. You can tell that Data contains 10 rows and 1 column.

7. Display the Data element in the cursor object, curs. Assign the variable AA to the data element, curs.Data. Type.

AA = curs.Data

MATLAB returns

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

For more information about working with data in MATLAB cell arrays, see Working with Cell Arrays in MATLAB.

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(curs)
close(conn)

About Objects and Methods for the Database Toolbox

Viewing Information About the Imported Data