JMS Queue Persistence on SQLServer2000 - Solution
In the past we had some problems persisting JMS queues to SQL2000. We looked at the code, we found the rigth parameters and we finaly made it.
Here is the solution:
+ Install Microsoft JDBC for SQLServer2000
+ Copy JARS
Add jars in MSSQL2000/lib to JBOSS/server/default/lib
+ Configure SQL Server
- Enable SQL Authentication
If you are using SQL Server 2000, you need to go to the SQL Server Enterprise Manager.
You should see a tree similar to this:
Console Root
->MS SQL Server
--->SQL Server Group
------><Machine Name>(Windows NT)
Right click on <Machine Name>(Windows NT)
Choose "Properties"
That should bring up the "SQL Server Properties Configure" Page.
Choose the "Security" Tab
You should see 2 radio buttons under Security Authentication
---> 1. SQL Server and Windows
---> 2. Windows only
Choose option 1.
Create New Database
Create a Database called TL_JBoss_JMS
(just an example name - you can put what you want, just keep it consistent with the naming further on...)
Create New Data Type
On the EnterpriseManager, under the database TL_JBoss_Persistent_Queue on the SQLServer2000, create a new user data type mapping OBJECT to binary fixed size [8000]
+ Configure JBOSS deployment files
In the deployment descriptor add to the JDBC connection string: SelectMethod=cursor
In :
Change OBJECT to IMAGE
assign BLOB_TYPE=OBJECT_BLOB
modify jbossmq-service.xml :
<depends optional-attribute-name="MessageCache">jboss.mq:service=MessageCache
<depends optional-attribute-name="DataSource">jboss.jca:service=LocalTxDS,name=MSSQLDS
jboss.jca:service=LocalTxCM,name=MSSQLDS
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) 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 OBJECT, PRIMARY KEY (MESSAGEID, DESTINATION) )
CREATE_TX_TABLE = CREATE TABLE JMS_TRANSACTIONS ( TXID INTEGER )
modify mssql-service.xml:
<depends optional-attribute-name="ManagedConnectionFactoryName">
<!--embedded mbean-->
MSSQLDS
<config-property name="ConnectionURL" type="java.lang.String">jdbc:microsoft:sqlserver://WSITITAFMD002:1433;DatabaseName=TLTest;SelectMethod=cursor</config-property>
<config-property name="DriverClass" type="java.lang.String">com.microsoft.jdbc.sqlserver.SQLServerDriver</config-property>
<!--set these only if you want only default logins, not through JAAS-->
<config-property name="UserName" type="java.lang.String">ivan</config-property>
<config-property name="Password" type="java.lang.String">test</config-property>
<!--Below here are advanced properties -->
<!--hack-->
<depends optional-attribute-name="OldRarDeployment">jboss.jca:service=RARDeployment,name=JBoss LocalTransaction JDBC Wrapper