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.
