2 Replies Latest reply on Nov 1, 2002 5:06 PM by Scott Williams

    Pls Help!  Oracle 8 and sqlProperties for JMS

    Tom Sawyer Newbie

      Hi there,

      Anyone out there running Jboss 3.0.3 and Oracle 8.1? I am getting this exception when I try to persist my messages to the Oracle database.

      I've included mysqlProperties and the exception. Any help is really appreciated!!!

      ----------------- sqlProperties ---------------------

      <depends optional-attribute-name="MessageCache">jboss.mq:service=MessageCache
      <depends optional-attribute-name="DataSource">jboss.jca:service=LocalTxDS,name=SRSStudent
      jboss.jca:service=LocalTxCM,name=SRSStudent

      BLOB_TYPE=BYTES_BLOB
      INSERT_TX = INSERT INTO JMS_TRANSACTIONS (TXID) values(?)
      INSERT_MESSAGE = INSERT INTO JMS_MESSAGES (MESSAGEID, DESTINATION, MESSAGEBLOB, TXID, TXOP) VALUES(?,?,?,?,?)
      SELECT_ALL_UNCOMMITED_TXS = SELECT TXID FROM JMS_TRANSACTIONS
      SELECT_MAX_TX = SELECT MAX(TXID) FROM JMS_MESSAGES
      SELECT_MESSAGES_IN_DEST = SELECT MESSAGEID, MESSAGEBLOB FROM JMS_MESSAGES WHERE DESTINATION=?
      SELECT_MESSAGE = SELECT MESSAGEID, MESSAGEBLOB FROM JMS_MESSAGES WHERE MESSAGEID=? AND DESTINATION=?
      MARK_MESSAGE = UPDATE JMS_MESSAGES SET (TXID, TXOP) VALUES(?,?) WHERE MESSAGEID=? AND DESTINATION=?
      DELETE_ALL_MESSAGE_WITH_TX = DELETE FROM JMS_MESSAGES WHERE TXID=?
      DELETE_TX = DELETE FROM JMS_TRANSACTIONS WHERE TXID = ?
      DELETE_MARKED_MESSAGES = DELETE FROM JMS_MESSAGES WHERE TXID=? AND TXOP=?
      DELETE_MESSAGE = DELETE FROM JMS_MESSAGES WHERE MESSAGEID=? AND DESTINATION=?
      CREATE_MESSAGE_TABLE = CREATE TABLE JMS_MESSAGES ( MESSAGEID INTEGER NOT NULL, \
      DESTINATION VARCHAR(50) NOT NULL, TXID INTEGER, TXOP CHAR(1), \
      MESSAGEBLOB BLOB, PRIMARY KEY (MESSAGEID, DESTINATION) )
      CREATE_TX_TABLE = CREATE TABLE JMS_TRANSACTIONS ( TXID INTEGER )
      CREATE_TABLES_ON_STARTUP=true



      ---------------- Exception---------------------------
      17:52:40,218 WARN [TxCapsule] XAException: tx=XidImpl [FormatId=257, GlobalId=b
      hh147//21, BranchQual=] errorCode=XAER_RMERR
      javax.transaction.xa.XAException
      at org.jboss.mq.SpyXAResource.commit(SpyXAResource.java:100)
      at org.jboss.tm.TxCapsule.commitResources(TxCapsule.java:1714)
      at org.jboss.tm.TxCapsule.commit(TxCapsule.java:393)
      at org.jboss.tm.TransactionImpl.commit(TransactionImpl.java:73)
      at org.jboss.jms.asf.StdServerSession.onMessage(StdServerSession.java:31
      9)
      at org.jboss.mq.SpyMessageConsumer.sessionConsumerProcessMessage(SpyMess
      ageConsumer.java:603)
      at org.jboss.mq.SpyMessageConsumer.addMessage(SpyMessageConsumer.java:41
      7)
      at org.jboss.mq.SpySession.run(SpySession.java:259)
      at org.jboss.jms.asf.StdServerSession.run(StdServerSession.java:177)
      at EDU.oswego.cs.dl.util.concurrent.PooledExecutor$Worker.run(PooledExec
      utor.java:642)
      at java.lang.Thread.run(Thread.java:536)
      17:52:41,109 WARN [TxCapsule] XAException: tx=XidImpl [FormatId=257, GlobalId=b
      hh147//23, BranchQual=] errorCode=XAER_RMERR
      javax.transaction.xa.XAException
      at org.jboss.mq.SpyXAResource.commit(SpyXAResource.java:100)
      at org.jboss.tm.TxCapsule.commitResources(TxCapsule.java:1714)
      at org.jboss.tm.TxCapsule.commit(TxCapsule.java:393)
      at org.jboss.tm.TransactionImpl.commit(TransactionImpl.java:73)
      at org.jboss.jms.asf.StdServerSession.onMessage(StdServerSession.java:31
      9)
      at org.jboss.mq.SpyMessageConsumer.sessionConsumerProcessMessage(SpyMess
      ageConsumer.java:603)
      at org.jboss.mq.SpyMessageConsumer.addMessage(SpyMessageConsumer.java:41
      7)
      at org.jboss.mq.SpySession.run(SpySession.java:259)
      at org.jboss.jms.asf.StdServerSession.run(StdServerSession.java:177)
      at EDU.oswego.cs.dl.util.concurrent.PooledExecutor$Worker.run(PooledExec
      utor.java:642)
      at java.lang.Thread.run(Thread.java:536)

        • 1. Re: Pls Help!  Oracle 8 and sqlProperties for JMS
          Tom Sawyer Newbie

          I figured out there are some non-standard sql in here that's causing problems in Oracle. I've included the sqlProperties here for Oracle 8 if anyone else want to use this.



          <depends optional-attribute-name="DataSource">jboss.jca:service=LocalTxDS,name=SRSStudent
          jboss.jca:service=LocalTxCM,name=SRSOracle8
          <depends optional-attribute-name="MessageCache">jboss.mq:service=MessageCache

          BLOB_TYPE=BINARYSTREAM_BLOB
          INSERT_TX = INSERT INTO JMS_TRANSACTIONS (TXID) values(?)
          INSERT_MESSAGE = INSERT INTO JMS_MESSAGES (MESSAGEID, DESTINATION, MESSAGEBLOB, TXID, TXOP) VALUES(?,?,?,?,?)
          SELECT_ALL_UNCOMMITED_TXS = SELECT TXID FROM JMS_TRANSACTIONS
          SELECT_MAX_TX = SELECT MAX(TXID) FROM JMS_MESSAGES
          SELECT_MESSAGES_IN_DEST = SELECT MESSAGEID, MESSAGEBLOB FROM JMS_MESSAGES WHERE DESTINATION=?
          SELECT_MESSAGE = SELECT MESSAGEID, MESSAGEBLOB FROM JMS_MESSAGES WHERE MESSAGEID=? AND DESTINATION=?
          MARK_MESSAGE = UPDATE JMS_MESSAGES SET TXID=?, TXOP=? WHERE MESSAGEID=? AND DESTINATION=?
          DELETE_ALL_MESSAGE_WITH_TX = DELETE FROM JMS_MESSAGES WHERE TXID=?
          DELETE_TX = DELETE FROM JMS_TRANSACTIONS WHERE TXID = ?
          DELETE_MARKED_MESSAGES = DELETE FROM JMS_MESSAGES WHERE TXID=? AND TXOP=?
          DELETE_MESSAGE = DELETE FROM JMS_MESSAGES WHERE MESSAGEID=? AND DESTINATION=?
          CREATE_MESSAGE_TABLE = CREATE TABLE JMS_MESSAGES ( MESSAGEID INTEGER NOT NULL, \
          DESTINATION VARCHAR(50) NOT NULL, TXID INTEGER, TXOP CHAR(1), \
          MESSAGEBLOB BLOB, PRIMARY KEY (MESSAGEID, DESTINATION) )
          CREATE_TX_TABLE = CREATE TABLE JMS_TRANSACTIONS ( TXID INTEGER )


          • 2. Re: Pls Help!  Oracle 8 and sqlProperties for JMS
            Scott Williams Newbie

            Thanks for the post. I got the jms queue working with Oracle with your SQL properties.

            I noticed something else though, I was getting a class not found error on the oracle driver class. Prior to using this queue I just put the classes12.jar in the deploy folder and jboss picked up the jar. However, it seemed like this no longer worked once I started to use Oracle to store jms messages. I changed the jboss startup class path to include the oracle jar and everything works fine, but now sure why I can't just drop the classes12.jar in the deploy folder and get this to work.