1 Reply Latest reply on May 30, 2003 3:39 AM by Stephane Nicoll

    How to persist JMS Messages in database

    jbosssurendra Newbie

      Hi Friends can any one know how to persist the JMS Messages in to the database.

      How to modify the below code , my database name is say testdb, username is guest,password is guestt and I am using msql fourth type of driver.

      please help me in this regard



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

      BLOB_TYPE=OBJECT_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 OBJECT, PRIMARY KEY (MESSAGEID, DESTINATION) )
      CREATE_TX_TABLE = CREATE TABLE JMS_TRANSACTIONS ( TXID INTEGER )



      any help will be appriciated.

        • 1. Re: How to persist JMS Messages in database
          Stephane Nicoll Master

          I am not sure it's a good place for this but anyway ...

          Just defines a datasource with your mysql DB and change the hsqldb in the snipper below to your ds.

          To create a ds (jboss 3.2.X) just drop an xml file ending with -ds.xml in the deploy directory

          <?xml version="1.0" encoding="UTF-8"?>


          <!-- Data source for the Postgres JBossMQ PM java:/jmsDS
          Database: jms
          User: mqagent
          Password: mqagent
          -->
          <local-tx-datasource>
          <jndi-name>jmsDS</jndi-name>
          <connection-url>jdbc:postgresql://kpserverdb/jms</connection-url>
          <driver-class>org.postgresql.Driver</driver-class>
          <user-name>mqagent</user-name>
          mqagent
          <min-pool-size>0</min-pool-size>
          <max-pool-size>30</max-pool-size>
          <blocking-timeout-millis>5000</blocking-timeout-millis>
          <idle-timeout-minutes>15</idle-timeout-minutes>
          </local-tx-datasource>



          Any question let me know