Database Toolbox™

exec - Execute SQL statement and open cursor

GUI Alternatives

Query databases using Visual Query Builder. For more information on Visual Query Builder, see Using Visual Query Builder.

Syntax

curs = exec(conn, 'sqlquery')

Description

curs = exec(conn, 'sqlquery') executes the SQL statement sqlquery for the database connection conn, and opens a cursor.

Running exec returns the cursor object to the variable curs and returns additional information about the cursor object. The sqlquery argument can be a stored procedure for that database connection, of the form {call sp_name (parm1,parm2,...)}.

Remarks

After opening a cursor, use fetch to import data from the cursor. Use resultset, rsmd, and statement to get properties of the cursor.
Use querytimeout to specify the maximum amount of time for which exec tries to execute the SQL statement.
You can have multiple cursors open at one time.
A cursor stays open until you close it using the close function.
Unless noted in this reference page, the exec function supports all valid SQL statements, such as nested queries.
The order of records in your database is not constant. Use values in column names to identify records. Use the SQL ORDER BY command to sort records.
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 the following MATLAB^® error:
```
[Vendor][ODBC Driver] The database engine could not lock 
table 'TableName' because it is already in use by 
another person or process.
```
For Microsoft^® Excel^®, tables in sqlquery are Excel^® worksheets. By default, some worksheet names include $. To select data from a worksheet with this name format, use a SQL statement of the form: select * from "Sheet1$" (or 'Sheet1$') .
You may experience issues with text field formats in the Microsoft^® SQL Server™ database management system. Workarounds for these issues include:
- Converting fields of format NVARCHAR, TEXT, NTEXT, and VARCHAR to CHARin the database.
- Usingsqlquery 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. For example:
```
curs = exec(conn,'select * from mydb where mydate > #03/05/2005#')
```

Examples

Example 1 — Select Data from a Database Table

Select data from the customers table that you access using the database connection conn. Assign the returned cursor object to the variable curs.

curs = exec(conn, 'select * from customers')
curs =
    Attributes: []
          Data: 0
DatabaseObject: [1x1 database]
      RowLimit: 0
      SQLQuery: 'select * from customers'
       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

Example 2 — Select One Column of Data from Database Table

Select country data from the customers table that you access using the database connection conn. Assign the SQL statement to the variable sqlquery and assign the returned cursor to the variable curs.

sqlquery = 'select country from customers';
curs = exec(conn, sqlquery);

Example 3 — Use a Variable in a Query

Select data from the customers table that you access using the database connection conn, where country is a variable. In this example, you are prompted to specify your country. Your input is assigned to the variable UserCountry.
```
UserCountry = input('Enter your country: ', 's')
```
You are prompted as follows:
```
Enter your country:
```
Enter:
```
Mexico
```
To perform the query using your input, run:
```
curs = exec(conn, ...
		['select * from customers where country= ' '''' UserCountry ''''])
curs=fetch(curs)
```
The select statement is created by using square brackets to concatenate the two strings select * from customers where country = and 'UserCountry'. The pairs of four quotation marks are needed to create the pair of single quotation marks that appears in the SQL statement around UserCountry. 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.

Example 4 — Roll Back or Commit Data Exported to Database Table

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

To roll back data for the database connection conn.
```
exec(conn, 'rollback')
```
To commit the data, run:
```
exec(conn, 'commit');
```

Example 5 — Change Database Connection Catalog

Change the catalog for the database connection conn to intlprice.

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

Example 6 — Create a Table and Add a New Column

This example creates a table and adds a new column to it.

Use the SQL CREATE command to create the table.

mktab = 'CREATE TABLE Person(LastName varchar, ...
FirstName varchar,Address varchar,Age int)'

Create the table for the database connection object conn.
```
exec(conn, mktab);
```

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

a = exec(conn, ...
'ALTER TABLE Person ADD City varchar(30)')

Example 7 — Run a Simple Stored Procedure

Execute the stored procedure sp_customer_list for the database connection conn.
```
curs = exec(conn,'sp_customer_list');
```

Run a stored procedure with input parameters.

curs = exec(conn,'{call sp_name (parm1,parm2,...)}');

Example 8 — Return a Cursor Object Using a Stored Procedure

The following example calls a database stored procedure that returns a cursor object.

Specify data to return as a structure.

setdbprefs('DataReturnFormat','structure');

Define a stored procedure.

ssql_cmd1 = '{?= call get_int_by_id(1,1, ...
				to_date('07/02/05','MM/DD/YY'),...
				to_date('07/07/05','MM/DD/YY'))}';

Execute the stored procedure and open a cursor object.

curs = exec(conn, ssql_cmd1)
curs =
   Attributes: []
             Data: 0
   DatabaseObject: [1x1 database]
         RowLimit: 0
         SQLQuery: [1x97 char]
          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

Import data from the cursor to a MATLAB variable, a.
```
a = fetch(curs);
```

View a.Data.

a.Data
ans =
       TS_DT: {'2005-07-02 00:00:00.0'}
   INT_VALUE: 1

Define another stored procedure.

sql_cmd2='{?= call nrg.ts_get_int_by_id(1,1,...
				to_date(''07/02/05'',''MM/DD/YY''),...
				to_date(''07/20/05'',''MM/DD/YY''))}';

Repeat steps 1 through 5 using this new stored procedure.

curs = exec(conn, ssql_cmd2)
curs =
       Attributes: []
             Data: 0
   DatabaseObject: [1x1 database]
         RowLimit: 0
         SQLQuery: [1x97 char]
          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

a = fetch(curs)
a =
       Attributes: []
             Data: [1x1 struct]
   DatabaseObject: [1x1 database]
         RowLimit: 0
         SQLQuery: [1x97 char]
          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: ...
							[1x1 com.mathworks.toolbox.database.fetchTheData]
a.Data
ans =
       TS_DT: {2x1 cell}
   INT_VALUE: [2x1 double]

Examine the attributes of a.

a.Data.TS_DT
ans =
   '2005-07-02 00:00:00.0'
   '2005-07-10 00:00:00.0'
a.Data.INT_VALUE
ans =
    1
    6