I'm trying to setup jboss 4.0.3sp1 to use Sybase as its JMS persistence. I copied the example configuration file from $JBOSS_HOME/docs/examples/jms and edited it to use my datasource. The tables are created correctly, but there is a problem with the SQL defined for SELECT_MAX_TX which results in the following failure:
--- MBEANS THAT ARE THE ROOT CAUSE OF THE PROBLEM ---
ObjectName: jboss.mq:service=PersistenceManager
State: FAILED
Reason: org.jboss.mq.SpyJMSException: Could not resolve uncommited transactions. Message recovery may not be accurate; - nested throwable: (com.sybase.jdbc2.jdbc.SybSQLException: Incorrect syntax near the keyword 'SELECT'.
)
The Sybase manual states that the UNION keyword is not supported in subqueries, which seems to be an issue here. Union does support a select into clause in Sybase, but without writing a stored proc, I can't condense the required SQL into a single call. A case statement will be messy and redundant to accomplish the same thing.
It seems like others are using Sybase for JMS - should I simply limit the scope of the search for a TXID to the JMS_MESSAGES table and remove the union? That seems to be the workaround for the similar problem in MySQL.
Any suggestions would be appreciated.
My suggestion is to read the release notes.
For others: http://www.jboss.com/index.html?module=bb&op=viewtopic&t=72331