-
1. Re: JMS Oracle store with per server destinations
pvamstel Nov 20, 2003 12:55 AM (in response to isotope1145)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
genman Nov 20, 2003 1:06 AM (in response to isotope1145)
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 Mar 16, 2004 1:28 PM (in response to isotope1145)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
ianh Mar 16, 2004 2:20 PM (in response to isotope1145)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
lucsky Apr 20, 2004 11:31 PM (in response to isotope1145)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
lucsky Apr 20, 2004 11:59 PM (in response to isotope1145)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
genman Apr 21, 2004 2:56 AM (in response to isotope1145)
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