1 Reply Latest reply on Sep 8, 2015 10:32 AM by Steven Hawkins

    Insert not working for SQLServer UID datatype.

    Priyanka Navale Newbie

      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?