Error while accessing VDB

[iginsk]

SELECT * FROM SubmissionVDB.SubmissionViewModel.DEV_NEES.EVI_SUBMISSION

org.teiid.runtime.client.TeiidClientException: java.lang.RuntimeException: Remote org.teiid.core.TeiidProcessingException: TEIID30504 SubmissionUseCaseSourceModel: Unexpected exception while translating results: Invalid column type

Elapsed Time:  0 hr, 0 min, 0 sec, 791 ms.

============================================================================

----------------------------------------------------------------------------

OPTIMIZATION COMPLETE:

PROCESSOR PLAN:

AccessNode(0) output=[SubmissionUseCaseSourceModel.DEV_NEES.EVI_SUBMISSION.SUBMISSION_ID, SubmissionUseCaseSourceModel.DEV_NEES.EVI_SUBMISSION.JRNL_SUBMISSION_ID, SubmissionUseCaseSourceModel.DEV_NEES.EVI_SUBMISSION.JRNL_ARTICLE_ID, SubmissionUseCaseSourceModel.DEV_NEES.EVI_SUBMISSION.JRNL_ID, SubmissionUseCaseSourceModel.DEV_NEES.EVI_SUBMISSION.ARTICLE_ID, SubmissionUseCaseSourceModel.DEV_NEES.EVI_SUBMISSION.UCM_ID, SubmissionUseCaseSourceModel.DEV_NEES.EVI_SUBMISSION.PII_ID, SubmissionUseCaseSourceModel.DEV_NEES.EVI_SUBMISSION.CREATED_BY, SubmissionUseCaseSourceModel.DEV_NEES.EVI_SUBMISSION.CREATION_DATE, SubmissionUseCaseSourceModel.DEV_NEES.EVI_SUBMISSION.UPDATED_BY, SubmissionUseCaseSourceModel.DEV_NEES.EVI_SUBMISSION.UPDATED_DATE, SubmissionUseCaseSourceModel.DEV_NEES.EVI_SUBMISSION.STICKYNOTE, SubmissionUseCaseSourceModel.DEV_NEES.EVI_SUBMISSION.AUTHOR_REV_ID, SubmissionUseCaseSourceModel.DEV_NEES.EVI_SUBMISSION.PLAG_FOLDER_ID] SELECT g_0.SUBMISSION_ID, g_0.JRNL_SUBMISSION_ID, g_0.JRNL_ARTICLE_ID, g_0.JRNL_ID, g_0.ARTICLE_ID, g_0.UCM_ID, g_0.PII_ID, g_0.CREATED_BY, g_0.CREATION_DATE, g_0.UPDATED_BY, g_0.UPDATED_DATE, g_0.STICKYNOTE, g_0.AUTHOR_REV_ID, g_0.PLAG_FOLDER_ID FROM SubmissionUseCaseSourceModel.DEV_NEES.EVI_SUBMISSION AS g_0

============================================================================

16:40:34,179 WARN  [org.teiid.CONNECTOR] (Worker4_QueryProcessorQueue5) Connector worker process failed for atomic-request=BKVErNDDSiTv.0.0.2: org.teiid.translator.TranslatorException: Unexpected exception while translating results: Invalid column type

  at org.teiid.translator.jdbc.JDBCQueryExecution.next(JDBCQueryExecution.java:351)

  at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.handleBatch(ConnectorWorkItem.java:384) [teiid-engine-8.7.1.redhat-8.jar:8.7.1.redhat-8]

  at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.more(ConnectorWorkItem.java:202) [teiid-engine-8.7.1.redhat-8.jar:8.7.1.redhat-8]

  at org.teiid.dqp.internal.process.DataTierTupleSource.getResults(DataTierTupleSource.java:301) [teiid-engine-8.7.1.redhat-8.jar:8.7.1.redhat-8]

  at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:110) [teiid-engine-8.7.1.redhat-8.jar:8.7.1.redhat-8]

  at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:107) [teiid-engine-8.7.1.redhat-8.jar:8.7.1.redhat-8]

  at java.util.concurrent.FutureTask.run(FutureTask.java:262) [rt.jar:1.7.0_75]

  at org.teiid.dqp.internal.process.FutureWork.run(FutureWork.java:58) [teiid-engine-8.7.1.redhat-8.jar:8.7.1.redhat-8]

  at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:274) [teiid-engine-8.7.1.redhat-8.jar:8.7.1.redhat-8]

  at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:119) [teiid-engine-8.7.1.redhat-8.jar:8.7.1.redhat-8]

  at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:210) [teiid-engine-8.7.1.redhat-8.jar:8.7.1.redhat-8]

  at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145) [rt.jar:1.7.0_75]

  at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615) [rt.jar:1.7.0_75]

  at java.lang.Thread.run(Thread.java:745) [rt.jar:1.7.0_75]

