Sql-provider cannot update the status field

[rasmusskibye]

Hello.
I have a problem with the sql-provider in the jboss-esb.

Created a simple table:

CREATE table ELECTRONIC_ORDER_TEST(
STATUS varchar2(20),
ORDER_ID number not null,
MESSAGE_ID varchar2(200),
primary key (order_id));


, added the required params to the esb-file:

<sql-provider name="ResourceOrderSQLProvider"
url="jdbc:oracle:thin:@211.211.11.92:1511:test13"
driver="oracle.jdbc.OracleDriver" username="AB" password="AB1234">
<sql-bus busid="ResourceOrderSQLChannel">
<sql-message-filter
tablename="ELECTRONIC_ORDER_TEST" status-column="STATUS" order-by="ORDER_ID"
message-id-column="ORDER_ID"
message-column="MESSAGE_ID" post-delete="false"
error-delete="false"/>
</sql-bus>
</sql-provider>


, added a row to the table:

insert into electronic_order_test values('P',1,'test_text');


, and got the following error:

14:47:14,924 ERROR [SqlTableGatewayListener] Row status change to Working has failed. Rolling back!!


I am able to change the status manually (from P to W), but the esb-service can not do this?


Any help to this problem is very welcome :-)

Regards

Rasmus

[kconner]

"Rasmus Skibye" wrote:
14:47:14,924 ERROR [SqlTableGatewayListener] Row status change to Working has failed. Rolling back!!

This could simply mean that the row is already being processed by another thread.

Which version of ESB are you using?

"Rasmus Skibye" wrote:
Any help to this problem is very welcome :-)

Have you checked the server.log file? Is there an exception logged there at debug level?

[rasmusskibye]

Hello again.

I am using Embedded ESB Server - Version 4.2.0.beta2, but I have specified maxThreads="1" in the esb file.

There are no exceptions in the log file.

Regards.

Rasmus

[kconner]

Can you try updating to a more recent version? We are just about to release ESB 4.3 but 4.2.1 has been out for some time.

[rasmusskibye]

Ok.

I will try that.

BR.

Rasmus

[amcclure]

I am having the same issue and using ESB 4.3.GA.

If I run the db watcher quickstarts (using Hypersonic) all is fine. But as I start to code my own examples (using either SQL Server or MySQL) I recieve the same error.

Do the columns need to be in specific format in the table? Is this documented somewhere?

Any ideas?

Thanks,
Anthony

[amcclure]

I have noticed a new behavior if I have the insert-timestamp-column set in the jboss-esb.xml...

Still have the same message that appears -
Cannot change row state from Pending to Working.

But now followed by -
Number of rows in state Pending = 0.

And repeats for each check of the database.

[tcunning]

There seems to be a bug in upper-case/lower-case handling in the SqlTableGatewayListener. I've logged a bug on the issue here :

http://jira.jboss.com/jira/browse/JBESB-1855

There's a pretty easy workaround for this that I'd suggest using in the meantime though - if you upper-case all of your column names in the sql-message-filter in jboss-esb.xml, you should be okay :

(example from helloworld_sql_action QS :)
<sql-message-filter
tablename="GATEWAY_TABLE"
status-column="STATUS_COL"
order-by="DATA_COLUMN"
where-condition="DATA_COLUMN like 'data%'"
message-column="message"
message-id-column="UNIQUE_ID"
insert-timestamp-column="TIMESTAMP_COL"
/>

[postal]

I'm using JBoss SOA-P, 4.2.0 GA_CP01 / MYSQL, and am running into the ERROR [SqlTableGatewayListener] Row status change to Working has failed. Rolling back!! issue. I have already tried capitlizing my column names but this doesn't seem to be working. Table structure is as follows:

id, filename, status_col, <other columns not used>



From jboss-esb.xml:

 <sql-bus busid="SQLChannel" >
 <sql-message-filter tablename="`TEST`.`TESTDB`"
 message-id-column="ID"
 message-column="message"
 status-column="STATUS_COL"
 />
 </sql-bus>

[postal]

Changed my jboss-esb.xml to this...and it works now.

 <sql-message-filter
 tablename="`TEST`.`TESTDB`"
 status-column="STATUS_COL"
 where-condition="FILENAME like '%'"
 order-by="FILENAME"
 message-column="message"
 message-id-column="ID"
 insert-timestamp-column="TIMESTAMP_COL"
 post-delete='false'
 />

[karypid]

Hello,

I am using JBossESB 4.4GA installed on a JBossAS 4.2.3GA and am experiencing this issue, but only when using the insert-timestamp-column.

That is, the following works:

<sql-bus busid="mySQLChannel">
 <sql-message-filter tablename="INCOMING"
 message-id-column="UNIQUE_ID"
 status-column="STATUS"
 message-column="MESSAGE"
 post-delete="false" />
</sql-bus>

whereas the following does NOT work:

<sql-bus busid="mySQLChannel">
 <sql-message-filter tablename="INCOMING"
 message-id-column="UNIQUE_ID"
 status-column="STATUS"
 message-column="MESSAGE"
 insert-timestamp-column="WHE"
 post-delete="false" />
</sql-bus>

My table is in the default HSQLDB 1.8 data source installed with JBoss 4.2.3 and was created as follows:

CREATE TABLE INCOMING (
 UNIQUE_ID INTEGER,
 STATUS VARCHAR,
 MESSAGE VARCHAR,
 WHE TIMESTAMP,
 PRIMARY KEY (UNIQUE_ID)
 );

Perhaps JBESB-1855 is not quite fixed yet?

[karypid]

It turns out that the problem was the timestamp column type. JBossESB expects it to be of type varchar.