Documentation

This is machine translation

Translated by Microsoft
Mouseover text to see original. Click the button below to return to the English verison of the page.

Note: This page has been translated by MathWorks. Please click here
To view all translated materals including this page, select Japan from the country navigator on the bottom of this page.

cursor

Database cursor

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. For details, see Using Scrollable Cursors.

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. For details, see Using Scrollable Cursors.

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

attrRetrieve attributes of columns in fetched data set
closeClose and invalidate database and driver resource utilizer
colsRetrieve number of columns in fetched data set
columnnamesRetrieve names of columns in fetched data set
fetchImport data into MATLAB workspace from database cursor or from execution of SQL statement
fetchmultiImport data from multiple result sets
get(To be removed) Retrieve object properties
isopenDetermine if database connection or database cursor is open
querytimeoutGet time specified for SQL queries to succeed
rowsReturn number of rows in fetched data set
set(To be removed) Set properties for database or cursor object
widthReturn field size of column in fetched data set

Examples

expand 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.

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

Check the database connection. If the Message property is empty, then 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]

With the native ODBC interface, the Type property contains the character vector ODBCCursor Object. For JDBC connections, this property contains the character vector 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)

Using the cursor object and JDBC driver, import product data from a MySQL® database into MATLAB. Then, determine the highest unit cost among products.

Create a JDBC database connection using the 'Vendor' name-value pair argument in the database function to specify connecting to a MySQL database. Specify a user name and password. Specify the database server name using the 'Server' name-value pair argument.

conn = database('dbname','username','pwd','Vendor','MySQL', ...
    'Server','sname');

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:

        Attributes: []
              Data: 0
    DatabaseObject: [1×1 database.jdbc.connection]
          RowLimit: 0
          SQLQuery: 'SELECT * FROM productTable'
           Message: []
              Type: 'Database Cursor Object'
         ResultSet: [1×1 com.mysql.jdbc.JDBC4ResultSet]
            Cursor: [1×1 com.mathworks.toolbox.database.sqlExec]
         Statement: [1×1 com.mysql.jdbc.StatementImpl]
             Fetch: 0

For JDBC connections, the Type property contains Database Cursor Object. For ODBC connection, this property contains ODBCCursor 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)

Introduced before R2006a

Was this topic helpful?