Join muliple table in JBOSS-ESB sql-provider ?
tvraghavan Jun 7, 2010 3:07 PMIs 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