Caused by: java.sql.SQLException: Invalid column type

  at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)

  at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:179)

  at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:269)

  at oracle.jdbc.driver.OracleStatement.getDATEValue(OracleStatement.java:3873)

  at oracle.jdbc.driver.OracleStatement.getTimestampValue(OracleStatement.java:4813)

  at oracle.jdbc.driver.OracleResultSetImpl.getTimestamp(OracleResultSetImpl.java:1284)

  at org.jboss.jca.adapters.jdbc.WrappedResultSet.getTimestamp(WrappedResultSet.java:1507)

  at org.teiid.translator.jdbc.JDBCExecutionFactory.retrieveValue(JDBCExecutionFactory.java:963)

  at org.teiid.translator.jdbc.JDBCQueryExecution.next(JDBCQueryExecution.java:344)

  ... 13 more

16:40:34,276 WARN  [org.teiid.PROCESSOR] (Worker3_QueryProcessorQueue6) TEIID30020 Processing exception for request BKVErNDDSiTv.0 'TEIID30504 SubmissionUseCaseSourceModel: Unexpected exception while translating results: Invalid column type'. Originally TeiidProcessingException DBError.java:134. Enable more detailed logging to see the entire stacktrace.

Can there be any help please

[iginsk]

[iginsk]

Connecting to Oracle, using ojdbc6.jar/EAP 6.0/Teiid 8.7.1/JBDS 8.1.0 Final (with all updated teiid specs to 9.0.0)

[rareddy]

Looks there is a mis-match between the metadata of EVI_SUBMISSION table in the source model of the VDB to the Oracle database version. One of the column is defined as Timestamp in the Teiid, but it does not look like that column is TIMETAMP in the Oracle DB. Check the columns and correct the type in VDB.

Ramesh..

[iginsk]

Some how I did not see the difference and these are not created by hand, it is created by JBDS designer view itself...not sure am i looking into the right place???

[iginsk]

Indeed, we are seeing the number type in oracle is converted to bigdecimal in VDB (source/view model)...is it a problem?

[iginsk]

Or could it be because of the difference in date and timestamp? I am currently looking for the bigdecimals and making them big integers and also if need be i can make sure ot have the sync on the date and timestamp. I mean, i will check all the date fields in the db and i will make the date in the VDB also...

[rareddy]

BigDecimal ==> BigInteger , I believe Teiid was choosing the widest data type, but yes you can change it to BigNumber.

I am little confused by the stack from Oracle driver

  at oracle.jdbc.driver.OracleStatement.getDATEValue(OracleStatement.java:3873)
  at oracle.jdbc.driver.OracleStatement.getTimestampValue(OracleStatement.java:4813)
  at oracle.jdbc.driver.OracleResultSetImpl.getTimestamp(OracleResultSetImpl.java:1284)

It is switching to Date value, not sure why? Can you see if you can use SQuirreL but use the same JDBC driver to Oracle to access the same table? Also what versions ojdbc driver are you using? I am suspecting you are using older version of jdbc driver. I have searched web last 15 mins, seen many threads on this, and none of them had a good answer if/how they fixed it

Ramesh..

[iginsk]

ojdbc6.jar is being used.

[iginsk]

have tried with ojdbc14.jar also...no luck

[rareddy]

Kiran,

No to "jdbc14.jar" as it is supposed to be sued with jdk 1.4 only.

After searching the web what I found is that when a column is retrieved through "resultset.getObject()" call, the Oracle JDBC call returns the native Oracle object type instead of "java.sql.*" object type. The complete details are from this documentation from Oracle  http://docs.oracle.com/database/121/JJDBC/datacc.htm#JJDBC28363

To overcome this issue you need to set the following JAVA system property

-Doracle.jdbc.J2EE13Compliant=true

so, to set this in the JBoss EAP/JDV,  edit "<jboss-install>/bin/standalone.conf" add the following

JAVA_OPTS="-Doracle.jdbc.J2EE13Compliant=true"

along with other similar options in this file.

For JBoss Developer Studio

<install>/studio/jbdevstudio.ini, add under "-vmargs" so it will look something like

-vmargs
-Xms512m
-Xmx1024m
-Doracle.jdbc.J2EE13Compliant=true
-Dosgi.instance.area.default=@user.home/workspace

restart both the JBoss EAP and JBoss Developer Studio and then try the preview or query the table using Teiid.

Let me know if this works.

Thanks

Ramesh..

[rareddy]

BTW, we also found java - resultSet.getTimeStamp() is not working - Stack Overflow that means you are using a older version of ojdbc6.jar than "11.1.0.6.0". To check the version of the driver, open the ojdbc6.jar with any zip utility and find MANIFEST.MF file and post it's contents or check the implemention-version, it should be something like below

Implementation-Version: 11.2.0.4.0

if not download the new ojdbc6.jar file from Oracle and make sure you have the right version, and clean up the JBoss EAP for all the old drivers and install this new driver.

Ramesh..