Main Content

exec

(Not recommended) Execute SQL statement and open cursor

collapse all in page

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.

example

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

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

collapse all

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

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

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);

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

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

ValueDescription

'forward_only'

Create a basic cursor.

'scrollable'

Create a scrollable cursor.

Output Arguments

collapse all

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

collapse all

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.

See Also

| | | |

Topics

External Websites