MATLAB: Do I get an error when calling FETCH with a SAS database in Database Toolbox 3.7 (R2010a)

Database Toolbox

When I execute "curs=fetch(curs)", I get the following error:

 ??? Java exception occurred:
 java.sql.SQLException: [SAS][SAS ODBC Driver]Driver not capable
 at sun.jdbc.odbc.JdbcOdbc.createSQLException(Unknown Source)
 at sun.jdbc.odbc.JdbcOdbc.standardError(Unknown Source)
 at sun.jdbc.odbc.JdbcOdbc.SQLGetStmtOption(Unknown Source)
 at sun.jdbc.odbc.JdbcOdbcStatement.getStmtOption(Unknown Source)
 at sun.jdbc.odbc.JdbcOdbcStatement.getMaxRows(Unknown Source)
 Error in ==> cursor.get at 38
     v.RowLimit = getMaxRows(d.Statement);
 Error in ==> cursor.fetch at 66
     rowLimit = get(initialCursor,'Rowlimit');

I am connecting to a database with a fairly small amount of data, so I do not understand why I am getting a 'Rowlimit' error.

Best Answer

This issue is due to a limitation in the SAS driver. It requires a Rowlimit argument to the FETCH call. Executing

curs=fetch(curs,Inf)

will get all the records, since you are allowing the Rowlimit to be infinity.

Related Solutions

MATLAB: How to pass database name to datainsert in case of using ODBC

I figured it out the data source name has to be the same as the database name. Now I am getting another error when trying to use datainsert but it is more like a warning coz when i checked the database the data is inserted just fine.

java.sql.SQLException: SQL Exception : Conversion not supported by setObject!! at sun.jdbc.odbc.JdbcOdbcPreparedStatement.setObject(Unknown Source) at sun.jdbc.odbc.JdbcOdbcPreparedStatement.setObject(Unknown Source) at sun.jdbc.odbc.JdbcOdbcPreparedStatement.emulateExecuteBatch(Unknown Source) at sun.jdbc.odbc.JdbcOdbcPreparedStatement.executeBatchUpdate(Unknown Source) at sun.jdbc.odbc.JdbcOdbcStatement.executeBatch(Unknown Source) at com.mathworks.toolbox.database.writeTheData.cellWrite(writeTheData.java:284)

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 pass database name to datainsert in case of using ODBC

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

Related Question