Join muliple table in JBOSS-ESB sql-provider ?

[tvraghavan]

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

[mohankumarc8988]

Hi raghavan,

even I am facing the same problem.

did you resolved the above problem.

If you please let me know how you over come the problem.

Thanks in advance,

Mohan Kumar C

[tvraghavan]

Hi,

No. This is not yet supported by the SQL provider. You can go in for a transactional based approach. If not , you have to reconsider the source table design and flatten the structure.

-Raghav

[mohankumarc8988]

Hi,

Thanks for your reply .

I have another doubt.

I have hello world webservice i can able to call the service but I am not able to give the request to that service.

can you please tell me how to give the request.

Thanks in advance.

Mohan Kumar C

[tcunning]

Does your database support views and triggers?    It seems like you could overcome the two-table problem with a combination of views (for selection) and triggers (for deletion of the records in both tables).