Documentation

exec

Execute SQL statement and open cursor

To perform database operations on a SQLite database file created using the MATLAB® Interface to SQLite, use the first syntax. To query data using the MATLAB Interface to SQLite, use fetch with the SQLite connection as the first input argument. To query data and perform database operations using other database connections, use the other syntaxes. For details, see Working with the MATLAB Interface to SQLite.

Syntax

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

Description

example

exec(conn,sqlquery) performs database operations by executing the SQL statement sqlquery for the SQLite connection conn.

example

curs = exec(conn,sqlquery) executes the SQL statement sqlquery for the database connection conn and returns the cursor object curs.

example

curs = exec(conn,sqlquery,qTimeOut) executes the SQL statement with a timeout value qTimeOut.

example

curs = exec(conn,sqlquery,Name,Value) executes the SQL statement and creates a scrollable cursor.

Examples

collapse all

Select Data Using the Native ODBC Interface

Create a database connection conn to the Microsoft® Access™ database. For example, the following code assumes that you are connecting to a data source named dbtoolboxdemo with admin as the user name and password.

conn = database.ODBCConnection('dbtoolboxdemo','admin','admin');

Select data from productTable that you access using the database.ODBCConnection object, conn. Assign the SQL statement to the variable sqlquery. Assign the returned cursor object to the variable curs.

sqlquery = 'select * from productTable';
curs = exec(conn,sqlquery)
curs = 

  ODBCCursor with properties:

         Data: 0
     RowLimit: 0
     SQLQuery: 'select * from productTable'
      Message: []
         Type: 'ODBCCursor Object'
    Statement: [1x1 database.internal.ODBCStatementHandle]

With the native ODBC interface, exec returns curs as a database.ODBCCursor object instead of a Database Cursor Object.

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

close(curs)

Select Data Using a Scrollable Cursor

Using a MySQL® database, select data from a table that you access using the native ODBC database connection conn and create a scrollable cursor.

Connect to the MySQL database. This code assumes that you are connecting to a data source named MySQL with user name username and password pwd.

conn = database.ODBCConnection('MySQL','username','pwd');

Select all rows from the productTable table and create a scrollable cursor. Assign the SQL statement to the variable sqlquery. Assign the returned cursor object to the variable curs.

sqlquery = 'select * from productTable';
curs = exec(conn,sqlquery,'cursorType','scrollable')
curs = 

  ODBCCursor with properties:

         Data: 0
     RowLimit: 0
     SQLQuery: 'select * from productTable'
      Message: []
         Type: 'ODBCCursor Object'
    Statement: [1x1 database.internal.ODBCStatementHandle]

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

curs.Scrollable
ans =

     1

The Scrollable property equals 1 when the cursor is scrollable.

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

close(curs)

Select Data with a Timeout Value

Using the dbtoolboxdemo data source, select data from productTable that you access using the database connection conn with a timeout of 10 seconds. The timeout value specifies the maximum amount of time exec tries to execute the SQL statement. Assign the SQL statement to the variable sqlquery. Assign the returned cursor object to the variable curs.

sqlquery = 'select * from productTable';
curs = exec(conn,sqlquery,10)
curs =
 
        Attributes: []
              Data: 0
    DatabaseObject: [1x1 database]
          RowLimit: 0
          SQLQuery: 'select * from productTable'
           Message: []
              Type: 'Database Cursor Object'
         ResultSet: [1x1 sun.jdbc.odbc.JdbcOdbcResultSet]
            Cursor: [1x1 com.mathworks.toolbox.database.sqlExec]
         Statement: [1x1 sun.jdbc.odbc.JdbcOdbcStatement]
             Fetch: 0

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

close(curs)

Use a Variable in a Query

Using the dbtoolboxdemo data source, select data from the productTable table that you access using the database connection conn, where productdesc is a variable. In this example, you are prompted to specify the product description. Your input is assigned to the variable productdesc.

productdesc = input('Enter your product description: ', 's')

The following prompt appears.

Enter your product description:

Type the following into the Command Window.

Train Set

To perform the query using your input, run the following code. The cursor object curs contains the executed query. Import the data from the executed query using the fetch function.

