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.

exec

Execute SQL statement and open cursor

Syntax

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

Description

example

exec(conn,sqlquery) performs database operations on a SQLite database file by executing the SQL statement sqlquery for the SQLite connection conn using the MATLAB® interface to SQLite.

example

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

example

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

example

curs = exec(conn,sqlquery,qTimeOut) uses a 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.

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)

Use an ODBC connection with a timeout value 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. Specify a timeout value of 10 seconds. The timeout value specifies the maximum amount of time the exec function tries to execute the SQL SELECT statement. Assign the SQL SELECT statement to the variable sqlquery. The cursor object contains the executed SQL query.

sqlquery = 'SELECT * FROM productTable';
qTimeOut = 10;
curs = exec(conn,sqlquery,qTimeOut)
curs = 

  cursor with properties:

         Data: 0
     RowLimit: 0
     SQLQuery: 'SELECT * FROM productTable'
      Message: []
         Type: 'ODBCCursor Object'
    Statement: [1×1 database.internal.ODBCStatementHandle]

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)

Use an ODBC connection to import product data from a Microsoft® SQL Server® database into MATLAB® using a variable in the SQL SELECT statement.

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, then the connection is successful.

conn.Message
ans =

     []

Select data from productTable by specifying the product description as a variable. The cursor object contains the executed query. Import the data from the executed query using the fetch function.

The SQL SELECT statement uses square brackets to concatenate the two character vectors. To create the pair of single quotation marks that appears in the SQL SELECT statement, specify the pair of four quotation marks around productdesc. The outer two marks delineate the next character vector for concatenation. The two inner marks denote a quotation mark inside a character vector.

productdesc = 'Painting Set';
sqlquery = ['SELECT * FROM productTable ' ...
    'WHERE productDescription = ' '''' productdesc ''''];
curs = exec(conn,sqlquery);
curs = fetch(curs);
curs.Data
ans =

  1×5 table

    productNumber    stockNumber    supplierNumber    unitCost    productDescription
    _____________    ___________    ______________    ________    __________________

    2                4.0031e+05     1002              9           'Painting Set'    

Close the cursor object before executing another SQL statement.

close(curs)

Instead of a variable, use the character vector 'Slinky' to import data.

sqlquery = ['SELECT * FROM productTable ' ...
    'WHERE productDescription = ' '''Slinky'''];
curs = exec(conn,sqlquery);
curs = fetch(curs);
curs.Data
ans =

  1×5 table

    productNumber    stockNumber    supplierNumber    unitCost    productDescription
    _____________    ___________    ______________    ________    __________________

    3                4.01e+05       1009              17          'Slinky'          

After you finish working with the cursor object, close it. Close the database connection.

close(curs)
close(conn)

Use an ODBC connection to import a limited number of rows of 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 productTable. Specify the maximum number of rows to return as two rows.

sqlquery = 'SELECT * FROM productTable ORDER BY productNumber';
rowlimit = 2;
curs = exec(conn,sqlquery,'MaxRows',rowlimit);

Display the returned data.

curs = fetch(curs);
data = curs.Data
data =

  2×5 table

    productNumber    stockNumber    supplierNumber    unitCost    productDescription
    _____________    ___________    ______________    ________    __________________

    1                4.0035e+05     1001              14          'Building Blocks' 
    2                4.0031e+05     1002               9          'Painting Set'    

Determine the highest unit cost in the limited data set.

max(data.unitCost)
ans =

    14

After you finish working with the cursor object, close it. Close the database connection.

close(curs)
close(conn)

Using a Microsoft® SQL Server® database, run a stored procedure using the native ODBC database connection conn.

Define a stored procedure create_table that creates a table named test_table by executing the following code. This procedure has no input or output arguments. The code assumes that you are using a Microsoft SQL Server database.

CREATE PROCEDURE create_table 
	
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

CREATE TABLE test_table
	 (
		CATEGORY_ID     INTEGER     IDENTITY PRIMARY KEY,
		CATEGORY_DESC   CHAR(50)    NOT NULL
        );
	
END
GO

Connect to the Microsoft SQL Server database. This code assumes that you are connecting to a data source named MS SQL Server with a user name and password.

conn = database('MS SQL Server','username','pwd');

Call the stored procedure create_table. Assign the returned cursor object to the variable curs.

curs = exec(conn,'create_table')
curs = 

  cursor with properties:

         Data: 0
     RowLimit: 0
     SQLQuery: 'create_table'
      Message: []
         Type: 'ODBCCursor Object'
    Statement: [1x1 database.internal.ODBCStatementHandle]

The empty Message property means the stored procedure completed successfully.

After you finish working with the cursor object, close it.

close(curs)

Use a scrollable cursor object 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 and create a scrollable cursor 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,'CursorType','scrollable')
curs = 

  cursor with properties:

         Data: 0
     RowLimit: 0
     SQLQuery: 'SELECT * FROM productTable'
      Message: []
         Type: 'ODBCCursor Object'
    Statement: [1×1 database.internal.ODBCStatementHandle]

To verify that the exec function creates a scrollable cursor, display the hidden Scrollable property of the cursor object.

curs.Scrollable
ans =

  logical

   1

The Scrollable property equals 1 when the database cursor is scrollable.

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 MATLAB® Interface to SQLite, create a table in a new SQLite database file.

Create a SQLite connection conn to a new SQLite database file tutorial.db. Specify the file name in the current working folder.

dbfile = fullfile(pwd,'tutorial.db');

conn = sqlite(dbfile,'create');

Create the table inventoryTable using exec.

createInventoryTable = ['create table inventoryTable ' ...
    '(productNumber NUMERIC, Quantity NUMERIC, ' ...
    'Price NUMERIC, inventoryDate VARCHAR)'];

exec(conn,createInventoryTable)

inventoryTable is an empty table in tutorial.db.

Close the SQLite connection.

close(conn)

Input Arguments

collapse all

Database connection, specified as a connection object or sqlite object created using the database or sqlite functions.

SQL statement, specified as a character vector. 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 this function. For procedures that return output arguments, use runstoredprocedure.

Data Types: char

Timeout value, specified as a scalar denoting the maximum amount of time in seconds 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 single quotes (' '). You can specify several name and value pair arguments in any order as Name1,Value1,...,NameN,ValueN.

Example:

collapse all

Maximum number of rows to return before executing the SQL query, specified as a 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 Approaches and Memory Management.

Data Types: double

Cursor type, specified as a comma-separated pair consisting of 'CursorType' and one of these values.

ValueDescription

'forward_only'

Create a basic cursor.

'scrollable'

Create a scrollable cursor.

For details, see Using Scrollable Cursors.

Output Arguments

collapse all

Database cursor, returned as a cursor object.

Limitations

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

  • The native ODBC interface is not supported if you are using Microsoft Access™.

  • 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 is not constant. To sort records, use the SQL statement ORDER BY.

  • 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

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

Introduced before R2006a

Was this topic helpful?