[esb-users] SqlTableCourier

Fri Feb 26 02:15:58 EST 2010

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/sch
emas/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/pr
oduct/etc/schemas/xml/jbossesb-1.0.1.xsd
http://anonsvn.labs.jboss.com/labs/jbossesb/trunk/product/etc/schemas/xm
l/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(QueryExecu
torImpl.java:1548)

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

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

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

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

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

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

                at
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessor
Impl.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(Wr
appedPreparedStatement.java:342)

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

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

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

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

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

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

                at
org.jboss.soa.esb.listeners.lifecycle.AbstractThreadedManagedLifecycle.r
un(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!

To read FirstRand Bank's Disclaimer for this email click on the following address or copy into your Internet browser: 
https://www.fnb.co.za/disclaimer.html 

If you are unable to access the Disclaimer, send a blank e-mail to
firstrandbankdisclaimer at fnb.co.za and we will send you a copy of the Disclaimer.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.jboss.org/pipermail/esb-users/attachments/20100226/8da8825e/attachment-0001.html 