9 Replies Latest reply on Jan 5, 2014 6:29 PM by Steven Hawkins

    Teiid ODBC Translator

    kchen007 Apprentice

      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

        • 1. Re: Teiid ODBC Translator
          Steven Hawkins Master

          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

          • 2. Re: Teiid ODBC Translator
            kchen007 Apprentice

            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)

            • 3. Re: Teiid ODBC Translator
              Steven Hawkins Master

              > 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.

              • 4. Re: Teiid ODBC Translator
                kchen007 Apprentice

                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

                • 5. Re: Teiid ODBC Translator
                  Steven Hawkins Master

                  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

                  • 6. Re: Teiid ODBC Translator
                    kchen007 Apprentice

                    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

                    • 7. Re: Teiid ODBC Translator
                      Steven Hawkins Master

                      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

                      • 8. Re: Teiid ODBC Translator
                        kchen007 Apprentice

                        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

                        • 9. Re: Teiid ODBC Translator
                          Steven Hawkins Master

                          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'