exec

Execute SQL statement and open cursor

Syntax

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

Description

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

expand all

Select Data from a Database Table Using the Native ODBC Interface

Create a connection conn using the native ODBC interface and the dbtoolboxdemo data source.

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 a database.ODBCCursor object instead of a Database Cursor Object.

After finishing with the cursor object, close it.

close(curs)

Select Data from a Database Table

Using the dbtoolboxdemo data source, select data from the suppliers table that you access using the database connection, conn. Assign the SQL statement to the variable sqlquery. Assign the returned cursor object to the variable curs.

sqlquery = 'select City from suppliers';
curs = exec(conn,sqlquery)
curs =
 
        Attributes: []
              Data: 0
    DatabaseObject: [1x1 database]
          RowLimit: 0
          SQLQuery: 'select City from suppliers'
           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 finishing with the cursor object, close it.

close(curs)

Select Data from a Database Table 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 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 finishing with the cursor object, close it.

close(curs)

Select Data from a Database Table 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 finishing 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 MATLAB® Command Window.

Train Set

To perform the query using your input, run the following code.

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 strings select * from productTable where productDescription = and 'productdesc'. The pairs of four quotation marks are needed to create the pair of single quotation marks that appears in the SQL statement around productdesc. The outer two marks delineate the next string to concatenate, and two marks are required inside them to denote a quotation mark inside a string.

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 finishing with the cursor object, close it.

close(curs)

Roll Back and Commit Data in a Database

Use exec to roll back and commit data after running fastinsert, insert, or update for which the AutoCommit flag is off.

Roll back data for the database connection conn.

sqlquery = 'rollback';
exec(conn,sqlquery);

When you do not specify an output argument, MATLAB returns the results of calling exec into cursor object ans. Assign ans to variable curs so that MATLAB does not overwrite the cursor object. After finishing with the cursor object, close it.

curs = ans;
close(curs)

Commit the data.

sqlquery = 'commit';
exec(conn,sqlquery);

After finishing with the cursor object, close it.

curs = ans;
close(curs)

Change the Database Connection Catalog

Change the catalog for the database connection conn to intlprice.

sqlquery = 'Use intlprice';
curs = exec(conn,sqlquery);

After finishing with the cursor object, close it.

close(curs)

Create a Table and Add a New Column

Use the SQL CREATE command to create the table.

sqlquery = ['CREATE TABLE Person(LastName varchar, '...
        'FirstName varchar,Address varchar,Age int)'];

Create the table for the database connection object conn.

exec(conn,sqlquery);

Use the SQL ALTER command to add a new column, City, to the table.

sqlquery = 'ALTER TABLE Person ADD City varchar(30)'; 
curs = exec(conn,sqlquery);

After finishing with the cursor object, close it.

close(curs)

Input Arguments

expand all

conn — Database connectionconnection object

Database connection, specified as a database connection object created using database.

sqlquery — SQL statementSQL string

SQL statement, specified as an SQL string to execute.

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 an enumerated string 'scrollable' that creates a scrollable cursor. For details, see Importing Data Using a Scrollable Cursor.

Data Types: char

Output Arguments

expand 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 to identify 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.

Was this topic helpful?