JMS Oracle store with per server destinations

[isotope1145]

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

[pvamstel]

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

Works great for me

[genman]

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.

[xibin]

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

[ianh]

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>

[lucsky]

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>

[lucsky]

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

[genman]

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