Configuring JdbcOdbcDriver on Teiid 8.1

[snadji]

Hi,

I am trying to configure the JDBC-ODBC bridge on 8.1. I copied your example datasource into standalone-teiid.xml. When I tried to restart JBoss, I got the errors listed below and JBoss would not start.

Do I need to specify a driver? How do I do that?

Thanks

JBoss errors:

14:59:44,230 INFO  [org.jboss.as] JBAS015899: JBoss AS 7.1.1.Final "Brontes" starting

14:59:45,299 ERROR [org.jboss.as.controller.management-operation] Operation ("add") failed - address: ([

    ("subsystem" => "datasources"),

    ("data-source" => "odbcDS")

]) - failure description: "JBAS014746: driver-name may not be null"

This is what I added to standalone-teiid.xml:

    <datasource jndi-name="java:/odbcDS" pool-name="odbcDS">

        <connection-url>jdbc:odbc:MyDSN</connection-url>       

         <driver-class>sun.jdbc.odbc.JdbcOdbcDriver</driver-class>

        <transaction-isolation>TRANSACTION_NONE</transaction-isolation>       

        <pool>

            <prefill>false</prefill>

            <use-strict-min>false</use-strict-min>

            <flush-strategy>FailingConnectionOnly</flush-strategy>

        </pool>

        <security>

            <user-name>MyUser</user-name>

            <password>MyPassword</password>

        </security>

    </datasource>                              

[rareddy]

Samier,

Try adding the following in jboss-as-7.1.1.Final/modules/sun/jdk/main/module.xml

<path name="sun/jdbc/odbc"/>

Then in the standalone-teiid.xml in subsystem "datasources", in the <drivers> section

	<drivers>
	<driver name="odbc" module="sun.jdk">
	<driver-class>sun.jdbc.odbc.JdbcOdbcDriver</driver-class>
	</driver>
	</drivers>

as per the "datasources" section it should be like (see the driver element)

	<datasource jndi-name="java:/odbcDS" pool-name="odbcDS">
	<connection-url>jdbc:odbc:{DSN}</connection-url>
	<driver>odbc</driver>
	<transaction-isolation>TRANSACTION_NONE</transaction-isolation>
	<pool>
	<prefill>false</prefill>
	<use-strict-min>false</use-strict-min>
	<flush-strategy>FailingConnectionOnly</flush-strategy>
	</pool>
	<security>
	<user-name>{user}</user-name>
	<password>{password}</password>
	</security>
	</datasource>

Let me know if this works, I will update the example.

Ramesh..

[snadji]

Ramesh,

Thanks for the quick response.

Your suggestoins got me quite a bit further. I am now able to add the datasource and start JBoss. However, I am still not able to create a good VDB.I am getting the errors below.  What translator should I use when defining the physical model in my dynamic-vdb.xml?

21:15:37,384 INFO  [org.teiid.RUNTIME] (teiid-async-threads - 4) TEIID50030 VDB sdm126.1 model "sd126" metadata loaded. End Time: 9/12/12 9:15 PM

21:15:37,562 WARN  [org.teiid.PLANNER.RESOLVER] (teiid-async-threads - 4) TEIID31071 Invalid table; Table mydb.xxx.tableyyy has no columns defined

...

[rareddy]

Samier,

You can use "jdbc-simple" translator. Are you placing this on top of a Excel document?

Ramesh..

[snadji]

Using "jdbc-simple" did not help.

No, we are not using Excel.  We are trying to connect to a custom implementation of a DataDirect ODBC driver. 

I noticed that the "Invalid table" error message was not being logged for all tables. However, the tables that cannot be loaded under Teiid are being displayed OK in Squirrel.  Also, when I use "importer.tableNamePattern" to load just one of those "no columns" tables, then the table loads OK.

I will starting looking into debugging JdbcMetaDataProcessor.getColumns(), but if you have any ideas, please let me know.

Thanks.

[rareddy]

Samier,

We have a validation on VDB that checks a table and makes sure there is atleast one column on the table. So, do you have any tables in the database that have no columns on that table or have any special kind of columns on that table?. Technically if it worked on Squirrel then it should work on Teiid too. Let us know what you find out. If you got the Teiid's code you can walk the JDBCMetadataProcessor code.

Ramesh..

[snadji]

Hi Ramesh,

I was not able to figure out why columns fail to load for some of the tables. When I use importer.excludeTables and tableNamePattern to load only one of these "failed" tables, then all works OK.

Anyhow, I am currently only specinfying the tables that I need (by using importer.excludeTables and tableNamePattern) and the VDB loads OK. 

However, when i try to get data from my VDB, I get the following error:

