cursor

(Not recommended) Database cursor

The cursor object is not recommended. Use the fetch function instead. For details, see Version History.

The scrollable cursor functionality has no replacement.

Description

After connecting to a relational database using either ODBC or JDBC drivers, you can perform actions using the database connection. To import data into MATLAB^® from a database and perform database operations, you must create a cursor object. Database Toolbox™ uses this object to retrieve rows from database tables and execute SQL statements.

There are two types of database cursors, basic and scrollable. Basic cursors let you import data in an SQL query in a sequential way. However, scrollable cursors enable data import from a specified offset in the data set.

To import data quickly using a SQL SELECT statement, use the select function. To import data with full functionality, use the exec and fetch functions. For differences, see Data Import Using Database Explorer App or Command Line.

A cursor object stays open until you close it using the close function.

Creation

Create a cursor object using the exec function.

Properties

ODBC and JDBC Driver Properties

Data — SQL query results

cell array (default) | table | structure | numeric | dataset

SQL query results, specified as a cell array, table, structure, numeric, or dataset array. After running the exec function, this property is blank. The fetch function populates this property with imported data from the executed SQL query.

To set the data return format, use the setdbprefs function.

Note

The dataset array value will be removed in a future release. Use table instead.

Example: [15×5 table]

Data Types: double | struct | table | cell

RowLimit — Number of rows to import

0 (default) | positive numeric scalar

This property is read-only.

Number of rows to import at a time, specified as a positive numeric scalar.

Data Types: double

SQLQuery — SQL query

character vector

This property is read-only.

SQL query, specified as a character vector. To change the SQL query, create a cursor object and specify the SQL query in the input argument sqlquery of the exec function.

For information about the SQL query language, see the SQL Tutorial.

Example: 'SELECT * FROM productTable'

Data Types: char

Message — Error message

'' (default) | character vector

This property is read-only.

Error message, specified as a character vector. An empty character vector specifies that the exec or fetch functions executed successfully. If this property is empty after running exec, then the SQL statement executed successfully. If this property is empty after running fetch, then the data import completed successfully. Otherwise, the property populates with the returned error message.

To throw error messages to the Command Window, use the setdbprefs function. Enter this code:

setdbprefs('ErrorHandling','report');
sqlquery = 'SELECT * FROM invalidtablename';
curs = exec(conn,sqlquery)

To store error messages in the Message property instead, enter this code:

setdbprefs('ErrorHandling','store');
sqlquery = 'SELECT * FROM invalidtablename';
curs = exec(conn,sqlquery)

Example: 'Table 'schame.InvalidTableName' doesn't exist'

Data Types: char

Type — Database cursor type

'ODBCCursor Object' | 'Database Cursor Object'

This property is read-only.

Database cursor type, specified as one of these values.

Value	Database Cursor Type
'ODBCCursor Object'	cursor object created using an ODBC database connection
'Database Cursor Object'	cursor object created using a JDBC database connection

Statement — Statement

C statement object | Java^® statement object

This property is read-only.

Statement, specified as a C statement object or Java statement object.

Example: [1×1 com.mysql.jdbc.StatementImpl]

Scrollable — Scrollable cursor

0 (default) | 1

This property is read-only.

Scrollable cursor, specified as a logical value. The value 0 identifies the cursor object as basic. The value 1 identifies the cursor object as scrollable.

Note

This property is hidden.

Data Types: logical

Position — Cursor position

0 (default) | numeric scalar

This property is read-only.

Cursor position of a scrollable cursor in the data set, specified as a numeric scalar. Only scrollable cursors have this property. The cursor position behaves differently depending on the database driver used to establish the database connection.

Data Types: double

JDBC Driver Properties

DatabaseObject — JDBC connection

connection object

This property is read-only.

JDBC connection, specified as a connection object created by connecting to a database using the JDBC driver.

Example: [1×1 database.jdbc.connection]

ResultSet — Result set

Java result set object

This property is read-only.

Result set, specified as a Java result set object.

Example: [1×1 com.mysql.jdbc.JDBC4ResultSet]

Cursor — Database cursor

Java object

This property is read-only.

Database cursor, specified as an internal Java object that represents the cursor object.

Example: [1×1 com.mathworks.toolbox.database.sqlExec]

Fetch — Imported data

Java object

This property is read-only.

Imported data, specified as an internal Java object that represents the imported data.

Example: [1×1 com.mathworks.toolbox.database.fetchTheData]

Object Functions

close	(Not recommended) Close cursor
fetch	(Not recommended) Import data into MATLAB workspace from database cursor
get	(To be removed) Retrieve object properties
isopen	(Not recommended) Determine if database cursor is open

Examples

Select Data Using Native ODBC Driver

Use a native ODBC connection to import product data from a Microsoft^® SQL Server^® database into MATLAB. Then, determine the highest unit cost among products.

Create an ODBC database connection to a Microsoft SQL Server database with Windows^® authentication. Specify a blank user name and password. The database contains the table productTable.

datasource = 'MS SQL Server Auth';
conn = database(datasource,'','');

Check the database connection. If the Message property is empty, the connection is successful.

conn.Message

ans =

     []

Select all data from the table productTable using the connection object. Assign the SQL SELECT statement to the variable sqlquery. The cursor object contains the executed SQL query.

sqlquery = 'SELECT * FROM productTable';
curs = exec(conn,sqlquery)

curs = 

   cursor with properties:

         Data: 0
     RowLimit: 0
     SQLQuery: 'SELECT * FROM productTable'
      Message: []
         Type: 'ODBCCursor Object'
    Statement: [1×1 database.internal.ODBCStatementHandle]

For an ODBC connection, the Type property contains ODBCCursor Object. For JDBC connections, the Type property contains Database Cursor Object.

Import data from the table into MATLAB.

curs = fetch(curs);
data = curs.Data;

Determine the highest unit cost in the table.

max(data.unitCost)

ans =

    24

After you finish working with the cursor object, close it. Close the database connection.

close(curs)
close(conn)

Version History

Introduced before R2006a

R2018b: cursor object is not recommended

The cursor object is not recommended. Use the fetch function instead. Some differences between the workflows might require updates to your code.

There are no plans to remove the cursor object at this time.

Update Code

Use the fetch function with the connection object to import data from a database in one step.

In prior releases, you wrote multiple lines of code to create the cursor object and import data. For example:

curs = exec(conn,sqlquery);
curs = fetch(curs);
results = curs.Data;
close(curs)

Now you can import data in one step using the fetch function.

results = fetch(conn,sqlquery);