4 Replies Latest reply on Apr 12, 2013 1:45 PM by Steven Hawkins

    INSERT procedure throwing exceptions

    skethire Newbie

      I have a simpe view with one table and 2 columns.  I am trying out the update procedure.  When I run a insert query against the view using Squirrel, it is successful.  However, when I use a preparedstatement from a java program, I get the following exception stack:

       

      My update procedure is as below:

         

      FOR EACH ROW

      BEGIN ATOMIC

        DECLARE integer x = "NEW".ColumnA;

        INSERT INTO IBitSync.CRUDTest (colA) VALUES (x);

      END

       

      The physical table ddl is below:

       

      CREATE TABLE [dbo].[CRUDTest](

      [colA] [int] NOT NULL,

      PRIMARY KEY CLUSTERED

      (

      [colA] ASC

      )
      I am using Teiid 8.1.Final

      Similar issue has been reported in : https://community.jboss.org/message/742008

       

       

      org.teiid.jdbc.TeiidSQLException

       

       

      : TEIID30504 Remote org.teiid.core.TeiidProcessingException

      : TEIID30504 IBitSync: 0 TEIID11013:TEIID11004 Error executing statement(s): [Prepared Values: [?] SQL: INSERT INTO "IBitSync"."dbo"."CRUDTest" (colA) VALUES (?)]

      at org.teiid.jdbc.TeiidSQLException.create(

      TeiidSQLException.java:135

      )

      at org.teiid.jdbc.TeiidSQLException.create(

      TeiidSQLException.java:71

      )

      at org.teiid.jdbc.StatementImpl.postReceiveResults(

      StatementImpl.java:656

      )

      at org.teiid.jdbc.StatementImpl.access$100(

      StatementImpl.java:62

      )

      at org.teiid.jdbc.StatementImpl$2.onCompletion(

      StatementImpl.java:512

      )

      at org.teiid.client.util.ResultsFuture.done(

      ResultsFuture.java:130

      )

      at org.teiid.client.util.ResultsFuture.access$200(

      ResultsFuture.java:37

      )

      at org.teiid.client.util.ResultsFuture$1.receiveResults(

      ResultsFuture.java:75

      )

      at org.teiid.net.socket.SocketServerInstanceImpl.receivedMessage(

      SocketServerInstanceImpl.java:222

      )

      at org.teiid.net.socket.SocketServerInstanceImpl.read(

      SocketServerInstanceImpl.java:257

      )

      at sun.reflect.NativeMethodAccessorImpl.invoke0(

      Native Method

      )

      at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)

      at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)

      at java.lang.reflect.Method.invoke(Unknown Source)

      at org.teiid.net.socket.SocketServerConnectionFactory$ShutdownHandler.invoke(

      SocketServerConnectionFactory.java:102

      )

      at $Proxy1.read(Unknown Source)

      at org.teiid.net.socket.SocketServerInstanceImpl$RemoteInvocationHandler$1.get(

      SocketServerInstanceImpl.java:356

      )

      at org.teiid.jdbc.StatementImpl.executeSql(

      StatementImpl.java:521

      )

      at org.teiid.jdbc.PreparedStatementImpl.executeUpdate(

      PreparedStatementImpl.java:217

      )

      at cg.test.filestream.ReadWriteFileStreamBlob.main(

      ReadWriteFileStreamBlob.java:45

      )

      Caused by:

      org.teiid.core.TeiidProcessingException: TEIID30504 Remote org.teiid.core.TeiidProcessingException

      : TEIID30504 IBitSync: 0 TEIID11013:TEIID11004 Error executing statement(s): [Prepared Values: [?] SQL: INSERT INTO "IBitSync"."dbo"."CRUDTest" (colA) VALUES (?)]

      at org.teiid.dqp.internal.process.DataTierTupleSource.exceptionOccurred(

      DataTierTupleSource.java:502

      )

      at org.teiid.dqp.internal.process.DataTierTupleSource.nextTuple(

      DataTierTupleSource.java:289

      )

      at org.teiid.query.processor.relational.AccessNode.nextBatchDirect(

      AccessNode.java:279

      )

      at org.teiid.query.processor.relational.RelationalNode.nextBatch(

      RelationalNode.java:279

      )

      at org.teiid.query.processor.relational.RelationalPlan.nextBatch(

      RelationalPlan.java:148

      )

      at org.teiid.query.processor.QueryProcessor.nextBatchDirect(

      QueryProcessor.java:143

      )

      at org.teiid.query.processor.QueryProcessor.nextBatch(

      QueryProcessor.java:109

      )

      at org.teiid.query.processor.BatchIterator.finalRow(

      BatchIterator.java:70

      )

      at org.teiid.common.buffer.AbstractTupleSource.getCurrentTuple(

      AbstractTupleSource.java:69

      )

      at org.teiid.query.processor.BatchIterator.getCurrentTuple(

      BatchIterator.java:82

      )

      at org.teiid.common.buffer.AbstractTupleSource.hasNext(

      AbstractTupleSource.java:91

      )

      at org.teiid.query.processor.proc.ProcedurePlan.executePlan(

      ProcedurePlan.java:510

      )

      at org.teiid.query.processor.proc.CreateCursorResultSetInstruction.process(

      CreateCursorResultSetInstruction.java:69

      )

      at org.teiid.query.processor.proc.ProcedurePlan.processProcedure(

      ProcedurePlan.java:365

      )

      at org.teiid.query.processor.proc.ProcedurePlan.nextBatchDirect(

      ProcedurePlan.java:293

      )

      at org.teiid.query.processor.proc.ProcedurePlan.nextBatch(

      ProcedurePlan.java:266

      )

      at org.teiid.query.processor.QueryProcessor.nextBatchDirect(

      QueryProcessor.java:143

      )

      at org.teiid.query.processor.QueryProcessor.nextBatch(

      QueryProcessor.java:109

      )

      at org.teiid.query.processor.proc.ForEachRowPlan.nextBatch(

      ForEachRowPlan.java:119

      )

      at org.teiid.query.processor.relational.PlanExecutionNode.nextBatchDirect(

      PlanExecutionNode.java:118

      )

      at org.teiid.query.processor.relational.RelationalNode.nextBatch(

      RelationalNode.java:279

      )

      at org.teiid.query.processor.relational.ProjectNode.nextBatchDirect(

      ProjectNode.java:146

      )

      at org.teiid.query.processor.relational.RelationalNode.nextBatch(

      RelationalNode.java:279

      )

      at org.teiid.query.processor.relational.RelationalPlan.nextBatch(

      RelationalPlan.java:148

      )

      at org.teiid.query.processor.QueryProcessor.nextBatchDirect(

      QueryProcessor.java:143

      )

      at org.teiid.query.processor.QueryProcessor.nextBatch(

      QueryProcessor.java:109

      )

      at org.teiid.query.processor.BatchCollector.collectTuples(

      BatchCollector.java:153

      )

      at org.teiid.dqp.internal.process.RequestWorkItem.processMore(

      RequestWorkItem.java:382

      )

      at org.teiid.dqp.internal.process.RequestWorkItem.process(

      RequestWorkItem.java:291

      )

      at org.teiid.dqp.internal.process.AbstractWorkItem.run(

      AbstractWorkItem.java:49

      )

      at org.teiid.dqp.internal.process.RequestWorkItem.run(

      RequestWorkItem.java:219

      )

      at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(

      DQPWorkContext.java:249

      )

      at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(

      ThreadReuseExecutor.java:123

      )

      at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(

      ThreadReuseExecutor.java:298

      )

      at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(

      ThreadPoolExecutor.java:886

      )

      at java.util.concurrent.ThreadPoolExecutor$Worker.run(

      ThreadPoolExecutor.java:908

      )

      at java.lang.Thread.run(

      Thread.java:662

      )

      Caused by:

      org.teiid.core.TeiidException: 0 Remote org.teiid.translator.jdbc.JDBCExecutionException

      : 0 TEIID11013:TEIID11004 Error executing statement(s): [Prepared Values: [?] SQL: INSERT INTO "IBitSync"."dbo"."CRUDTest" (colA) VALUES (?)]

      at org.teiid.translator.jdbc.JDBCUpdateExecution.executeTranslatedCommand(

      JDBCUpdateExecution.java:211

      )

      at org.teiid.translator.jdbc.JDBCUpdateExecution.execute(

      JDBCUpdateExecution.java:71

      )

      at org.teiid.dqp.internal.datamgr.ConnectorWorkItem$1.execute(

      ConnectorWorkItem.java:294

      )

      at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.execute(

      ConnectorWorkItem.java:261

      )

      at org.teiid.dqp.internal.process.DataTierTupleSource.getResults(

      DataTierTupleSource.java:425

      )

      at org.teiid.dqp.internal.process.DataTierTupleSource.nextTuple(

      DataTierTupleSource.java:270

      )

      ... 35 more

      Caused by:

      java.sql.SQLException: Remote com.microsoft.sqlserver.jdbc.SQLServerException

      : The conversion from UNKNOWN to INTEGER is unsupported.

      at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(

      SQLServerException.java:190

      )

      at com.microsoft.sqlserver.jdbc.DataTypes.throwConversionError(

      DataTypes.java:1117

      )

      at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.setObject(

      SQLServerPreparedStatement.java:991

      )

      at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.setObject(

      SQLServerPreparedStatement.java:944

      )

      at org.jboss.jca.adapters.jdbc.WrappedPreparedStatement.setObject(

      WrappedPreparedStatement.java:957

      )

      at org.teiid.translator.jdbc.JDBCExecutionFactory.bindValue(

      JDBCExecutionFactory.java:830

      )

      at org.teiid.translator.jdbc.sybase.BaseSybaseExecutionFactory.bindValue(

      BaseSybaseExecutionFactory.java:137

      )

      at org.teiid.translator.jdbc.JDBCBaseExecution.bind(

      JDBCBaseExecution.java:101

      )

      at org.teiid.translator.jdbc.JDBCUpdateExecution.executeTranslatedCommand(

      JDBCUpdateExecution.java:203

      )

      ... 40 more