sqlquery = ['select * from productTable'...
'where productDescription = ' '''' productdesc '''']; 
curs = exec(conn,sqlquery);
curs = fetch(curs);
curs.Data
ans = 

    [8]    [212569]    [1001]    [5]    'Train Set'

The select statement is created by using square brackets to concatenate the two character vectors select * from productTable where productDescription = and 'productdesc'. To create the pair of single quotation marks that appears in the SQL statement, specify the pair of four quotation marks around productdesc. The outer two marks delineate the next character vector to concatenate, and two marks are required inside them to denote a quotation mark inside a character vector.

Perform the query without a variable.

sqlquery = ['select * from productTable'... 
'where productDescription = ' '''Engine Kit'''];
curs = exec(conn,sqlquery);
curs = fetch(curs);
curs.Data
ans = 

    [7]    [389123]    [1007]    [16]    'Engine Kit'

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

close(curs)

Limit Maximum Number of Rows to Return

Create a database connection conn to the MySQL database using the native ODBC interface. Here, this code assumes that you are connecting to an ODBC data source named MySQL with user name username and password pwd.

conn = database.ODBCConnection('MySQL','username','pwd');

Select data from the table productTable. To limit the maximum number of rows, set the name-value pair argument 'maxRows' to 10.

sqlquery = 'select * from productTable order by productNumber';

curs = exec(conn,sqlquery,'maxRows',10);

exec retrieves only 10 rows from the database server.

Display the returned data curs.Data.

curs = fetch(curs); 

curs.Data
ans = 

    [ 1]    [400345]    [1001]    [14]    'Building Blocks'
    [ 2]    [400314]    [1002]    [ 9]    'Painting Set'   
    [ 3]    [400999]    [1009]    [17]    'Slinky'         
    [ 4]    [400339]    [1008]    [21]    'Space Cruiser'  
    [ 5]    [400455]    [1005]    [ 3]    'Tin Soldier'    
    [ 6]    [400876]    [1004]    [ 8]    'Sail Boat'      
    [ 7]    [389123]    [1007]    [16]    'Engine Kit'     
    [ 8]    [212569]    [1001]    [ 5]    'Train Set'      
    [ 9]    [125970]    [1003]    [13]    'Victorian Doll' 
    [10]    [888652]    [1006]    [24]    'Teddy Bear'  

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

close(curs)

Call a Stored Procedure Without Input and Output Arguments

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

Define a stored procedure named create_table that creates a table named test_table by executing this code. This procedure has no input or output arguments. This 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 user name username and password pwd.

conn = database.ODBCConnection('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 = 

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

Create a Table Using the MATLAB Interface to SQLite

Create a SQLite connection conn to a new SQLite database file tutorial.db.

dbfile = '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)

Related Examples

Input Arguments

collapse all

conn — Database connectiondatabase connection object | SQLite connection object

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

sqlquery — SQL statementcharacter vector

SQL statement, specified as a character vector.

Data Types: char

qTimeOut — Timeout valuescalar

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: 'cursorType','scrollable'

'cursorType' — Cursor type'scrollable'

Cursor type, specified as 'scrollable' that creates a scrollable cursor. For details, see Importing Data Using a Scrollable Cursor.

Data Types: char

'maxRows' — Maximum number of rows to returnscalar

Maximum number of rows to return, specified as a nonnegative scalar value. Before exec runs the SQL query, the maximum number of rows to return is set using this name-value pair argument. For details about this option and other memory management options, see Managing Memory to Import Data.

Data Types: double

Output Arguments

collapse all

curs — Database cursordatabase cursor object

Database cursor, returned as a database cursor object. The properties of this object are different based on the database connection object.

For a JDBC/ODBC bridge or a JDBC driver database connection, the cursor object has the following properties.

Property

Description

Attributes

Not used.

Data

Contains the resulting data after executing fetch.

DatabaseObject

Database connection object or database.ODBCConnection object that opened the cursor object.

RowLimit

Number of rows to fetch at a time.

SQLQuery

SQL statement to execute.

Message

Contains the error messages generated from executing the SQL statement. If this property is empty, then the SQL statement executed successfully.

Type

Database cursor object or database.ODBCCursor object type.

ResultSet

Java® result set object.

Cursor

Internal Java representation of a cursor object.

Statement

Java statement object.

Fetch

Internal Java representation of the fetched data.

Scrollable

Contains a logical value that identifies the cursor object as scrollable or basic. This property is set to 1 for a scrollable cursor and 0 otherwise. This property is hidden and read only.

Position

Contains a number that specifies the current position of the cursor in the data set. This property is only available for a scrollable cursor. This property behaves differently for native ODBC, JDBC, and different database drivers. This property is read only.

For a native ODBC connection, the cursor object has only these properties from the previous list: Data, RowLimit, SQLQuery, Message, Type, Statement, Scrollable, and Position.

Limitations

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

  • The native ODBC interface is not supported using Microsoft Access. Connect to the database using the JDBC/ODBC bridge.

  • 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.

See Also

| |

Introduced before R2006a

Was this topic helpful?