cursor

(Not recommended) Database cursor

expand all in page

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

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

expand all

ODBC and JDBC Driver Properties

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

This property is read-only.

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

Data Types: double

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

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

This property is read-only.

Database cursor type, specified as one of these values.

ValueDatabase Cursor Type

'ODBCCursor Object'

cursor object created using an ODBC database connection

'Database Cursor Object'

cursor object created using a JDBC database connection

This property is read-only.

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

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

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

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

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]

This property is read-only.

Result set, specified as a Java result set object.

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

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]

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

attr(Not recommended) Retrieve attributes of columns in fetched data set
close(Not recommended) Close cursor
cols(Not recommended) Retrieve number of columns in fetched data set
columnnames(Not recommended) Retrieve names of columns in fetched data set
fetch(Not recommended) Import data into MATLAB workspace from database cursor
fetchmulti(Not recommended) Import data from multiple result sets
get(Not recommended) Retrieve object properties
isopen(Not recommended) Determine if database cursor is open
querytimeout(Not recommended) Get time specified for SQL queries to succeed
rows(Not recommended) Return number of rows in fetched data set
set(Not recommended) Set properties for database cursor
width(Not recommended) Return field size of column in fetched data set

Examples

collapse all

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)

Compatibility Considerations

expand all

Not recommended starting in R2018b

See Also

| | |

Introduced before R2006a

Database Toolbox Documentation

Support