Hi,

 

I am busy investigating how the transaction management works within JBoss ESB. What I have discovered so far tells me that the transactional boundaries are dictated by the particular provider that the service is configured to listen to.

 

I have tested this theory by integrating hibernate and spring into one of my actions and verifying that transactions roll back correctly when a later action in the pipeline throws an un-checked exception. I now need to verify the rest of the providers that we intend on using.

 

The very first one I chose was SQLprovider, which uses the SqlTableCourier. I am trying to get the helloworld_tx_sql_action quickstart example working in our environment, which specifically includes postgres 8.4.

 

In order to make the example compatible with postgres I had to change ‘clob’ to ‘text’, the resulting  sql creation statement is:

 

CREATE TABLE TX_ESB_MESSAGES

(

message_id VARCHAR(255) NOT NULL,

data_column text NOT NULL,

status_col VARCHAR(255) NOT NULL,

timestamp_col VARCHAR(255)

);

 

My ESB service definition looks as follows:

 

<jbossesb parameterReloadSecs="5"

      xmlns="http://anonsvn.labs.jboss.com/labs/jbossesb/trunk/product/etc/schemas/xml/jbossesb-1.0.1.xsd"

      xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"

      xsi:schemaLocation="http://anonsvn.labs.jboss.com/labs/jbossesb/trunk/product/etc/schemas/xml/jbossesb-1.0.1.xsd http://anonsvn.labs.jboss.com/labs/jbossesb/trunk/product/etc/schemas/xml/jbossesb-1.0.1.xsd">

      <providers>

            <sql-provider name="SQLprovider"

                        url="jdbc:postgresql://localhost:5432/rlog"

                        datasource="java:/RLogDB">

            <sql-bus busid="TxSQLChannel" >

                  <sql-message-filter

                        tablename="TX_ESB_MESSAGES"

                              message-id-column="MESSAGE_ID"

                        status-column="STATUS_COL"

                              insert-timestamp-column="TIMESTAMP_COL"

                              message-column="DATA_COLUMN"

                  />

            </sql-bus>

          </sql-provider>

      </providers>

      <services>

            <service category="ParserActions" description="Entrypoint with rollback"

                  invmScope="NONE" invmTransacted="true" name="EntryPointAction">

                  <listeners>

                        <sql-listener name="TxSqlListener" busidref="TxSQLChannel">

                              <property name="transacted" value="true"/>

                </sql-listener>    

                  </listeners>

                  <actions mep="OneWay">

                        <action class="za.co.fnb.sqlprovider.actions.entrypoint.ParserAction" name="ParserAction" process="process">

                              <property name="springContextXml" value="applicationContext.xml"/>

                        </action>

                  </actions>

            </service>

      </services>

</jbossesb>

 

I then run the system and enter a manual row into the database with the status value of ‘P’, I then get repeated exceptions thrown:

 

09:07:15,080 WARN  [SqlTableCourier] Exception during pickup

org.postgresql.util.PSQLException: ERROR: operator does not exist: character varying = bigint

                at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:1548)

                at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1316)

                at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:191)

                at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:452)

                at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:351)

                at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:255)

                at sun.reflect.GeneratedMethodAccessor308.invoke(Unknown Source)

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

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

                at org.postgresql.ds.jdbc23.AbstractJdbc23PooledConnection$StatementHandler.invoke(AbstractJdbc23PooledConnection.java:471)

                at $Proxy327.executeQuery(Unknown Source)

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

                at org.jboss.internal.soa.esb.couriers.SqlTableCourier.tryToPickup(SqlTableCourier.java:368)

                at org.jboss.internal.soa.esb.couriers.SqlTableCourier.pickup(SqlTableCourier.java:261)

                at org.jboss.internal.soa.esb.couriers.TwoWayCourierImpl.pickup(TwoWayCourierImpl.java:228)

                at org.jboss.internal.soa.esb.couriers.TwoWayCourierImpl.pickup(TwoWayCourierImpl.java:204)

                at org.jboss.soa.esb.listeners.message.MessageAwareListener.waitForEventAndProcess(MessageAwareListener.java:297)

                at org.jboss.soa.esb.listeners.message.MessageAwareListener.doRun(MessageAwareListener.java:253)

                at org.jboss.soa.esb.listeners.lifecycle.AbstractThreadedManagedLifecycle.run(AbstractThreadedManagedLifecycle.java:115)

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

09:07:15,081 WARN  [MessageAwareListener] Error processing courier, backing off for 8000 milliseconds

 

I think the cause is that postgres no longer supports implicit type casting. I notice that timestamp field is handled as long:

SqlTableCourier.java:368: selectPicukupMessageStatement.setLong(3, prospect.timestamp);

But the field is defined in the database as a varchar.

 

I have tried to change the database field to a bigint, but then the courier never tries to pick it up?

 

So, basically I have two questions:

1.       Does anyone have a solution to this problem?

2.       I am going to try solve it myself, however I am not sure how to add new provider types to the ESB, does anyone know how to do this? Is there a guide? Do I need to rebuild JBoss ESB to achieve this?

 

I am sending this to both user lists in the hope that someone will be able to help me out..

 

Thanks in advance!