4 Replies Latest reply on Nov 10, 2004 9:55 AM by lunxian

    Problem about change the database used by JBossMQ from Hyper

      I want to change the database used by JBossMQ from Hypersonic to SQLServer 2000. i started from a clean jboss-3.2.6, and followed the http://www.jboss.org/wiki/Wiki.jsp?page=ConfigJBossMQDB to setup, but some unexpected things happend:table JMS_USERS was created, but JMS_ROLE and JMS_SUBSCRIPTIONS was not, I was so puzzled.

      who succeed to change the database used by JBossMQ from Hypersonic to SQLServer 2000?

      Give me a hand, thanks.

        • 1. Re: Problem about change the database used by JBossMQ from H
          genman


          You can create those tables yourself. Also, turning on DEBUG and watching the logs might help.

          • 2. Re: Problem about change the database used by JBossMQ from H

            Here is some log maybe about it:

            2004-11-05 11:21:14,500 DEBUG [org.jboss.system.ServiceConfigurator] considering ConnectionManager with object name jboss.jca:service=LocalTxCM,name=SAMDS
            2004-11-05 11:21:14,515 DEBUG [org.jboss.system.ServiceConfigurator] SqlProperties set to 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=?
            POPULATE.TABLES.01 = INSERT INTO JMS_USERS (USERID, PASSWD) VALUES ('guest', 'guest')
            POPULATE.TABLES.02 = INSERT INTO JMS_USERS (USERID, PASSWD) VALUES ('j2ee', 'j2ee')
            POPULATE.TABLES.03 = INSERT INTO JMS_USERS (USERID, PASSWD, CLIENTID) VALUES ('john', 'needle', 'DurableSubscriberExample')
            POPULATE.TABLES.04 = INSERT INTO JMS_USERS (USERID, PASSWD) VALUES ('nobody', 'nobody')
            POPULATE.TABLES.05 = INSERT INTO JMS_USERS (USERID, PASSWD) VALUES ('dynsub', 'dynsub')
            POPULATE.TABLES.06 = INSERT INTO JMS_ROLES (ROLEID, USERID) VALUES ('guest','guest')
            POPULATE.TABLES.07 = INSERT INTO JMS_ROLES (ROLEID, USERID) VALUES ('j2ee','guest')
            POPULATE.TABLES.08 = INSERT INTO JMS_ROLES (ROLEID, USERID) VALUES ('john','guest')
            POPULATE.TABLES.09 = INSERT INTO JMS_ROLES (ROLEID, USERID) VALUES ('subscriber','john')
            POPULATE.TABLES.10 = INSERT INTO JMS_ROLES (ROLEID, USERID) VALUES ('publisher','john')
            POPULATE.TABLES.11 = INSERT INTO JMS_ROLES (ROLEID, USERID) VALUES ('publisher','dynsub')
            POPULATE.TABLES.12 = INSERT INTO JMS_ROLES (ROLEID, USERID) VALUES ('durpublisher','john')
            POPULATE.TABLES.13 = INSERT INTO JMS_ROLES (ROLEID, USERID) VALUES ('durpublisher','dynsub')
            POPULATE.TABLES.14 = INSERT INTO JMS_ROLES (ROLEID, USERID) VALUES ('noacc','nobody')
            CREATE_TABLES_ON_STARTUP = TRUE in jboss.mq:service=StateManager

            2004-11-05 11:21:28,437 DEBUG [org.jboss.mq.pm.jdbc2.PersistenceManager] Could not create table with SQL: CREATE TABLE JMS_MESSAGES (MESSAGEID INTEGER NOT NULL, DESTINATION VARCHAR(150) NOT NULL, TXID INTEGER, TXOP CHAR(1), MESSAGEBLOB IMAGE, PRIMARY KEY (MESSAGEID, DESTINATION))
            java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]数据库中已存在名为 'JMS_MESSAGES' 的对象.

            Caused by: java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]object name 'JMS_SUBSCRIPTIONS' invalid.

            • 3. Re: Problem about change the database used by JBossMQ from H

              I think I got it. After I changed driver from microsoft's jdbc driver to sf's jTDS, it workes fine now.

              • 4. Re: Problem about change the database used by JBossMQ from H

                I think I have got it. When I change driver from ms's jdbc driver to sf's jtds, it workes fine.
                I think it's maybe a bug with ms's jdbc driver, or so.