2 Replies Latest reply on Nov 11, 2002 5:22 AM by Thomas Hübner

    setting up JMS PersistenceManager with MS SQLServer jdbc dri

    Thomas Hübner Newbie

      Hi all,

      i'm using JBoss 3.0.3 with a SQLServer2000 DB and the MS JDBC driver and tried to setup a jdbc based PersistenceManager, for storing jms messages, which uses a ms sqlserver datasource. But i always get the following error when a message should be stored:


      10:22:46,633 WARN [OILServerILService] Client request resulted in a server exception:
      org.jboss.mq.SpyJMSException: Could not store message: 0; - nested throwable: (java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC]Unable to determine the type of the specified object.)
      at org.jboss.mq.pm.jdbc2.PersistenceManager.add(PersistenceManager.java:655)
      at org.jboss.mq.server.PersistentQueue.addMessage(PersistentQueue.java:41)
      at org.jboss.mq.server.JMSTopic.addMessage(JMSTopic.java:228)
      at org.jboss.mq.server.JMSDestinationManager.addMessage(JMSDestinationManager.java:397)
      at org.jboss.mq.server.JMSDestinationManager.addMessage(JMSDestinationManager.java:375)
      at org.jboss.mq.server.JMSServerInterceptorSupport.addMessage(JMSServerInterceptorSupport.java:135)
      at org.jboss.mq.security.ServerSecurityInterceptor.addMessage(ServerSecurityInterceptor.java:155)
      at org.jboss.mq.server.TracingInterceptor.addMessage(TracingInterceptor.java:206)
      at org.jboss.mq.server.JMSServerInvoker.addMessage(JMSServerInvoker.java:137)
      at org.jboss.mq.il.oil.OILServerILService$Client.run(OILServerILService.java:248)
      at java.lang.Thread.run(Thread.java:536)


      The configuration of the PersistenceManger in the jbossmq-service.xml is:



      <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=OBJECT_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 IMAGE, PRIMARY KEY (MESSAGEID, DESTINATION) )
      CREATE_TX_TABLE = CREATE TABLE JMS_TRANSACTIONS ( TXID INTEGER )



      The same datasource works well other cmp beans which also contains binary data like images.
      Has anyone a working configuration for storing jms messages in a ms sqlserver database using the ms sqlserver2000 jdbc driver?

      any help would be great

      thx Thomas

        • 1. Re: setting up JMS PersistenceManager with MS SQLServer jdbc
          Mikael Tollefsen Newbie

          Using jdbc-url
          jdbc:microsoft:sqlserver://localhost:1433;databasename=sms;SelectMethod=cursor

          and the following in jbossmq-service.xml I got it working. It is pretty slow (50%) compared to using MySQL though.


          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(255) NOT NULL, TXID INTEGER, TXOP CHAR(1), \
          MESSAGEBLOB IMAGE, PRIMARY KEY (MESSAGEID, DESTINATION) )
          CREATE_TX_TABLE = CREATE TABLE JMS_TRANSACTIONS ( TXID INTEGER )

          • 2. Re: setting up JMS PersistenceManager with MS SQLServer jdbc
            Thomas Hübner Newbie

            Hi,

            right, this works for me too. But how to know that "BLOB_TYPE" has to be "BINARYSTREAM_BLOB" instead of the hsql default "OBJECT_BLOB" ;-? Digging in jboss source hmm...
            however... it works. Not fast, but that's another topic...

            thx Thomas