5 Replies Latest reply on Oct 22, 2012 1:13 PM by gamvi01

    Parameter replacement for an update statement is not happening for set clause.

    gamvi01

      Hi

       

      I have an update statement which is getting routed from a trigger statement(which involves firing update using dynamic sql instruction), I have the below metadata defined for a model. Parameter replacement happens fine for the where clasuse but for setclause the command object is shown as below.Is some sqlvisitor not ovveriden wel for dynamic sql ?

       

      *Note 1) when issue update statement by putting in values instead of paraemeter replacement everything looks good

                2) Paraemter replacement update against secuirty_ldap looks good

        Thing that fails ---Update routed through trigger

       

       

      UPDATE acf2admingrp=lids,host=xe42_cia,o=ca,c=us?SUBTREE_SCOPE SET name = '?', phone = '?' WHERE acf2admingrp=lids,host=xe42_cia,o=ca,c=us?SUBTREE_SCOPE.DN = 'ALUVE01'

       

      <metadata type="DDL">

           create view BASEUSER ( ............

       

       

           CREATE TRIGGER ON BASEUSER INSTEAD OF UPDATE

                              AS FOR EACH ROW

                                  BEGIN ATOMIC

                                      DECLARE string setclause = null;

                                      DECLARE string whereClause = 'security_ldap.BASEUSER.DN'||'='||'new.USERID'||' AND '||'SECURITY_LDAP.BASEUSER.SYSID'||'='||'new.SYSID';

                                      IF (CHANGING.NAME)

                                          BEGIN

                                              setclause = 'security_ldap.BASEUSER.NAME'||'='||'new.name';

                                          END

                                      EXECUTE IMMEDIATE 'UPDATE security_ldap.BASEUSER SET ' || setclause || ' WHERE '||whereClause;

                                  END;

      </metadata>

       

      I have an alter trigger statement fired after jboss starts.

      Issue alter statement against SECURITY_USERUPDATE.BASEUSER

      ALTER TRIGGER ON SECURITY_USERUPDATE.BASEUSER INSTEAD OF UPDATE

      AS FOR EACH ROW

      BEGIN ATOMIC

      DECLARE string setclause = null;

      DECLARE string whereClause = 'security_ldap.BASEUSER.DN'||'='||'new.USERID'||' AND '||'security_ldap.BASEUSER.SYSID'||'='||'new.SYSID';

      IF (CHANGING.NAME)

          BEGIN

              if(setclause is not null)

                  BEGIN

                      setclause = setclause||',';

                  END

              ELSE

                  BEGIN

                      setclause = '';

                  END

          setclause = setclause||'security_ldap.BASEUSER.NAME'||'='||'new.NAME';

      END

      IF (CHANGING.PHONE)

          BEGIN

              if(setclause is not null)

                  BEGIN

                      setclause = setclause||',';

                  END

              ELSE

                  BEGIN

                      setclause = '';

                  END

          setclause = setclause||'security_ldap.BASEUSER.PHONE'||'='||'new.PHONE';

      END

      EXECUTE IMMEDIATE 'UPDATE security_ldap.BASEUSER SET ' || setclause || ' WHERE '||whereClause;

      END;

        • 1. Re: Parameter replacement for an update statement is not happening for set clause.
          gamvi01

          Update - Observed the same behaviour on dybnamic portfolio. When i provide the values in the query instead of parameterization updates goes fine.

           

          org.teiid.jdbc.TeiidSQLException: Error Code:TEIID30504 Message:Remote org.teiid.core.TeiidProcessingException: TEIID30504 h2-connector: 90026 TEIID11013:TEIID11004 Error executing statement(s): [Prepared Values: ['?'] SQL: UPDATE "ACCOUNTS"."PUBLIC"."ACCOUNT" SET "TYPE" = ? WHERE "ACCOUNTS"."PUBLIC"."ACCOUNT"."ACCOUNT_ID" = 19980002]

              at org.teiid.jdbc.TeiidSQLException.create(TeiidSQLException.java:113)

              at org.teiid.jdbc.TeiidSQLException.create(TeiidSQLException.java:70)

              at org.teiid.jdbc.StatementImpl.postReceiveResults(StatementImpl.java:654)

              at org.teiid.jdbc.StatementImpl.access$100(StatementImpl.java:62)

              at org.teiid.jdbc.StatementImpl$2.onCompletion(StatementImpl.java:565)

              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(NativeMethodAccessorImpl.java:57)

              at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)

              at java.lang.reflect.Method.invoke(Method.java:601)

              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:574)

              at org.teiid.jdbc.PreparedStatementImpl.executeUpdate(PreparedStatementImpl.java:226)

              at com.ca.test.ParameterRepalcementOnDynamicPortfolio.main(ParameterRepalcementOnDynamicPortfolio.java:19)

          Caused by: [TeiidProcessingException] TEIID30504: Error Code:TEIID30504 Message:Remote org.teiid.core.TeiidProcessingException: TEIID30504 h2-connector: 90026 TEIID11013:TEIID11004 Error executing statement(s): [Prepared Values: ['?'] SQL: UPDATE "ACCOUNTS"."PUBLIC"."ACCOUNT" SET "TYPE" = ? WHERE "ACCOUNTS"."PUBLIC"."ACCOUNT"."ACCOUNT_ID" = 19980002]

          1 [TeiidException] 90026: Error Code:90026 Message:Remote org.teiid.translator.jdbc.JDBCExecutionException: 90026 TEIID11013:TEIID11004 Error executing statement(s): [Prepared Values: ['?'] SQL: UPDATE "ACCOUNTS"."PUBLIC"."ACCOUNT" SET "TYPE" = ? WHERE "ACCOUNTS"."PUBLIC"."ACCOUNT"."ACCOUNT_ID" = 19980002]

          2 [SQLException]Remote org.h2.jdbc.JdbcSQLException: Serialization failed, cause: "java.io.NotSerializableException: org.teiid.query.sql.symbol.Reference" [90026-161]

          3 [NotSerializableException]Remote java.io.NotSerializableException: org.teiid.query.sql.symbol.Reference

              at org.teiid.dqp.internal.process.DataTierTupleSource.exceptionOccurred(DataTierTupleSource.java:534)

              at org.teiid.dqp.internal.process.DataTierTupleSource.nextTuple(DataTierTupleSource.java:321)

              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:149)

              at org.teiid.query.processor.QueryProcessor.nextBatchDirect(QueryProcessor.java:145)

              at org.teiid.query.processor.QueryProcessor.nextBatch(QueryProcessor.java:111)

              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:569)

              at org.teiid.query.processor.proc.CreateCursorResultSetInstruction.process(CreateCursorResultSetInstruction.java:68)

              at org.teiid.query.processor.proc.ExecDynamicSqlInstruction$1.process(ExecDynamicSqlInstruction.java:196)

              at org.teiid.query.processor.proc.ProcedurePlan.processProcedure(ProcedurePlan.java:379)

              at org.teiid.query.processor.proc.ProcedurePlan.nextBatchDirect(ProcedurePlan.java:306)

              at org.teiid.query.processor.proc.ProcedurePlan.nextBatch(ProcedurePlan.java:280)

              at org.teiid.query.processor.QueryProcessor.nextBatchDirect(QueryProcessor.java:145)

              at org.teiid.query.processor.QueryProcessor.nextBatch(QueryProcessor.java:111)

              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:149)

              at org.teiid.query.processor.QueryProcessor.nextBatchDirect(QueryProcessor.java:145)

              at org.teiid.query.processor.QueryProcessor.nextBatch(QueryProcessor.java:111)

              at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:153)

              at org.teiid.dqp.internal.process.RequestWorkItem.processMore(RequestWorkItem.java:384)

              at org.teiid.dqp.internal.process.RequestWorkItem.process(RequestWorkItem.java:288)

              at org.teiid.dqp.internal.process.AbstractWorkItem.run(AbstractWorkItem.java:49)

              at org.teiid.dqp.internal.process.RequestWorkItem.run(RequestWorkItem.java:216)

              at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:250)

              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.runWorker(ThreadPoolExecutor.java:1110)

              at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:603)

              at java.lang.Thread.run(Thread.java:722)

          Caused by: [TeiidException] 90026: Error Code:90026 Message:Remote org.teiid.translator.jdbc.JDBCExecutionException: 90026 TEIID11013:TEIID11004 Error executing statement(s): [Prepared Values: ['?'] SQL: UPDATE "ACCOUNTS"."PUBLIC"."ACCOUNT" SET "TYPE" = ? WHERE "ACCOUNTS"."PUBLIC"."ACCOUNT"."ACCOUNT_ID" = 19980002]

          1 [SQLException]Remote org.h2.jdbc.JdbcSQLException: Serialization failed, cause: "java.io.NotSerializableException: org.teiid.query.sql.symbol.Reference" [90026-161]

          2 [NotSerializableException]Remote java.io.NotSerializableException: org.teiid.query.sql.symbol.Reference

              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:288)

              at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.execute(ConnectorWorkItem.java:255)

              at org.teiid.dqp.internal.process.DataTierTupleSource.getResults(DataTierTupleSource.java:457)

              at org.teiid.dqp.internal.process.DataTierTupleSource.nextTuple(DataTierTupleSource.java:302)

              ... 36 more

          Caused by: java.sql.SQLException: Remote org.h2.jdbc.JdbcSQLException: Serialization failed, cause: "java.io.NotSerializableException: org.teiid.query.sql.symbol.Reference" [90026-161]

              at org.h2.message.DbException.getJdbcSQLException(DbException.java:329)

              at org.h2.message.DbException.get(DbException.java:158)

              at org.h2.util.Utils.serialize(Utils.java:239)

              at org.h2.value.DataType.convertToValue(DataType.java:945)

              at org.h2.jdbc.JdbcPreparedStatement.setObject(JdbcPreparedStatement.java:452)

              at org.jboss.jca.adapters.jdbc.WrappedPreparedStatement.setObject(WrappedPreparedStatement.java:957)

              at org.teiid.translator.jdbc.JDBCExecutionFactory.bindValue(JDBCExecutionFactory.java:837)

              at org.teiid.translator.jdbc.JDBCBaseExecution.bind(JDBCBaseExecution.java:101)

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

              ... 41 more

          Caused by: java.io.NotSerializableException: Remote java.io.NotSerializableException: org.teiid.query.sql.symbol.Reference

              at java.io.ObjectOutputStream.writeObject0(ObjectOutputStream.java:1180)

              at java.io.ObjectOutputStream.writeObject(ObjectOutputStream.java:346)

              at org.h2.util.Utils.serialize(Utils.java:236)

              ... 47 more

          • 2. Re: Parameter replacement for an update statement is not happening for set clause.
            gamvi01
            • 3. Re: Parameter replacement for an update statement is not happening for set clause.
              gamvi01

              Iam seeing this error sometimes after getting the patches.

               

               

              Iam seeing this error after i got the patches for TEIID-2264 and TEIID-2265. Do you think the below error could be caused due to any of those patches ?

               

               

              [18:40:31.548][info][talledLocalContainer] 18:40:31,534 ERROR [org.jboss.threads.executor] Task execution failed for task org.teiid.jboss.VDBService$6@6d5e41b1: java.lang.IllegalStateException

              [18:40:31.548][info][talledLocalContainer]     at org.jboss.msc.value.InjectedValue.getValue(InjectedValue.java:47) [jboss-msc-1.0.2.GA.jar:1.0.2.GA]

              [18:40:31.548][info][talledLocalContainer]     at org.teiid.jboss.VDBService.getVDBRepository(VDBService.java:432)  [teiid-jboss-integration-8.1.0.FinalCAFix-SNAPSHOT.jar:8.1.0.FinalCAFix-SNAPSHOT]

              [18:40:31.549][info][talledLocalContainer]     at org.teiid.runtime.AbstractVDBDeployer.metadataLoaded(AbstractVDBDeployer.java:166) [teiid-runtime-8.1.0.FinalCAFix-SNAPSHOT.jar:8.1.0.FinalCAFix-SNAPSHOT]

              [18:40:31.549][info][talledLocalContainer]     at org.teiid.jboss.VDBService.access$800(VDBService.java:89) [teiid-jboss-integration-8.1.0.FinalCAFix-SNAPSHOT.jar:8.1.0.FinalCAFix-SNAPSHOT]

              [18:40:31.549][info][talledLocalContainer]     at org.teiid.jboss.VDBService$6.run(VDBService.java:385) [teiid-jboss-integration-8.1.0.FinalCAFix-SNAPSHOT.jar:8.1.0.FinalCAFix-SNAPSHOT]

              [18:40:31.549][info][talledLocalContainer]     at org.jboss.threads.SimpleDirectExecutor.execute(SimpleDirectExecutor.java:33)

              [18:40:31.549][info][talledLocalContainer]     at org.jboss.threads.QueueExecutor.runTask(QueueExecutor.java:801)

              [18:40:31.549][info][talledLocalContainer]     at org.jboss.threads.QueueExecutor.access$100(QueueExecutor.java:45)

              [18:40:31.549][info][talledLocalContainer]     at org.jboss.threads.QueueExecutor$Worker.run(QueueExecutor.java:842)

              [18:40:31.549][info][talledLocalContainer]     at java.lang.Thread.run(Thread.java:722) [rt.jar:1.7.0_05]

              [18:40:31.549][info][talledLocalContainer]     at org.jboss.threads.JBossThread.run(JBossThread.java:122)

              • 4. Re: Parameter replacement for an update statement is not happening for set clause.
                shawkins

                The changes would not have caused that exception, although there were ancillary changes to the vdbservice that make the line numbers not match up with master.  More than likely this is a timing issue.  Was the vdb removed during deployment?  I'm not sure what could have made the vdbrepository value unavailable otherwise.

                 

                Steve

                • 5. Re: Parameter replacement for an update statement is not happening for set clause.
                  gamvi01

                  No the vdb was not removed during deployment. Ill keep watching this . Ill try deleting the vdb and try a fresh start.