0 Replies Latest reply on Apr 25, 2011 3:21 PM by brendalc1636

    SQLAssembler id-query

    brendalc1636

      Hello all,

       

      I have stumbled across a problem I ran across previously but I don't know how to fix the problem.

       

      I have an insert query in my SQLAssembler that inserts a record into our Oracle database. I have an id-query that gets the currval from a sequence for the primary key of the new record:

       

      <create-item>

      <sql>

      INSERT INTO WORK_QUEUE (WORK_QUEUE_ID, CREATION_DATE, DESCRIPTION) VALUES

      (#WORK_QUEUE_ID#, TO_DATE(#CREATION_DATE#, 'MM/DD/YYYY'), #DESCRIPTION#)

      </sql>

      <id-query>SELECT SUBSCRIBER_ID_SEQ.CURRVAL FROM DUAL</id-query>

      </create-item>

       

      where WORK_QUEUE_ID is the primary key. When this query gets called I get the following error:

       

      18:02:23,090 INFO  [STDOUT] lcds[DEBUG] [Service.Data.SQL] SQL Assembler jdbc exception on operation=create-item error=java.sql.SQLException: ORA-08002: sequence SUBSCRIBER_ID_SEQ.CURRVAL is not yet defined in this session


      at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)

      at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:331)

      at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:288)

      at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:745)

      at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:216)

      at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:810)

      at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1039)

      at oracle.jdbc.driver.T4CPreparedStatement.executeMaybeDescribe(T4CPreparedStatement.java:85 0)

      at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1134)

      at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3 339)

      at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3384 )

      at org.jboss.resource.adapter.jdbc.WrappedPreparedStatement.executeQuery(WrappedPreparedStat ement.java:342)

      at flex.data.assemblers.SQLAssembler.processIdQuery(SQLAssembler.java:1069)

      at flex.data.assemblers.SQLAssembler.createItem(SQLAssembler.java:1020)

      at flex.data.adapters.JavaAdapter.doCreateItem(JavaAdapter.java:1829)

      at flex.data.adapters.JavaAdapter.invokeAssemblerSync(JavaAdapter.java:1698)

      at flex.data.adapters.JavaAdapter.invokeBatchOperation(JavaAdapter.java:1562)

      at flex.data.adapters.JavaAdapter.invoke(JavaAdapter.java:636)

      at flex.messaging.services.MessageService.serviceMessage(MessageService.java:276)

      at flex.data.DataService.sendBatchToAdapter(DataService.java:1414)

      at flex.data.DataService.serviceTransactedMessage(DataService.java:787)

      at flex.data.DataService.serviceMessage(DataService.java:465)

      at flex.messaging.MessageBroker.routeMessageToService(MessageBroker.java:1503)

      at flex.messaging.endpoints.AbstractEndpoint.serviceMessage(AbstractEndpoint.java:898)

      at flex.messaging.endpoints.amf.MessageBrokerFilter.invoke(MessageBrokerFilter.java:121)

      at flex.messaging.endpoints.amf.LegacyFilter.invoke(LegacyFilter.java:158)

      at flex.messaging.endpoints.amf.SessionFilter.invoke(SessionFilter.java:44)

      at flex.messaging.endpoints.amf.BatchProcessFilter.invoke(BatchProcessFilter.java:67)

      at flex.messaging.endpoints.amf.SerializationFilter.invoke(SerializationFilter.java:147)

      at flex.messaging.endpoints.BaseHTTPEndpoint.service(BaseHTTPEndpoint.java:278)

      at flex.messaging.MessageBrokerServlet.service(MessageBrokerServlet.java:322)

      at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)

      at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.j ava:290)

      at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)

      at org.jboss.web.tomcat.filters.ReplyHeaderFilter.doFilter(ReplyHeaderFilter.java:96)

      at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.j ava:235)

      at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)

      at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:235)

      at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)

      at org.jboss.web.tomcat.security.SecurityAssociationValve.invoke(SecurityAssociationValve.ja va:190)

      at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:433)

      at flex.messaging.security.TomcatValve.invoke(TomcatValve.java:151)

      at org.jboss.web.tomcat.security.JaccContextValve.invoke(JaccContextValve.java:92)

      at org.jboss.web.tomcat.security.SecurityContextEstablishmentValve.process(SecurityContextEs tablishmentValve.java:126)

      at org.jboss.web.tomcat.security.SecurityContextEstablishmentValve.invoke(SecurityContextEst ablishmentValve.java:70)

      at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:127)

      at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102)

      at org.jboss.web.tomcat.service.jca.CachedConnectionValve.invoke(CachedConnectionValve.java: 158)

      at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)

      at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:330)

      at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:829)

      at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.ja va:598)

      at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:447)

      at java.lang.Thread.run(Unknown Source)

       

      I found that this error happens because I am trying to use CURRVAL before using NEXTVAL according to the Oracle site. But I don't understand why this is happening when it is. If I perform an action that uses a similar insert and id-query, I do not get that error and the new row is added correctly.

       

      Does anyone know why this is happening? Has anyone run into this problem before?