3 Replies Latest reply on Mar 6, 2006 5:53 PM by D Rickard

    JBoss 4.x fails to create JMS_XXX tables at startup

    Ostap Bender Newbie

      I'm observing some strange behaviour with JBossMQ when JMS persistance is configured to use MS SQL 2000 data source.

      Looks like org.jboss.mq.pm.jdbc2.PersistenceManager creates its tables just fine but org.jboss.mq.sm.jdbc.JDBCStateManager creates only JMS_USERS table. No errors on creation of those missing tables in the log.

      mssql-jdbc2-service.xml

       <mbean code="org.jboss.mq.pm.jdbc2.PersistenceManager"
       name="jboss.mq:service=PersistenceManager">
       <depends optional-attribute-name="ConnectionManager">jboss.jca:service=DataSourceBinding,name=jdbc/ejbPool_mssql4</depends>
       <attribute name="SqlProperties">
       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=? WHERE MESSAGEID=? AND DESTINATION=?
       UPDATE_MESSAGE = UPDATE JMS_MESSAGES SET MESSAGEBLOB=? WHERE MESSAGEID=? AND DESTINATION=?
       UPDATE_MARKED_MESSAGES = UPDATE JMS_MESSAGES SET TXID=?, TXOP=? WHERE TXOP=?
       UPDATE_MARKED_MESSAGES_WITH_TX = UPDATE JMS_MESSAGES SET TXID=?, TXOP=? WHERE TXOP=? AND TXID=?
       UPDATE_MESSAGE = UPDATE JMS_MESSAGES SET MESSAGEBLOB=? WHERE MESSAGEID=? AND DESTINATION=?
       UPDATE_MARKED_MESSAGES = UPDATE JMS_MESSAGES SET TXID=?, TXOP=? WHERE TXOP=?
       UPDATE_MARKED_MESSAGES_WITH_TX = UPDATE JMS_MESSAGES SET TXID=?, TXOP=? WHERE TXOP=? AND TXID=?
       DELETE_MARKED_MESSAGES_WITH_TX = DELETE FROM JMS_MESSAGES WHERE TXID IN (SELECT TXID FROM JMS_TRANSACTIONS) AND TXOP=?
       DELETE_TX = DELETE FROM JMS_TRANSACTIONS WHERE TXID = ?
       DELETE_MARKED_MESSAGES = DELETE FROM JMS_MESSAGES WHERE TXID=? AND TXOP=?
       DELETE_TEMPORARY_MESSAGES = DELETE FROM JMS_MESSAGES WHERE TXOP='T'
       DELETE_MESSAGE = DELETE FROM JMS_MESSAGES WHERE MESSAGEID=? AND DESTINATION=?
       CREATE_MESSAGE_TABLE = CREATE TABLE JMS_MESSAGES (MESSAGEID INTEGER NOT NULL, DESTINATION VARCHAR(150) NOT NULL, TXID INTEGER, TXOP CHAR(1), MESSAGEBLOB IMAGE, PRIMARY KEY (MESSAGEID, DESTINATION))
       CREATE_TX_TABLE = CREATE TABLE JMS_TRANSACTIONS (TXID INTEGER)
       CREATE_TABLES_ON_STARTUP = TRUE
       </attribute>
       </mbean>
      


      mssql-jdbc-state-service.xml
       <mbean code="org.jboss.mq.sm.jdbc.JDBCStateManager"
       name="jboss.mq:service=StateManager">
       <depends optional-attribute-name="ConnectionManager">jboss.jca:service=DataSourceBinding,name=jdbc/ejbPool_mssql4</depends>
       <attribute name="SqlProperties">
       CREATE_TABLES_ON_STARTUP = TRUE
       CREATE_USER_TABLE = CREATE TABLE JMS_USERS (USERID VARCHAR(32) NOT NULL, PASSWD VARCHAR(32) NOT NULL, \
       CLIENTID VARCHAR(128), PRIMARY KEY(USERID))
       CREATE_ROLE_TABLE = CREATE TABLE JMS_ROLES (ROLEID VARCHAR(32) NOT NULL, USERID VARCHAR(32) NOT NULL, \
       PRIMARY KEY(USERID, ROLEID))
       CREATE_SUBSCRIPTION_TABLE = CREATE TABLE JMS_SUBSCRIPTIONS (CLIENTID VARCHAR(128) NOT NULL, \
       SUBNAME VARCHAR(128) NOT NULL, TOPIC VARCHAR(255) NOT NULL, \
       SELECTOR VARCHAR(255), PRIMARY KEY(CLIENTID, SUBNAME))
       GET_SUBSCRIPTION = SELECT TOPIC, SELECTOR FROM JMS_SUBSCRIPTIONS WHERE CLIENTID=? AND SUBNAME=?
       LOCK_SUBSCRIPTION = SELECT TOPIC, SELECTOR FROM JMS_SUBSCRIPTIONS WHERE CLIENTID=? AND SUBNAME=?
       GET_SUBSCRIPTIONS_FOR_TOPIC = SELECT CLIENTID, SUBNAME, SELECTOR FROM JMS_SUBSCRIPTIONS WHERE TOPIC=?
       INSERT_SUBSCRIPTION = INSERT INTO JMS_SUBSCRIPTIONS (CLIENTID, SUBNAME, TOPIC, SELECTOR) VALUES(?,?,?,?)
       UPDATE_SUBSCRIPTION = UPDATE JMS_SUBSCRIPTIONS SET TOPIC=?, SELECTOR=? WHERE CLIENTID=? AND SUBNAME=?
       REMOVE_SUBSCRIPTION = DELETE FROM JMS_SUBSCRIPTIONS WHERE CLIENTID=? AND SUBNAME=?
       GET_USER_BY_CLIENTID = SELECT USERID, PASSWD, CLIENTID FROM JMS_USERS WHERE CLIENTID=?
       GET_USER = SELECT PASSWD, CLIENTID FROM JMS_USERS WHERE USERID=?
       </attribute>
       </mbean>
      


      Any ideas? Am I missing something?

        • 1. Re: JBoss 4.x fails to create JMS_XXX tables at startup
          Ostap Bender Newbie

          Forgot to mention that data source is not an XA.

          • 2. Re: JBoss 4.x fails to create JMS_XXX tables at startup
            Ostap Bender Newbie

            I found the problem. It is in the configuration of JCA actually for MS SQL2000 driver.
            The file mssql-ds.xml from the examples should have the following element set.

            <connection-property name="SelectMethod">cursor</connection-property>
            


            MS has a support article on this.

            http://support.microsoft.com/default.aspx?scid=kb%3Ben-us%3B313181

            • 3. Re: JBoss 4.x fails to create JMS_XXX tables at startup
              D Rickard Newbie

              Just wanted to follow up to this for anyone else who finds it to say "It worked for me, too." Here's an example of a the full mssql-ds.xml file:

              <datasources>
               <local-tx-datasource>
               <jndi-name>MSSQLDS</jndi-name>
               <connection-url>jdbc:microsoft:sqlserver://MyServer;DatabaseName=MyDatabase</connection-url>
               <driver-class>com.microsoft.jdbc.sqlserver.SQLServerDriver</driver-class>
               <user-name>MyUser</user-name>
               <password>MyPassword</password>
               <!-- sql to call when connection is created
               <new-connection-sql>some arbitrary sql</new-connection-sql>
               -->
               <!-- sql to call on an existing pooled connection when it is obtained from pool -->
               <check-valid-connection-sql>Select 1</check-valid-connection-sql>
               <!-- corresponding type-mapping in the standardjbosscmp-jdbc.xml (optional) -->
               <metadata>
               <type-mapping>MS SQLSERVER2000</type-mapping>
               </metadata>
               <connection-property name="SelectMethod">cursor</connection-property>
               </local-tx-datasource>
              </datasources>
              


              Once I added the SelectMethod to be cursor, all the tables were created and populated as specified in mssql-jdbc2-service.xml and mssqldb-jdbc-state-service.xml.

              Now that JBoss and Microsoft are cooperating, I'm sure the example mssql-ds.xml will be updated, right...?