1 Reply Latest reply on Dec 9, 2005 11:08 PM by Adrian Brock

    Sybase configuration for JMS persistence error

    Dave Cox Newbie

      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:

      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.