Documentation Center

  • Trial Software
  • Product Updates

exec

Execute SQL statement and open cursor

Syntax

  • curs = exec(conn,sqlquery) example
  • curs = exec(conn,sqlquery,qTimeOut) 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.

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')
conn = 

  ODBCConnection with properties:

      Instance: 'dbtoolboxdemo'
      UserName: 'admin'
       Message: []
        Handle: [1x1 database.internal.ODBCConnectHandle]
       TimeOut: 0
    AutoCommit: 0
          Type: 'ODBCConnection Object'

conn has an empty Message property, which means a successful connection.

Select data from productTable that you access using the database.ODBCConnection object, conn. 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 as database.ODBCCursor object instead of a Database Cursor Object.

After you finish with the cursor object, close the cursor.

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 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 you finish with the cursor object, close the cursor.

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 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 you finish with the cursor object, close the cursor.

close(curs);

Select One Column of Data from the Database Table

Using dbtoolboxdemo data source, select stockNumber data from the productTable 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 stocknumber from productTable';
curs = exec(conn,sqlquery);

After you are finished with the cursor object, close the cursor.

close(curs);

Use a Variable in a Query

Using 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 you are finished with the cursor object, close the cursor.

close(curs);

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.

Roll back data for the database connection conn.

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

When you don't 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 you are finished with the cursor object, close the cursor.

curs = ans;
close(curs);

Commit the data.

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

After you are finished with the cursor object, close the cursor.

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 you are finished with the cursor object, close the cursor.

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 you are finished with the cursor object, close the cursor.

close(curs);

Run a Stored Procedure and Return the Result Set

Use the JDBC interface to connect to a Microsoft® SQL Server® database, run a stored procedure, and return the result set. For this example, the stored procedure getSupplierInfo is defined in the Microsoft SQL Server database. This stored procedure returns the supplier information for suppliers of a given city. The procedure definition is as follows.

CREATE PROCEDURE dbo.getSupplierInfo
	(@cityName varchar(20))
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    SELECT * from suppliers where city = @cityName
END
GO

For Microsoft SQL Server, the statement 'SET NOCOUNT ON' suppresses the results of insert, update or any non-select statements that might be before the final select query so you can fetch the results of the select query.

Use exec when the stored procedure returns one or more result sets. For procedures that return output parameters, use runstoredprocedure.

Using the JDBC interface, connect to the Microsoft SQL Server database called 'test_db' with the user name 'root' and password 'matlab' using port number 1234. This example assumes your database server is located on the machine servername.

conn = database('test_db','root','matlab',...
						'Vendor','Microsoft SQL Server',...
						'Server','servername','PortNumber',1234)
conn =
 
       Instance: 'test_db'
       UserName: 'root'
         Driver: []
            URL: []
    Constructor: [1x1 com.mathworks.toolbox.database.databaseConnect]
        Message: []
         Handle: [1x1 com.microsoft.sqlserver.jdbc.SQLServerConnection]
        TimeOut: 0
     AutoCommit: 'on'
           Type: 'Database Object'

database returns conn, a connection Database Object for the 'test_db' database.

Alternatively, you can use the native ODBC interface for an ODBC connection. For details, see database.

To return the result set in table format, use setdbprefs to set 'DataReturnFormat' to 'table'.

setdbprefs('DataReturnFormat','table');

Run the stored procedure, getSupplierInfo, to return supplier information for the city of New York using exec with conn.

sqlquery = '{call getSupplierInfo(''New York'')}';
curs = exec(conn,sqlquery)
curs =
 
        Attributes: []
              Data: 0
    DatabaseObject: [1x1 database]
          RowLimit: 0
          SQLQuery: '{call getSupplierInfo('New York')}'
           Message: []
              Type: 'Database Cursor Object'
         ResultSet: [1x1 com.microsoft.sqlserver.jdbc.SQLServerResultSet]
            Cursor: [1x1 com.mathworks.toolbox.database.sqlExec]
         Statement: [1x1 com.microsoft.sqlserver.jdbc.SQLServerStatement]
             Fetch: 0

exec returns a Database Cursor Object, curs, containing the supplier information.

Retrieve supplier data from curs using fetch.

curs = fetch(curs)
curs =
 
        Attributes: []
              Data: [3x5 table]
    DatabaseObject: [1x1 database]
          RowLimit: 0
          SQLQuery: '{call getSupplierInfo('New York')}'
           Message: []
              Type: 'Database Cursor Object'
         ResultSet: [1x1 com.microsoft.sqlserver.jdbc.SQLServerResultSet]
            Cursor: [1x1 com.mathworks.toolbox.database.sqlExec]
         Statement: [1x1 com.microsoft.sqlserver.jdbc.SQLServerStatement]
             Fetch: [1x1 com.mathworks.toolbox.database.fetchTheData]

curs contains the supplier data from running the stored procedure, getSupplierInfo, in table format.

Display the supplier data in table format by accessing the contents of the Data element of curs.

curs.Data
ans = 

    SupplierNumber       SupplierName          City   
    --------------    ------------------    ----------
    1001              'Wonder Products'     'New York'
    1006              'ACME Toy Company'    'New York'
    1012              'Aunt Jemimas'        'New York'


        Country          FaxNumber   
    ---------------    --------------
    'United States'    '212 435 1617'
    'United States'    '212 435 1618'
    'USA'              '14678923104' 

>>

Close the Database Cursor Object, curs, and then close the connection Database Object, conn.

close(curs);
close(conn);

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

Use the database connection, conn, to execute the custom function from MATLAB.

sqlquery = 'SELECT dbo.get_prodCount() as num_products';
curs = exec(conn,sqlquery);
curs = fetch(curs);

After you are finished with the cursor object, close the cursor.

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

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

For a native ODBC connection, the cursor object has only these properties from the previous list: Data, RowLimit, SQLQuery, Message, Type, and Statement.

See Also

| | | | | | | | | |

Was this topic helpful?