4 Replies Latest reply on Sep 10, 2003 3:56 PM by tomdee

    Oracle BLOB/JMS not working.

    tomdee Newbie

      Env: JBoss 3.2.1, Oracle 8.1.7.4.1 , win2K

      From the FAQ it says that to user Oracle BLOB
      with JMS we'd need to use the OCI driver.

      FOr various reasons, we couln't use OCI driver.
      but we are able to use LONG RAW instead of BLOB.
      (even though it's on the list of being desupported)

      The FAQ mentioned that LONG RAW does work but
      I am unable to get JBoss to work properly.


      My jbossmq-service.xml looks like this

       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=?
       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_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 LONG RAW, PRIMARY KEY (MESSAGEID, DESTINATION) )
       CREATE_TX_TABLE = CREATE TABLE JMS_TRANSACTIONS ( TXID INTEGER )
      
      


      When writing to JMS i am getting an invalid column type SQL Exception. Anyone knows how to get
      JBoss to work with LONGRAW ?
      Thanks.





        • 1. Re: Oracle BLOB/JMS not working.
          Stephane Nicoll Master

          if you run the create table SQL yourself, is it working?

          • 2. Re: Oracle BLOB/JMS not working.
            osesm Newbie

            Try this:


            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=?
            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_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 BLOB, PRIMARY KEY (MESSAGEID, DESTINATION) )
            CREATE_TX_TABLE = CREATE TABLE JMS_TRANSACTIONS ( TXID INTEGER )
            CREATE_TABLES_ON_STARTUP = TRUE

            • 3. Re: Oracle BLOB/JMS not working.
              tomdee Newbie

              we narrowed it down to the entity definition inside
              the XML. For ease of deployment we were
              prefixing the JMS tables with a prefix for each
              deployment. (No we can't cluster b/c of customer
              requirements)

              - if we defined a prefix , invalid column type error occurs
              - if we don't use any entity prefix at all, things
              are working

              It looks like for some reason, the entity isn't resolved
              during XML parsing ???


              <!ENTITY PREFIX 'CUST01_' >
              
              .... more xml deleted here on purpose ...
              
              BLOB_TYPE=BINARYSTREAM_BLOB
              INSERT_TX = INSERT INTO &PREFIX;JMS_TRANSACTIONS (TXID) values(?)
              INSERT_MESSAGE = INSERT INTO &PREFIX;JMS_MESSAGES (MESSAGEID, DESTINATION, MESSAGEBLOB, TXID, TXOP) VALUES(?,?,?,?,?)
              SELECT_ALL_UNCOMMITED_TXS = SELECT TXID FROM &PREFIX;JMS_TRANSACTIONS
              SELECT_MAX_TX = SELECT MAX(TXID) FROM &PREFIX;JMS_MESSAGES
              SELECT_MESSAGES_IN_DEST = SELECT MESSAGEID, MESSAGEBLOB FROM &PREFIX;JMS_MESSAGES WHERE DESTINATION=?
              SELECT_MESSAGE = SELECT MESSAGEID, MESSAGEBLOB FROM &PREFIX;JMS_MESSAGES WHERE MESSAGEID=? AND DESTINATION=?
              MARK_MESSAGE = UPDATE &PREFIX;JMS_MESSAGES SET TXID=?, TXOP=? WHERE MESSAGEID=? AND DESTINATION=?
              UPDATE_MESSAGE = UPDATE &PREFIX;JMS_MESSAGES SET MESSAGEBLOB=? WHERE MESSAGEID=? AND DESTINATION=?
              UPDATE_MARKED_MESSAGES = UPDATE &PREFIX;JMS_MESSAGES SET TXID=?, TXOP=? WHERE TXOP=?
              UPDATE_MARKED_MESSAGES_WITH_TX = UPDATE &PREFIX;JMS_MESSAGES SET TXID=?, TXOP=? WHERE TXOP=? AND TXID=?
              DELETE_MARKED_MESSAGES_WITH_TX = DELETE FROM &PREFIX;JMS_MESSAGES WHERE TXID IN (SELECT TXID FROM &PREFIX;JMS_TRANSACTIONS) AND TXOP=?
              DELETE_TX = DELETE FROM &PREFIX;JMS_TRANSACTIONS WHERE TXID = ?
              DELETE_MARKED_MESSAGES = DELETE FROM &PREFIX;JMS_MESSAGES WHERE TXID=? AND TXOP=?
              DELETE_MESSAGE = DELETE FROM &PREFIX;JMS_MESSAGES WHERE MESSAGEID=? AND DESTINATION=?
              CREATE_MESSAGE_TABLE = CREATE TABLE &PREFIX;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 &PREFIX;JMS_TRANSACTIONS ( TXID INTEGER )
              


              • 4. Re: Oracle BLOB/JMS not working.
                tomdee Newbie

                When I look in the jmx-console this is what i am seeing

                without using entity prefix

                ##Wed Sep 10 14:26:18 PDT 2003UPDATE_MARKED_MESSAGES=UPDATE JMS_MESSAGES SET TXID\=?, TXOP\=? WHERE TXOP\=?SELECT_MAX_TX=SELECT MAX(TXID) FROM JMS_MESSAGESDELETE_MARKED_MESSAGES=DELETE FROM JMS_MESSAGES WHERE TXID\=? AND TXOP\=?DELETE_MARKED_MESSAGES_WITH_TX=DELETE FROM JMS_MESSAGES WHERE TXID IN (SELECT TXID FROM JMS_TRANSACTIONS) AND TXOP\=?SELECT_MESSAGES_IN_DEST=SELECT MESSAGEID, MESSAGEBLOB FROM JMS_MESSAGES WHERE DESTINATION\=?CREATE_TABLES_ON_STARTUP=falseSELECT_ALL_UNCOMMITED_TXS=SELECT TXID FROM JMS_TRANSACTIONSDELETE_TX=DELETE FROM JMS_TRANSACTIONS WHERE TXID \= ?CREATE_MESSAGE_TABLE=CREATE TABLE JMS_MESSAGES ( MESSAGEID INTEGER NOT NULL, DESTINATION VARCHAR(255) NOT NULL, TXID INTEGER, TXOP CHAR(1), MESSAGEBLOB LONG RAW, PRIMARY KEY (MESSAGEID, DESTINATION) )SELECT_MESSAGE=SELECT MESSAGEID, MESSAGEBLOB FROM JMS_MESSAGES WHERE MESSAGEID\=? AND DESTINATION\=?UPDATE_MARKED_MESSAGES_WITH_TX=UPDATE JMS_MESSAGES SET TXID\=?, TXOP\=? WHERE TXOP\=? AND TXID\=?BLOB_TYPE=BINARYSTREAM_BLOBUPDATE_MESSAGE=UPDATE JMS_MESSAGES SET MESSAGEBLOB\=? WHERE MESSAGEID\=? AND DESTINATION\=?DELETE_MESSAGE=DELETE FROM JMS_MESSAGES WHERE MESSAGEID\=? AND DESTINATION\=?CREATE_TX_TABLE=CREATE TABLE JMS_TRANSACTIONS ( TXID INTEGER )MARK_MESSAGE=UPDATE JMS_MESSAGES SET TXID\=?, TXOP\=? WHERE MESSAGEID\=? AND DESTINATION\=?INSERT_TX=INSERT INTO JMS_TRANSACTIONS (TXID) values(?)INSERT_MESSAGE=INSERT INTO JMS_MESSAGES (MESSAGEID, DESTINATION, MESSAGEBLOB, TXID, TXOP) VALUES(?,?,?,?,?)
                


                with entity prefix
                ##Wed Sep 10 14:39:07 PDT 2003JMS_TRANSACTIONS=( TXID INTEGER )
                


                so it looks like somehow the entity isn't being resolved.

                I am using JDK 1.4.2 , xalan 2.5.1 and its bundled
                xercesImpl.

                Due to the bug in JDK 1.4.x (xalan embedded in
                rt.jar), I am overidding the default xalan by
                using -Xbootclasspath when launching java. Not sure
                if this is causing this problem or not or
                it's just a bug somewhere