exec (Database Toolbox)

Execute SQL statement and open cursor

Syntax

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

Description

curs = exec(conn, 'sqlquery') executes the valid SQL statement sqlquery, against the database connection conn, and opens a cursor. Running exec returns the cursor object to the variable curs, and returns 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 determine the maximum amount of time for which exec will try to complete the SQL statement.
You can have multiple cursors open at one time.
A cursor stays open until you close it using the close function. Always close a cursor after you finish using it.
You can use the Visual Query Builder GUI instead of functions to import data. See Visual Query Builder for details.
Perform database administrative tasks, such as creating tables, using your database system application. The Database Toolbox is not intended to be used as a tool for database administration.
Unless specifically noted in this reference page, all valid SQL statements, such as nested queries, are supported by the exec function.
If you attempt to modify database tables from the Database Toolbox, be sure that you (or another user for a shared database) do not have the database open for editing (design mode in Microsoft Access). If the database is open for editing and you try to modify it, you will receive the following error in MATLAB.

[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 sheets. By default, some sheet names include $. To select data from a sheet with this name format, the SQL statement should be of this form: select * from "Sheet1$" (or `Sheet1$`) .
For the Microsoft SQL Server database management system, you might experience problems with text field formats. One workaround is to convert fields of the formats NVARCHAR, TEXT, NTEXT, and VARCHAR to CHAR on the database side. Another possible workaround is to convert data to VARCHAR as part of sqlquery. As an example, use a sqlquery of the form 'select convert(varchar(20), field1) from table1'
The Postgre database management system supports multidimensional fields, but SQL select statements fail when getting these fields unless an index is specified.

Examples

Example 1--Select All Data from Database Table

Select all data from the customers table accessed via 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 accessed via 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 Variable in a Query

Select data from the customers table accessed via the database connection conn, where country is a variable. In this example, the user is prompted to supply their country, which is assigned to the variable UserCountry.

UserCountry = input('Enter your country: ', 's')

MATLAB prompts

```
Enter your country:
```

The user responds

```
Mexico
```

Without using a variable, the function to retrieve the data would be

curs = exec(conn, ['select * from customers where country...
     = ''Mexico'''])
curs=fetch(curs)

To instead perform the query using the user's response, use

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

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

Use exec to roll back or commit data after running an insert or an update for which the AutoCommit flag is off. To roll back data for the database connection conn, type

```
exec(conn, 'rollback')
```

To commit the data, type:

```
exec(conn, 'commit');
```

Example 5--Run Stored Procedure

Execute the stored procedure sp_customer_list for the database connection conn.

```
curs = exec(conn,'sp_customer_list');
```

You can run a stored procedure with input parameters, for example

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

Example 6--Change Catalog

To change the catalog for the database connection conn to intlprice.

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

See Also

close, database, fetch, insert, procedures, querybuilder, querytimeout, resultset, rsmd, set, update

Data Retrieval Restrictions

drivermanager exportedkeys