13:55:23,178 ERROR [org.teiid.PROCESSOR] (Worker1_QueryProcessorQueue30) TEIID30019 Unexpected exception for request hoAwpLpcLA5h.8: java.lang.IllegalArgumentException: Invalid type: org.teiid.metadata.Schema.

  at org.teiid.query.metadata.TransformationMetadata.createInvalidRecordTypeException(TransformationMetadata.java:619) [teiid-engine-8.1.0.Final.jar:8.1.0.Final]

  at org.teiid.query.metadata.TransformationMetadata.getModelID(TransformationMetadata.java:253) [teiid-engine-8.1.0.Final.jar:8.1.0.Final]

  at org.teiid.query.metadata.TempMetadataAdapter.getModelID(TempMetadataAdapter.java:175) [teiid-engine-8.1.0.Final.jar:8.1.0.Final]

  at org.teiid.query.metadata.TempMetadataAdapter.getModelID(TempMetadataAdapter.java:175) [teiid-engine-8.1.0.Final.jar:8.1.0.Final]

  at org.teiid.query.optimizer.relational.plantree.PlanNode.recordDebugAnnotation(PlanNode.java:423) [teiid-engine-8.1.0.Final.jar:8.1.0.Final]

  at org.teiid.query.optimizer.relational.rules.RulePushLimit.raiseAccessOverLimit(RulePushLimit.java:235) [teiid-engine-8.1.0.Final.jar:8.1.0.Final]

  at org.teiid.query.optimizer.relational.rules.RuleRaiseAccess.raiseAccessNode(RuleRaiseAccess.java:293) [teiid-engine-8.1.0.Final.jar:8.1.0.Final]

  at org.teiid.query.optimizer.relational.rules.RuleRaiseAccess.execute(RuleRaiseAccess.java:76) [teiid-engine-8.1.0.Final.jar:8.1.0.Final]

  at org.teiid.query.optimizer.relational.RelationalPlanner.executeRules(RelationalPlanner.java:457) [teiid-engine-8.1.0.Final.jar:8.1.0.Final]

  at org.teiid.query.optimizer.relational.RelationalPlanner.optimize(RelationalPlanner.java:199) [teiid-engine-8.1.0.Final.jar:8.1.0.Final]

  at org.teiid.query.optimizer.QueryOptimizer.optimizePlan(QueryOptimizer.java:188) [teiid-engine-8.1.0.Final.jar:8.1.0.Final]

  at org.teiid.dqp.internal.process.Request.generatePlan(Request.java:433) [teiid-engine-8.1.0.Final.jar:8.1.0.Final]

  at org.teiid.dqp.internal.process.Request.processRequest(Request.java:453) [teiid-engine-8.1.0.Final.jar:8.1.0.Final]

Running in the debugger, it looks like the TransformationMetadata.getModelID() method parameter groupOrElementID is of type "Schema" and does not have a parent - causing the invalid recorded exception.

I verified that this error is NOT limited to my ODBC setup. The problem occurs on a straight-forward simple JDBC-ODBC connection to an MSSQL database.

Any ideas why we are getting this exception?

Thanks.

[rareddy]

I tried with the latest trunk version of the code I did not see any issues, can you try with 8.2-alpha1 and see if this is still occurs there? Also, if possible can you post the schema of the one the table you were having issues with, I thinking if there is data type that Teiid does not support.

Ramesh..

[snadji]

Ramesh,

It's working on 8.2-alpha. I no longer see the (Invalid type: org.teiid.metadata.Schema) exception when getting data.

I am still seeing strange behavior loading schema and displaying data, but this could be due to the custom odbc driver we are using. I'll need to debug this to see what's going on.

Thanks.

[rareddy]

Can you post SquireL view of that table which does not load, where all the column descriptions are shown? I want to see if there is any column that is being not read correctly.

Ramesh..

[snadji]

Ramesh,

Below is the column description of the table. With 8.2, the schema loads, but when I try to get the data, all the values are null - although the row count is correct. Squirrel displays the values OK when doing a "select * from ...", but no data in the content tab.

As I mentioned before, I think this is due to the custom odbc driver that we are using. At this point, we pretty much decided to bypass using the custom driver and go straight to the DB to avoid all these problems. Thanks for your help!

column1,SQL_BINARY (UUID),YES,,16,,last_mod_by,-2,16,,1,-2,,16,9

column2,SQL_INTEGER,YES,0,10,,delete flag,4,4,10,1,4,,,4

column3,SQL_INTEGER,YES,0,10,,id,4,4,10,1,4,,,1

column4,SQL_INTEGER,YES,0,10,,supports SMTP,4,4,10,1,4,,,10

column5,SQL_INTEGER,YES,0,10,,write file,4,4,10,1,4,,,7

column6,SQL_TIMESTAMP,YES,0,19,,last_mod_dt,93,16,0,1,11,,,8

column7,SQL_WVARCHAR,YES,,240,,contact method template,-9,240,,1,-9,,240,6

column8,SQL_WVARCHAR,YES,,60,,symbol,-9,60,,1,-9,,60,11

column9,SQL_WVARCHAR,YES,,255,,description,-9,255,,1,-9,,255,5

column10,SQL_WVARCHAR,YES,,4095,,combo_name,-9,4095,,1,-9,,4095,12

column11,SQL_WVARCHAR,YES,,100,,Last Name,-9,100,,1,-9,,100,13

column12,SQL_WVARCHAR,YES,,100,,Userid,-9,100,,1,-9,,100,14

column13,SQL_WVARCHAR,YES,,30,,persistent_id,-9,30,,1,-9,,30,2

column14,SQL_WVARCHAR,YES,,60,,symbol,-9,60,,1,-9,,60,3