MATLAB: Do I receive ‘Error:Commit/Rollback Problems’ when I try to execute a lengthy query with the Database Toolbox 2.2 (R12.1)

Database Toolboxlongquerytemporaltime

The properties of the connection are:

 conn =
        Instance: 'ocmsql'
        UserName: 'mbothwell'
          Driver: []
             URL: []
     Constructor: [1x1 com.mathworks.toolbox.database.databaseConnect]
         Message: []
          Handle: [1x1 sun.jdbc.odbc.JdbcOdbcConnection]
         TimeOut: 0
      AutoCommit: 'on'
            Type: 'Database Object'

The cursor obtained by running the lengthy query is:

 curs =
         Attributes: []
               Data: 0
     DatabaseObject: [1x1 database]
           RowLimit: 0
           SQLQuery: [1x87 char]
            Message: 'Error:Commit/Rollback Problems'
               Type: 'Database Cursor Object'
          ResultSet: 0
             Cursor: 0
          Statement: 0
              Fetch: 0

After this, even the QUERYTIMEOUT on the cursor fails with the following error message:

 ??? Undefined function or variable 'getQueryTimeout'.
 Error in ==> c:\matlab6p1\toolbox\database\database\@cursor\querytimeout.m
 On line 25  ==> timeOut = getQueryTimeout(cursor.Statement);

Best Answer

There is a bug in the Database Toolbox 2.2 (R12.1) related to executing lengthy queries.

This bug has been resolved in the Database Toolbox 2.2.1 (R13).

Related Solutions

MATLAB: Do I receive an ‘Invalid connection’ error message when trying to establish a Dynamic Database Source Name (DSN) Connection to Access within Database Toolbox 3.10 (R2011b)

The below example has been incorporated into the documentation in Release 2012b (R2012b). For previous releases, read below for any additional information:

This error occurs due an error in the URL syntax. To connect to an Access database which has an ACCDB file format, please use the following form instead:

url = [['jdbc:odbc:Driver={Microsoft Access Driver (*.mdb, *.accdb)};DSN='';DBQ='] dbpath];

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: Do I receive an ‘Invalid connection’ error message when trying to establish a Dynamic Database Source Name (DSN) Connection to Access within Database Toolbox 3.10 (R2011b)

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

Related Question