Teiid ODBC Translator

[kchen007]

Happy new year!

I have a question, does Teiid provide a ODBC translator? we have ODBC data srouce and will use JDBC-ODBC bridge to bring in the data. When we defined the vdb.xml, which translator should we use?

Thanks,

Kevin

[shawkins]

To us it's still JDBC, so you would use the most appropriate JDBC translator.  If there isn't a matching one, then there is jdbc-ansi (which assumes most sql constructs are supported using ansi sql) and jdbc-simple (which assumes most sql constructs are not supported).  Otherwise you'll want to look at a custom JDBC translator extension and/or logging an enhancement for a new built-in type.

Steve

[kchen007]

Thanks Steve.

We are facing another issue, when calling connection.isReadOnly() on the ODBC connection, jboss will throw exception. This problem is likely the underlying ODBC driver problem(or sun's JDBCODBC bridge). So we cannot configure it as DATASOURCE.

I create a resouce adapter for it, but when using jdbc-simple/jdbc-ansi translator, got the class cast exception, is there another JDBC translator that can will take WrappedConnectionFactory? Do I have to write a new translator to work with the Resource Adapter?

16:52:15,214 WARN  [org.teiid.RUNTIME] (teiid-async-threads - 1) TEIID50036 VDB PI.1 model "PI" metadata failed to load. Reason:org.teiid.resource.spi.Wrapped

nnectionFactory cannot be cast to javax.sql.DataSource: java.lang.ClassCastException: org.teiid.resource.spi.WrappedConnectionFactory cannot be cast to javax.

l.DataSource

        at org.teiid.translator.jdbc.JDBCExecutionFactory.getConnection(JDBCExecutionFactory.java:57)

        at org.teiid.translator.ExecutionFactory.getConnection(ExecutionFactory.java:190) [teiid-api-8.4.0.Final.jar:8.4.0.Final]

        at org.teiid.translator.BaseDelegatingExecutionFactory.getConnection(BaseDelegatingExecutionFactory.java:116) [teiid-api-8.4.0.Final.jar:8.4.0.Final]

        at org.teiid.query.metadata.NativeMetadataRepository.loadMetadata(NativeMetadataRepository.java:46) [teiid-engine-8.4.0.Final.jar:8.4.0.Final]

        at org.teiid.query.metadata.ChainingMetadataRepository.loadMetadata(ChainingMetadataRepository.java:55) [teiid-engine-8.4.0.Final.jar:8.4.0.Final]

        at org.teiid.query.metadata.ChainingMetadataRepository.loadMetadata(ChainingMetadataRepository.java:55) [teiid-engine-8.4.0.Final.jar:8.4.0.Final]

        at org.teiid.jboss.VDBService$6.run(VDBService.java:391) [teiid-jboss-integration-8.4.0.Final.jar:8.4.0.Final]

        at org.jboss.threads.SimpleDirectExecutor.execute(SimpleDirectExecutor.java:33)

        at org.jboss.threads.QueueExecutor.runTask(QueueExecutor.java:806)

        at org.jboss.threads.QueueExecutor.access$100(QueueExecutor.java:45)

        at org.jboss.threads.QueueExecutor$Worker.run(QueueExecutor.java:826)

        at java.lang.Thread.run(Thread.java:662) [rt.jar:1.6.0_38]

        at org.jboss.threads.JBossThread.run(JBossThread.java:122)

[shawkins]

> This problem is likely the underlying ODBC driver problem(or sun's JDBCODBC bridge). So we cannot configure it as DATASOURCE.

Can you post more details here?

> is there another JDBC translator that can will take WrappedConnectionFactory?

No, JDBC translators expect a DataSource. 

> Do I have to write a new translator to work with the Resource Adapter?

It would probably be simpler to modify the createConnectionFactory method of your BasicManagedConnectionFactory to return a DataSource instance.  Having not tried that though I'm not entirely sure about that approach.

Another approach would be to use custom delegating translator in front of the jdbc translator, such that you override the getConnection method to create a proxy or other DataSource instance based off of the passed in WrappedConnectionFactory.

Or ideally we'll be able to determine how to use normal pooling.

[kchen007]

the exception thrown from JBoss when it is configured as DS:

Caused by:
java.sql.SQLException: [OSI][PI ODBC][PI]SQLGetConnectOption: fOption = 101      
at sun.jdbc.odbc.JdbcOdbc.createSQLException(JdbcOdbc.java:6957) [rt.jar:1.6.0_38]      
at sun.jdbc.odbc.JdbcOdbc.standardError(JdbcOdbc.java:7114) [rt.jar:1.6.0_38]

at sun.jdbc.odbc.JdbcOdbc.SQLGetConnectOption(JdbcOdbc.java:3427) [rt.jar:1.6.0_38]      
at sun.jdbc.odbc.JdbcOdbcConnection.isReadOnly(JdbcOdbcConnection.java:863)[rt.jar:1.6.0_38]   
at org.jboss.jca.adapters.jdbc.BaseWrapperManagedConnection.<init>(BaseWrapperManagedConnection.java:201)

at org.jboss.jca.adapters.jdbc.local.LocalManagedConnection.<init>(LocalManagedConnection.java:61)

at org.jboss.jca.adapters.jdbc.local.LocalManagedConnectionFactory.getLocalManagedConnection(LocalManagedConnectionFactory.java:267)

Thanks,

Kevin

[shawkins]

Yes that does seem to be hard to work around.  Perhaps an issue against AS would be in order. 

And yet another option is to not use a JBoss connection pool and rather extend the appropriate JDBC translator to override the ExecutionFactory.getConnection method to obtain a connection directly from the driver or from a different pooling mechanism.

Steve

[kchen007]

Thanks Steve.

I have to go with the a new translator route due to some other issues.

right now, in translator's capacity how can I tell Teiid runtime to handle the timestamp comparasion in where clause?

for example, I have this SQL:

select * from myTable where strColum='myValue' and tsColum=''31-Dec-2013 12:00:00'

only this got pass to my translator:

select * from myTable where strColum='myValue'

thanks

Kevin

[shawkins]

If you are getting the first equality predicate I don't see an immediate reason why you wouldn't get the second part.  Are you doing something in the metadata such as marking the tsColumn as unsearchable? If you run with "set showplan debug" the "show plan" debug log should contain messages as to why predicates cannot be pushed.

Steve

[kchen007]

here is the log that indicate the timetamp part was not pushed to translator, where shall I define the conversion function?

12:22:13,312 DEBUG [org.teiid.PLANNER] (Worker3_QueryProcessorQueue20) [QueryAnnotation<Relational Planner, LOW,convert function not supported by source PI,convert(A."time", string) was not pushed>]

thanks,

Kevin

[shawkins]

If A.time is a timestamp type, then we are likely adding the convert function because the timestamp literal is not in the expected format.  For compatibility reasons we expect time/date/timestamp string values to appear in JDBC standard format.  So for a timestamp it would be yyyy-MM-dd hh:mm:ss ffff - e.g. '2013-12-31 12:00:00'