exec

(Not recommended) Execute SQL statement and open cursor

The exec function is not recommended. For SQL statements that return data, use the fetch function or the select function instead. For other SQL statements, use the execute function instead. For details, see Version History.

The scrollable cursor functionality has no replacement.

Syntax

curs = exec(conn,sqlquery)

curs = exec(conn,sqlquery,Name,Value)

curs = exec(conn,sqlquery,qTimeOut)

Description

curs = exec(conn,sqlquery) creates the cursor object after executing the SQL statement sqlquery for the database connection conn.

curs = exec(conn,sqlquery,Name,Value) specifies options using one or more name-value pair arguments. For example, 'MaxRows',10 limits the number of rows to return to 10 before SQL query execution.

curs = exec(conn,sqlquery,qTimeOut) uses the timeout value qTimeOut for SQL query execution.

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, this 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)

Input Arguments

conn — Database connection

connection object

Database connection, specified as an ODBC connection object or JDBC connection object created using the database function.

sqlquery — SQL query

string scalar | character vector

SQL query, specified as a string scalar or character vector. You can specify the SQL query as a nested query or as a stored procedure.

For information about the SQL query language, see the SQL Tutorial on the W3Schools website.

Example: {CALL getSupplierInfo('New York")}

Data Types: string | char

qTimeOut — Timeout value

numeric scalar

Timeout value, specified as a numeric scalar denoting the maximum amount of time, in seconds, that exec tries to execute the SQL statement sqlquery.

Data Types: double

Name-Value Arguments

Specify optional pairs of arguments as Name1=Value1,...,NameN=ValueN, where Name is the argument name and Value is the corresponding value. Name-value arguments must appear after other arguments, but the order of the pairs does not matter.

Before R2021a, use commas to separate each name and value, and enclose Name in quotes.

Example: curs = exec(conn,sqlquery,'MaxRows',rowlimit);

MaxRows — Maximum number of rows to return

positive numeric scalar

Maximum number of rows to return before executing the SQL query, specified as the comma-separated pair consisting of 'MaxRows' and a positive numeric scalar. By default, the exec function returns all rows from the executed SQL query. Use this name-value pair argument to limit the number of rows imported into MATLAB from the SQL query execution. For details about this option and other memory management options, see Data Import Memory Management.

Data Types: double

CursorType — Cursor type

'forward_only' (default) | 'scrollable'

Cursor type, specified as the comma-separated pair consisting of 'CursorType' and one of the values in this table.

Value	Description
'forward_only'	Create a basic cursor.
'scrollable'	Create a scrollable cursor.

Output Arguments

curs — Database cursor

cursor object

Database cursor, returned as a cursor object.

Limitations

The name-value pair argument 'MaxRows' has these limitations:

• If you are using Microsoft Access^®, the native ODBC interface is not supported.

• Not all database drivers support setting the maximum number of rows before query execution. For an unsupported driver, modify your SQL query to limit the maximum number of rows to return. The SQL syntax varies with the driver. For details, consult the driver documentation.

Tips

• The order of records in your database does not remain constant. Sort data using the SQL ORDER BY command in your sqlquery statement.

• For Microsoft Excel^®, tables in sqlquery are Excel worksheets. By default, some worksheet names include a $ symbol. To select data from a worksheet with this name format, use an SQL statement of the form SELECT * FROM "Sheet1$" (or 'Sheet1$').

• Before you modify database tables, ensure that the database is not open for editing. If you try to edit the database while it is open, you receive this MATLAB error:

  [Vendor][ODBC Driver] The database engine could not lock 
  table 'TableName' because it is already in use by 
  another person or process.

• The PostgreSQL database management system supports multidimensional fields, but SQL SELECT statements fail when retrieving these fields unless you specify an index.

• Some databases require that you include a symbol, such as #, before and after a date in a query, as follows:

  curs = exec(conn,'SELECT * FROM mydb WHERE mydate > #03/05/2005#')

Alternative Functionality

App

The exec function executes SQL statements using the command line. To execute SQL statements interactively, use the Database Explorer app.

Version History

Introduced before R2006a

R2018b: exec function is not recommended

The exec function is not recommended. For SQL statements that return data, use the fetch function with the connection object or the select function instead. For other SQL statements, use the execute function instead. Some differences between the workflows might require updates to your code.

There are no plans to remove the exec function 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);

You can also import data in one step using the select function.

data = select(conn,selectquery);

The scrollable cursor functionality has no replacement.