MATLAB: Am I unable to execute a SQL query which uses BEGIN and END statements with the MATLAB Database Toolbox 3.0.1 (R14SP1)

begincompountDatabase Toolboxsyntax

When I attempt to execute an SQL query which uses a BEGIN/END block, such as the following:

{BEGIN
    <procedure calls>;  
END;}

on a database that uses an ODBC driver, I receive the following result:

curs=exec(conn, sqlData);
curs.Message
ans =
[Microsoft][ODBC driver for Oracle]Syntax error or access violation

This query is able to execute from other SQL software; why is it failing?

Best Answer

Neither the Oracle ODBC driver nor the Microsoft Oracle ODBC driver is able to execute statements of this syntax. These drivers typically run a stored procedure interactively. The introduction of a BEGIN/END block will cause them to fail. To work around this issue, create a stored procedure in the database and then run this procedure from the exec command.

You are also able to use input parameters for the stored procedure call. For

example,

e = exec(c,'{call sp_name(a,b,c)}')

where sp_name is the name of your stored procedure.

Related Solutions

MATLAB: How to specify the Database URL String in the DATABASE function of Database Toolbox 3.4(R2007b) to connect to a POSTGRESQL database

For a PostGreSQL database the driver and the URL specifications are as follows:

JDBC driver: org.postgresql.DriverDatabase
URL: jdbc:postgresql://masd/DatabaseName

Please note that the Database URL string needs to have the Database name "DatabaseName" appended at the end of it.

MATLAB: How to call a stored procedure from the database using the Database Toolbox

The documentation for MATLAB 7.6 (R2008a) has been updated to incorporate the relevant information. For previous product releases, read below for any possible workarounds:

This feature has been added in the Database Toolbox 3.2 (R2006b). You can use the RUNSTOREDPROCEDURE function. If you are using a previous version, read below for any workarounds.

You can execute a database's stored procedure using the valid SQL command to do so.

The following is an example showing a call to a database stored procedure that returns a cursor:

setdbprefs('DataReturnFormat','structure')
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'))}';
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
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: {'2005-07-02 00:00:00.0'}
     INT_VALUE: 1
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''))}';
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]
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

For more information on calling stored procedures, consult the Database Toolbox documentation by issuing the following command at the MATLAB command prompt:

doc procedures

Best Answer

Related Solutions

MATLAB: How to specify the Database URL String in the DATABASE function of Database Toolbox 3.4(R2007b) to connect to a POSTGRESQL database

MATLAB: How to call a stored procedure from the database using the Database Toolbox

Related Question