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 |
---|---|
|
|
|
|
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
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 R2006aR2018b: 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.
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);
See Also
close
| database
| fetch
| setdbprefs
MATLAB Command
You clicked a link that corresponds to this MATLAB command:
Run the command by entering it in the MATLAB Command Window. Web browsers do not support MATLAB commands.
Select a Web Site
Choose a web site to get translated content where available and see local events and offers. Based on your location, we recommend that you select: .
You can also select a web site from the following list:
How to Get Best Site Performance
Select the China site (in Chinese or English) for best site performance. Other MathWorks country sites are not optimized for visits from your location.
Americas
- América Latina (Español)
- Canada (English)
- United States (English)
Europe
- Belgium (English)
- Denmark (English)
- Deutschland (Deutsch)
- España (Español)
- Finland (English)
- France (Français)
- Ireland (English)
- Italia (Italiano)
- Luxembourg (English)
- Netherlands (English)
- Norway (English)
- Österreich (Deutsch)
- Portugal (English)
- Sweden (English)
- Switzerland
- United Kingdom (English)