This is machine translation

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

Note: This page has been translated by MathWorks. Click here to see
To view all translated materials including this page, select Country from the country navigator on the bottom of this page.

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 Compatibility Considerations.

The scrollable cursor functionality has no replacement.

Syntax

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

Description

example

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

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 a connection object created with the database function.

SQL statement, specified as a character vector or string scalar. The SQL statement can be any valid SQL statement, including nested queries. The SQL statement can be a stored procedure, such as {call sp_name (parm1,parm2,...)}. For stored procedures that return one or more result sets, use exec function. For procedures that return output arguments, use runstoredprocedure.

For information about the SQL query language, see the SQL Tutorial.

Data Types: char | string

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 Pair Arguments

Specify optional comma-separated pairs of Name,Value arguments. Name is the argument name and Value is the corresponding value. Name must appear inside quotes. You can specify several name and value pair arguments in any order as Name1,Value1,...,NameN,ValueN.

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.

Compatibility Considerations

expand all

Not recommended starting in R2018b

Introduced before R2006a