4 Replies Latest reply on Sep 30, 2011 9:33 AM by Tom Cunningham

    Join muliple table in JBOSS-ESB sql-provider ?

    Raghavan TV Newbie

      Is it possible to join multiple table in the sql-provider ?

       

      sample code jboss-esb.xml

      ...

      <sql-provider datasource="java:/oracle-ds" name="sqlprovider1">
         <sql-bus busid="sqlchannel1">
          <sql-message-filter error-delete="false"
           insert-timestamp-column="ESB_TABLE1.timestamp_col"
           message-column="ESB_TABLE2.order_item_id"
           message-id-column="ESB_TABLE1.order_queue_nbr"
           post-delete="false"
           status-column="ESB_TABLE1.esb_status"
           tablename="ESB_TABLE1, ESB_TABLE2" where-condition="ESB_TABLE1.order_queue_nbr=ESB_TABLE2.order_queue_nbr AND ESB_TABLE2.order_item_id like '%PP%'"/>
         </sql-bus>
        </sql-provider>

       

      ....

       

      The sql-provider is unable to complete the read tx and throws the following error

      2010-06-08 00:09:47,209 DEBUG [org.jboss.soa.esb.helpers.persist.JdbcCleanConn] execUpdWait() FAILED
      java.sql.SQLException: ORA-00971: missing SET keyword

       

          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:743)
          at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:213)
          at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:952)
          at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1160)
          at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3285)
          at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:3368)
          at org.jboss.resource.adapter.jdbc.WrappedPreparedStatement.executeUpdate(WrappedPreparedStatement.java:365)
          at org.jboss.soa.esb.helpers.persist.JdbcCleanConn.execUpdWait(JdbcCleanConn.java:216)
          at org.jboss.soa.esb.listeners.gateway.SqlTableGatewayListener.changeStatus(SqlTableGatewayListener.java:741)
          at org.jboss.soa.esb.listeners.gateway.SqlTableGatewayListener.changeStatusToWorking(SqlTableGatewayListener.java:681)
          at org.jboss.soa.esb.listeners.gateway.SqlTableGatewayListener.doRun(SqlTableGatewayListener.java:176)
          at org.jboss.soa.esb.listeners.lifecycle.AbstractThreadedManagedLifecycle.run(AbstractThreadedManagedLifecycle.java:115)
          at java.lang.Thread.run(Thread.java:619)
      2010-06-08 00:09:47,209 ERROR [org.jboss.soa.esb.listeners.gateway.SqlTableGatewayListener] Row status change to Working has failed.  Rolling back!!

       

      Just curious to know if its supported in the current version or is it planned in upcoming releases ?


      Thanks,

      -Raghav