MATLAB: Do I receive a Java object signature (instead of a time) when I query for fields of TIMESTAMP type in an Oracle 10.2.0 database with Database Toolbox 3.3 (R2007a)

Database Toolbox

I am able to work normally with an Oracle (10.2.0) database through a JDBC connection. I am able to retrieve strings and number from the database, but get a problem when I query for the fields of the TIMESTAMP type. Instead of a time it returns the following string: 'oracle.sql.TIMESTAMP@37d830'. To me this seems like a Java object signature. I get the same result with just code or using the querybuilder. Can you explain how I can obtain timestamps from an Oracle database?

conumber = 427450;
query =['SELECT  PJ_OPENINGPROJECT FROM PROJECTEN WHERE PJ_PROJECTNR = ', num2str(conumber)];
curs = exec(conn, query);
curs = fetch(curs);
time =curs.DATA{1}

When I query the 'typeValue' field via code such as the following, it is 93:

a = attr(curs);
a.typeValue

Best Answer

Type 93 is normally mapped to java.sql.TIMESTAMP, and the 'toString' method converts this to a readable date. oracle.sql.TIMESTAMP is different and Oracle- specific. The 'toString' method for this object type just converts the object name to a string.

To workaround the issue, recast the oracle.sql.TIMESTAMP field to a java.sql.TIMESTAMP or "char". The SQL statement would appear as something like:

 SELECT  to_char(PJ_OPENINGPROJECT,'MM/DD/YYYY HH24:MI:SS') "NewDate" FROM PROJECTEN

Related Solutions

MATLAB: Do I receive the error “Unable to connect to data source” with Oracle 10G and the Database Toolbox 3.0.1 (R14SP1)

This information has been incorporated into the documentation for Database Toolbox 3.0.2 (R14SP2). For previous releases, please read below for any additional information:

When connecting to the new versions of Oracle (10) database via JDBC, the database 'Name' should be the same as the suffix after the port number in the database 'URL'

For the DATABASE function DATABASEURL argument, for example, instead of running

conn = database('oracle','scott','tiger','oracle.jdbc.driver.OracleDriver',...
'jdbc:oracle:thin:@144.212.123.24:1822:')

you should use

conn = database('databasename','username','password','oracle.jdbc.driver.OracleDriver',jdbc:oracle:thin:@IP_ADDRESS_OF_DATABASE_SERVER:1521:databasename');

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 the error “Unable to connect to data source” with Oracle 10G and the Database Toolbox 3.0.1 (R14SP1)

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

Related Question