Importing Data into MATLAB from a Database :: Using Functions in the Database Toolbox (Database Toolbox)

Database Toolbox

Importing Data into MATLAB from a Database

In this example, 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 Nwind sample database.

Note You can use the Visual Query Builder GUI instead of functions to import data from a database. See Visual Query Builder for details.

In this section, you learn to use these Database Toolbox functions:

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

If you did not already do so, set up the data source SampleDB according to the directions in Setting Up a Data Source.
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.

Connect to the database by typing

```
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'
```

Check the connection status by typing

```
ping(conn)
```

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

       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'

Open a cursor and execute an SQL statement by typing

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 function returns a cursor object. In this example, you assign the returned cursor object to 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

The data in the cursor object is stored in MATLAB.

If MATLAB displays an error, the query syntax might be invalid. See Data Retrieval Restrictions for more information.

Specify the format of retrieved data by typing

setdbprefs('DataReturnFormat','cellarray')

In this example, the returned data contains strings so the data format must support strings, which cellarray does. If the returned data contains only numerics or if the nonnumeric data is not relevant, you could instead specify the numeric format, which uses less memory.

Import data into MATLAB by typing

```
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. When importing large quantities of data, rather than importing all the rows at once, import the data using multiple fetches and include the rowlimit argument to improve speed and memory usage.
In this example, fetch reassigns the cursor object containing the rows of data returned by fetch to the variable curs. 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 in turn contains the rows of data in the cell array. You can tell that Data contains 10 rows and 1 column.

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

```
AA = curs.Data
```

MATLAB returns
- ```
AA = 
    'Germany'
    'Mexico'
    'Mexico'
    'UK'
    'Sweden'
    'Germany'
    'France'
    'Spain'
    'France'
    'Canada'
```
Now you can use MATLAB to perform operations on the returned data. For more information, see Working with Cell Arrays in MATLAB. For more about working with strings, see Characters and Strings in the MATLAB Programming documentation.

If the returned data includes Java BINARY or OTHER data types, you might have to process the data before using it. See Retrieving BINARY or OTHER Java SQL Data Types for instructions to use this type of data.

At this point, you can go to the next example. If you want to stop working now and resume with the next example at a later time, close the cursor and the connection. Type

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

Using Functions in the Database Toolbox Viewing Information About the Imported Data