Documentation

This is machine translation

Translated by Microsoft
Mouse over text to see original. Click the button below to return to the English verison of the page.

exec

Execute SQL statement and open cursor

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

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

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 = 

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

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 = 

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

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 =

  cursor with properties:

        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)

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 for concatenation. The two inner marks 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)

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)

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 = 

  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)

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)

Related Examples

Input Arguments

collapse all

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

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

collapse all

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

Data Types: char

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

Database cursor, returned as a database cursor object. The properties of this object are different depending 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

Data

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

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

To throw error messages to the Command Window, use setdbprefs. Enter this code:

setdbprefs('Errorhandling','report');
curs = exec(conn,'select * invalidtablename')

To store error messages in curs.Message instead of sending them to the Command Window, use setdbprefs. Enter this code:

setdbprefs('Errorhandling','store');

Type

Database cursor object.

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

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

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.

To import data from the cursor object, run fetch. To get properties of the cursor object, use resultset and rsmd, and access the Statement property.

A cursor object stays open until you close it using the close function.

Limitations

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

  • The native ODBC interface is not supported 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.

More About

collapse all

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.
  • You can experience issues with text field formats in the Microsoft SQL Server database management system. The workarounds are to:

    • Convert fields of format NVARCHAR, TEXT, NTEXT, and VARCHAR to CHAR in the database.

    • Use sqlquery to convert data to VARCHAR. For example, run a sqlquery statement of the form 'select convert(varchar(20),field1) from table1'.

  • 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#')

See Also

| | |

Introduced before R2006a

Was this topic helpful?