Insert not working for SQLServer UID datatype.

[priyanka_navale]

I have one SqlServer database with table as follows:

create table sample (

id uniqueidentifier NOT NULL PRIMARY KEY,

name varchar (250) NULL );

Created static vdb for this table and trying to insert values using teiid jdbc client.

While executig insert query I am getiing the following exception:

2015-09-07 16:26:41,032 WARN  [org.teiid.CONNECTOR] (Worker31_QueryProcessorQueue137) Connector worker process failed for atomic-request=WmyA9yqi9fAd.17.0.22: org.teiid.translator.jdbc.JDBCExecutionException: 102 TEIID11013:TEIID11004 Error executing statement(s): [Prepared Values: [] SQL: INSERT INTO "test"."dbo"."sample" (cast("id" as char(36)), "name") VALUES ('DF215E10-8BD4-4401-B2DC-99BB03135F2E', 'test')]

  at org.teiid.translator.jdbc.JDBCUpdateExecution.executeTranslatedCommand(JDBCUpdateExecution.java:247)

  at org.teiid.translator.jdbc.JDBCUpdateExecution.execute(JDBCUpdateExecution.java:79)

  at org.teiid.dqp.internal.datamgr.ConnectorWorkItem$1.execute(ConnectorWorkItem.java:358) [teiid-engine-8.7.0.Final.jar:8.7.0.Final]

  at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.execute(ConnectorWorkItem.java:325) [teiid-engine-8.7.0.Final.jar:8.7.0.Final]

  at org.teiid.dqp.internal.process.DataTierTupleSource.getResults(DataTierTupleSource.java:298) [teiid-engine-8.7.0.Final.jar:8.7.0.Final]

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

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

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

  at org.teiid.dqp.internal.process.FutureWork.run(FutureWork.java:58) [teiid-engine-8.7.0.Final.jar:8.7.0.Final]

  at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:274) [teiid-engine-8.7.0.Final.jar:8.7.0.Final]

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

  at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:214) [teiid-engine-8.7.0.Final.jar:8.7.0.Final]

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

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

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

query from client: insert into sample(id,name) VALUES('DF215E10-8BD4-4401-B2DC-99BB03135F2E','test');

query translated by teiid : INSERT INTO "test"."dbo"."sample" (cast("id" as char(36)), "name") VALUES ('DF215E10-8BD4-4401-B2DC-99BB03135F2E', 'test');

Exception from SqlServer: com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near '('.

When we mannually insert data with following query the command gets excuted successfully.

INSERT INTO test.dbo.sample (id , name) VALUES (cast('DF215E10-8BD4-4401-B2DC-99BB03135F2E' as varchar(36)), 'test');

(Reference from : https://technet.microsoft.com/en-us/library/Dd776381(v=SQL.105).aspx) .

Is there any workaround for this or do have have a possible solution for this after 8.7.0 release?

[shawkins]

This was addressed with [TEIID-3161] Generated sql with trim strings is incorrect for insert variables and with clause - JBoss Issue Tracker which is in 8.9 and later.