7 Replies Latest reply on Apr 21, 2004 2:56 AM by Elias Ross

    JMS Oracle store with per server destinations

    isotope1145 Newbie

      We would like to switch from the hypersonic JMS store to an Oracle store (this forum often recommends a "real" database for JMS). Our developers each have their own JBoss server but share a single Oracle database. Unfortunately, this means that developers end a JMS datastore and no longer enjoy the sandbox that the per server hypersonic database provides.

      How can I create separate message queues for each developer server?

      I tried to create a separate destination for each developer via the jboss.xml deployment descriptor:

      <enterprise-beans>
      <message-driven>
      ...
      <destination-jndi-name>bfx.queue.spooler.jobMessage.${jboss.bind.address}<destination-jndi-name>
      ...

      But this did NOT work. Any ideas?

      We are using MessageDrivenBeans with jboss-3.2.2 and JDK 1.4.2

        • 1. Re: JMS Oracle store with per server destinations
          patrick Novice

          Why not use mysql 4 for a database locally and oracle in the test env.

          Works great for me

          • 2. Re: JMS Oracle store with per server destinations
            Elias Ross Master


            I didn't quite follow everything you wrote...

            If you are sharing one Oracle between X JBoss, then each JBoss installation needs to have its own set of tables. Either each set of tables needs to belong to different logins, or you have to choose unique table names per server.

            If you have one JBoss installation and X clients and one Oracle (like I think you are suggesting), then you can create multiple persistence managers, caches, transports, etc., by duplicating the 'jms' directory and changing the ObjectNames and ports to be unique.

            • 3. Re: JMS Oracle store with per server destinations
              Xibin Zeng Newbie

              We encountered the same situation. We are planning to support both MS SQL and Oracle as JMS persistence store. Based on JBoss's design, looks like each node in the cluster must access different tables in the database. Since all our servers use the same datasource configuration, that means they will have to be in the same database...

              If anybody has another solution, please share it with the rest.

              Thanks

              • 4. Re: JMS Oracle store with per server destinations
                ian Newbie

                Just like genman has suggested, you can use the same datasource config file but different table name withing the same Oracle SID or schema

                eg.

                JMS_MESSAGES, JMS_TRANSACTIONS vs.
                NODEX_JMS_MESSAGES , NODEX_JMS_TRANSACTIONS

                You can specify the table that JMS uses in jboss-mqservice.xml.




                <mbean code="org.jboss.mq.pm.jdbc2.PersistenceManager"
                 name="jboss.mq:service=PersistenceManager">
                 <depends optional-attribute-name="MessageCache">jboss.mq:service=MessageCache</depends>
                 <depends optional-attribute-name="ConnectionManager">jboss.jca:service=LocalTxCM,name=OracleDS</depends>
                 <attribute name="SqlProperties">
                 BLOB_TYPE=BINARYSTREAM_BLOB
                 INSERT_TX = INSERT INTO NODEX_JMS_TRANSACTIONS (TXID) values(?)
                 INSERT_MESSAGE = INSERT INTO NODEX_JMS_MESSAGES (MESSAGEID, DESTINATION, MESSAGEBLOB, TXID, TXOP) VALUES(?,?,?,?,?)
                 SELECT_ALL_UNCOMMITED_TXS = SELECT TXID FROM NODEX_JMS_TRANSACTIONS
                 SELECT_MAX_TX = SELECT MAX(TXID) FROM NODEX_JMS_MESSAGES
                 SELECT_MESSAGES_IN_DEST = SELECT MESSAGEID, MESSAGEBLOB FROM NODEX_JMS_MESSAGES WHERE DESTINATION=?
                 SELECT_MESSAGE = SELECT MESSAGEID, MESSAGEBLOB FROM NODEX_JMS_MESSAGES WHERE MESSAGEID=? AND DESTINATION=?
                 MARK_MESSAGE = UPDATE NODEX_JMS_MESSAGES SET TXID=?, TXOP=? WHERE MESSAGEID=? AND DESTINATION=?
                 UPDATE_MESSAGE = UPDATE NODEX_JMS_MESSAGES SET MESSAGEBLOB=? WHERE MESSAGEID=? AND DESTINATION=?
                 UPDATE_MARKED_MESSAGES = UPDATE NODEX_JMS_MESSAGES SET TXID=?, TXOP=? WHERE TXOP=?
                 UPDATE_MARKED_MESSAGES_WITH_TX = UPDATE NODEX_JMS_MESSAGES SET TXID=?, TXOP=? WHERE TXOP=? AND TXID=?
                 DELETE_MARKED_MESSAGES_WITH_TX = DELETE FROM NODEX_JMS_MESSAGES WHERE TXID IN (SELECT TXID FROM NODEX_JMS_TRANSACTIONS) AND TXOP=?
                 DELETE_TX = DELETE FROM NODEX_JMS_TRANSACTIONS WHERE TXID = ?
                 DELETE_MARKED_MESSAGES = DELETE FROM NODEX_JMS_MESSAGES WHERE TXID=? AND TXOP=?
                 DELETE_MESSAGE = DELETE FROM NODEX_JMS_MESSAGES WHERE MESSAGEID=? AND DESTINATION=?
                 CREATE_MESSAGE_TABLE = CREATE TABLE NODEX_JMS_MESSAGES ( MESSAGEID INTEGER NOT NULL, \
                 DESTINATION VARCHAR(255) NOT NULL, TXID INTEGER, TXOP CHAR(1), \
                 MESSAGEBLOB LONG RAW, PRIMARY KEY (MESSAGEID, DESTINATION) )
                 CREATE_TX_TABLE = CREATE TABLE NODEX_JMS_TRANSACTIONS ( TXID INTEGER )
                 CREATE_TABLES_ON_STARTUP = false
                 </attribute>
                 </mbean>
                
                


                • 5. Re: JMS Oracle store with per server destinations
                  LUCIANO HARA Newbie

                  Another idea is to use an additional column to identify your different users, and each user should have his/her own datasource *-ds.xml with his/her Oracle user in tag <user-name>.

                  Example :

                  oracle-ds.xml

                  <datasources>
                   <local-tx-datasource>
                   <jndi-name>OracleDS</jndi-name>
                   <connection-url>jdbc:oracle:thin:@youroraclehost:1521:yoursid</connection-url>
                   <driver-class>oracle.jdbc.driver.OracleDriver</driver-class>
                   <user-name>SCOTT</user-name>
                   <password>TIGER</password>
                   </local-tx-datasource>
                  </datasources>
                  


                  jboss-mqservice.xml

                  <mbean code="org.jboss.mq.pm.jdbc2.PersistenceManager"
                   name="jboss.mq:service=PersistenceManager">
                   <depends optional-attribute-name="MessageCache">jboss.mq:service=MessageCache</depends>
                   <depends optional-attribute-name="ConnectionManager">jboss.jca:service=LocalTxCM,name=OracleDS</depends>
                   <attribute name="SqlProperties">
                   BLOB_TYPE=BINARYSTREAM_BLOB
                   INSERT_TX = INSERT INTO JMS_TRANSACTIONS (TXID,USERNAME) values(?,USER)
                   INSERT_MESSAGE = INSERT INTO JMS_MESSAGES (MESSAGEID, DESTINATION, MESSAGEBLOB, TXID, TXOP, USERNAME) VALUES(?,?,?,?,?,USER)
                   SELECT_ALL_UNCOMMITED_TXS = SELECT TXID FROM JMS_TRANSACTIONS WHERE USERNAME=USER
                   SELECT_MAX_TX = SELECT MAX(TXID) FROM JMS_MESSAGES WHERE USERNAME=USER
                   SELECT_MESSAGES_IN_DEST = SELECT MESSAGEID, MESSAGEBLOB FROM JMS_MESSAGES WHERE DESTINATION=? AND USERNAME = USER
                   SELECT_MESSAGE = SELECT MESSAGEID, MESSAGEBLOB FROM JMS_MESSAGES WHERE MESSAGEID=? AND DESTINATION=? AND USERNAME = USER
                   MARK_MESSAGE = UPDATE JMS_MESSAGES SET TXID=?, TXOP=? WHERE MESSAGEID=? AND DESTINATION=? AND USERNAME = USER
                   UPDATE_MESSAGE = UPDATE JMS_MESSAGES SET MESSAGEBLOB=? WHERE MESSAGEID=? AND DESTINATION=? AND USERNAME = USER
                   UPDATE_MARKED_MESSAGES = UPDATE JMS_MESSAGES SET TXID=?, TXOP=? WHERE TXOP=? AND USERNAME = USER
                   UPDATE_MARKED_MESSAGES_WITH_TX = UPDATE JMS_MESSAGES SET TXID=?, TXOP=? WHERE TXOP=? AND TXID=? AND USERNAME = USER
                   DELETE_MARKED_MESSAGES_WITH_TX = DELETE FROM JMS_MESSAGES WHERE TXID IN (SELECT TXID FROM JMS_TRANSACTIONS) AND TXOP=? AND USERNAME = USER
                   DELETE_TX = DELETE FROM JMS_TRANSACTIONS WHERE TXID = ? AND USERNAME = USER
                   DELETE_MARKED_MESSAGES = DELETE FROM JMS_MESSAGES WHERE TXID=? AND TXOP=? AND USERNAME = USER
                   DELETE_MESSAGE = DELETE FROM JMS_MESSAGES WHERE MESSAGEID=? AND DESTINATION=? AND USERNAME = USER
                   CREATE_MESSAGE_TABLE = CREATE TABLE JMS_MESSAGES ( MESSAGEID INTEGER NOT NULL, \
                   DESTINATION VARCHAR(255) NOT NULL, TXID INTEGER, TXOP CHAR(1), USERNAME VARCHAR2(50), \
                   MESSAGEBLOB LONG RAW, PRIMARY KEY (MESSAGEID, DESTINATION) )
                   CREATE_TX_TABLE = CREATE TABLE JMS_TRANSACTIONS ( TXID INTEGER, USERNAME VARCHAR2(50) )
                   CREATE_TABLES_ON_STARTUP = false
                   </attribute>
                   </mbean>
                  
                  


                  • 6. Re: JMS Oracle store with per server destinations
                    LUCIANO HARA Newbie

                    Sorry for the mistake :

                    DELETE_MARKED_MESSAGES_WITH_TX = DELETE FROM JMS_MESSAGES WHERE TXID IN (SELECT TXID FROM JMS_TRANSACTIONS AND USERNAME = USER) AND TXOP=? AND USERNAME = USER
                    


                    • 7. Re: JMS Oracle store with per server destinations
                      Elias Ross Master


                      Another hint:

                      You can put ${somevar} and it gets evaluated/substituted in the *-service.xml file, where the variable 'somevar' is a system property. You can set the system property on startup by using -Dsomevar=somevalue.

                      So, as part of the startup script, you can set the ENV variable:

                      $ export JAVA_OPTS="-Duser=joe"
                      $ run.sh