exec - Execute SQL statement and open cursor

Alternatives

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

Syntax

curs = exec(conn,'sqlquery') curs = exec(conn,'sqlquery',qTimeOut)

Description

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

curs = exec(conn,'sqlquery',qTimeOut) additionally specifies qTimeOut, the maximum amount of time exec tries to execute the SQL statement.

Tips

After opening a cursor, use fetch to import data from the cursor. Use resultset, rsmd, and statement to get properties of the cursor.
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 sqlquery argument can be a stored procedure for the database connection of the form {call sp_name (parm1,parm2,...)}.
Use exec when the stored procedure returns one or more result sets. For procedures that return output parameters, use runstoredprocedure.
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.
- Using 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. 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 the 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 the 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 the 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 Result Set Using a Stored Procedure

The following example calls a database stored procedure that returns a result set.

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

Example 9 — Run a Custom Database Function

This example shows how to run a user-defined database function on Microsoft SQL Server.

Consider a database function, get_prodCount, that gets entry counts in a table, productTable.

CREATE FUNCTION dbo.get_prodCount()
RETURNS int
AS
BEGIN
     DECLARE @PROD_COUNT int
     SELECT @PROD_COUNT = count(*) from productTable
     RETURN(@PROD_COUNT)
END
GO

Create the database connection, conn, and then execute the custom function from MATLAB.
```
curs = exec(conn,'SELECT dbo.get_prodCount() as num_products');
curs = fetch(curs);
```