Learn more about Database Toolbox

Importing Data from Databases into the MATLAB Workspace

This example imports country data from the customers table in the Nwind sample database into the MATLAB workspace using the following functions:

• database

• exec

• fetch (cursor.fetch)

• logintimeout

• ping

• setdbprefs

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

1. Before you connect to a database, set the maximum time that you want to allow the MATLAB software session to try to connect to a database to 5 seconds.

   logintimeout(5)
   ans=
        5
   

   Note   If you are connecting to a database using a JDBC connection, you must use different function syntax in this step. For more information, see the logintimeout function reference page.

2. Use the database function to define a MATLAB variable, conn, to represent the returned connection object. Pass the following arguments to this function:

   • The name of the database, SampleDB

   • The username and password, which are specified as empty strings because SampleDB does not require a user name or password

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

   Enter conn at the command prompt to see the data.

   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'
   

   Note   If you are connecting to a database using a JDBC connection, you need to specify different syntax for the database function. For more information, see the database reference page.

3. Use ping to check that the database connection status is successful.

   ping(conn)
   DatabaseProductName: 'ACCESS'
       DatabaseProductVersion: '04.00.0000'
               JDBCDriverName: 'JDBC-ODBC Bridge (odbcjt32.dll)'
            JDBCDriverVersion: '2.0001 (04.00.6200)'
       MaxDatabaseConnections: 64
              CurrentUserName: 'admin'
                  DatabaseURL: 'jdbc:odbc:SampleDB'
   AutoCommitTransactions: 'True'
   

4. Use the exec function to open a cursor and execute an SQL statement. Pass the following arguments to exec:

   • conn, the name of the connection object

   • select country from customers, a SQL statement that selects the country column of data from the customers table

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

   The exec function returns the MATLAB variable curs.

   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
   

5. The returned data contains strings, so you must convert it to a format that supports strings. Use setdbprefs to specify the format cellarray:

   setdbprefs('DataReturnFormat','cellarray')
   

6. Import data into the MATLAB workspace using the fetch function. Pass the following arguments to this function:

   • curs, the cursor object returned by exec

   • 10, the maximum number of rows you want fetch to return

   curs = fetch(curs, 10)
   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 contains the rows of data in the cell array.

   The Attributes field is always empty. To view cursor attributes, use the attr function.

7. Assign the data element, curs.Data to the variable AA to display the Data element of curs:

   AA = curs.Data
   AA = 
       'Germany'
       'Mexico'
       'Mexico'
       'UK'
       'Sweden'
       'Germany'
       'France'
       'Spain'
       'France'
       'Canada'
   

8. To import more rows of data, rerun fetch until you retrieve all data.

9. Continue with the next example. To stop working now and resume working on the next example at a later time, close the cursor and the connection as follows:

   close(curs)
   close(conn)
   

Getting Started with Database Toolbox Functions

Viewing Information About Imported Data

© 1984-2009- The MathWorks, Inc.    -   Site Help   -   Patents   -   Trademarks   -   Privacy Policy   -   Preventing Piracy   -   RSS