Teiid throw exception for column having dot in their name for sql server database.

[kulbhushanc]

Hi,

I am trying to insert data in SQL Server through teiid but it throw exception for column having dot in their name as dot are allowed in column names of SQL Server.

Exception is:

org.teiid.jdbc.TeiidSQLException: TEIID30126 Remote org.teiid.api.exception.query.QueryResolverException: TEIID30126 Column variables do not reference columns on group "SqlConnection1.DQ_2012.dbo.cust1": [Unable to resolve 'SqlConnection1.DQ_2012.DBO.CUST1.id.id': TEIID31119 Symbol SqlConnection1.DQ_2012.DBO.CUST1.id.id is specified with an unknown group context]

here, Connection name : SqlConnection1,

          Database name    : DQ_2012,

          Table name           :  DBO.CUST1,

          Column name       :  id.id

Teiid version:9.13

Teiid admin version:9.13

[shawkins]

The JDBC import will create a Teiid name for the column that replaces dot with underscore _.  That is the name you must use from your queries - the translator will replace that with the expected name that contains the dot.

[kulbhushanc]

Hi Steven,

You meant to say that the dot should be replaced by underscore in query,

INSERT INTO SqlConnection1.DQ_2012."DBO.CUST1"(SqlConnection1.DQ_2012."DBO.CUST1"."id_id",SqlConnection1.DQ_2012."DBO.CUST1"."name",SqlConnection1.DQ_2012."DBO.CUST1"."age",SqlConnection1.DQ_2012."DBO.CUST1"."address",SqlConnection1.DQ_2012."DBO.CUST1"."salary")VALUES(1,'name',3,'add',0)

if so that then we get the following exception:

TEIID30126 Remote org.teiid.api.exception.query.QueryResolverException: TEIID30126 Column variables do not reference columns on group "SqlConnection1.DQ_2012.dbo.cust1": [Unable to resolve 'SqlConnection1.DQ_2012.DBO.CUST1.id_id': TEIID31118 Element "SqlConnection1.DQ_2012.DBO.CUST1.id_id" is not defined by any relevant group.]

[shawkins]

Did you perform the import with Teiid or Teiid Designer?

It would help to consult the actual metadata as well.  Using a db tool like squirrel examine the columns on the table - or issue a query to the system metadata:

select * from SYS.Columns where TableName = 'DQ_2012.dbo.cust1' and SchemaName = 'SqlConnection1'

[kulbhushanc]

Did you perform the import with Teiid or Teiid Designer?

> I am using Teiid.

After executing select * from SYS.Columns where TableName = 'DQ_2012.dbo.cust1' and SchemaName = 'SqlConnection1' got the result:

1: SvcSourceVdb_SqlConnection1,SqlConnection1,DQ_2012.dbo.cust1,id.id,1,"id.id",integer,0,10,false,true,true,false,true,false,false,Nullable,null,null,-1,-1,Searchable,null,null,java.lang.Integer,10,0,10,tid:e578b348f4db-0a2db793-00000197,null,tid:e578b348f4db-605e4364-00000016

2: SvcSourceVdb_SqlConnection1,SqlConnection1,DQ_2012.dbo.cust1,name,2,"name",string,0,20,false,true,true,true,false,false,false,Nullable,null,null,-1,-1,Searchable,null,null,java.lang.String,20,20,0,tid:e578b348f4db-046b5f50-00000198,null,tid:e578b348f4db-605e4364-00000016

3: SvcSourceVdb_SqlConnection1,SqlConnection1,DQ_2012.dbo.cust1,age,3,"age",integer,0,10,false,true,true,false,true,false,false,Nullable,null,null,-1,-1,Searchable,null,null,java.lang.Integer,10,0,10,tid:e578b348f4db-04395dc4-00000199,null,tid:e578b348f4db-605e4364-00000016

4: SvcSourceVdb_SqlConnection1,SqlConnection1,DQ_2012.dbo.cust1,address,4,"address",string,0,20,false,true,true,true,false,false,false,Nullable,null,null,-1,-1,Searchable,null,null,java.lang.String,20,20,0,tid:e578b348f4db-bfcf80b9-0000019a,null,tid:e578b348f4db-605e4364-00000016

5: SvcSourceVdb_SqlConnection1,SqlConnection1,DQ_2012.dbo.cust1,salary,5,"salary",double,0,53,false,true,true,false,true,false,false,Nullable,null,null,-1,-1,Searchable,null,null,java.lang.Double,53,0,2,tid:e578b348f4db-cdfeae8f-0000019b,null,tid:e578b348f4db-605e4364-00000016

[shawkins]

This is a bug then - the name correction should happen automatically.  Can you log an issue for this?

There is a lot of legacy resolving code that prevents us from supporting . in a column name unfortunately.

[kulbhushanc]

Thanks Steven,

Can you please let me know how to log an issue for teiid?

[shawkins]

Using your jboss.org account log into - JBoss Issue Tracker  and hit create at the top of screen.  From there at least fill in the title, description, and add a link to this forum post.

[shawkins]

To make sure this gets into the upcoming 9.3 CR1, I went ahead and captured this as [TEIID-4913] MetadataFactory is not replacing . in column names - JBoss Issue Tracker