10 Replies Latest reply on Mar 13, 2003 3:51 PM by klee

    jdbc2 and Oracle

    Alex Sauve Newbie

      I am trying to use the jdbc2 persistence manager to persist the messages to an Oracle database. This is because the traditional jdbc persistence manager could not create the tables properly. I am running on the RC2 version and I am wondering if jdbc2 has been completely debugged in this version. Anyways here is a short snippet of the error exceptions that I received...

      org.jboss.mq.SpyJMSException: Could not resolve uncommited transactions. Message recovery may not be accurate at org.jboss.mq.pm.jdbc2.PersistenceManager.resolveAllUncommitedTXs(PersistenceManager.java:266)
      at org.jboss.mq.pm.jdbc2.PersistenceManager.startService(PersistenceManager.java:1062)
      at org.jboss.system.ServiceMBeanSupport.start(ServiceMBeanSupport.java:162)
      at sun.reflect.GeneratedMethodAccessor6.invoke(Unknown Source)
      at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
      at java.lang.reflect.Method.invoke(Method.java:324)
      at org.jboss.mx.capability.ReflectedMBeanDispatcher.invoke(ReflectedMBeanDispatcher.java:284)
      at org.jboss.mx.server.MBeanServerImpl.invoke(MBeanServerImpl.java:491)
      at org.jboss.system.ServiceController$ServiceProxy.invoke(ServiceController.java:867)
      at $Proxy0.start(Unknown Source)
      at org.jboss.system.ServiceController.start(ServiceController.java:341)
      at org.jboss.system.ServiceController.start(ServiceController.java:359)
      at org.jboss.system.ServiceController.start(ServiceController.java:359)
      at org.jboss.system.ServiceController.start(ServiceController.java:359)
      at sun.reflect.GeneratedMethodAccessor5.invoke(Unknown Source)
      at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
      at java.lang.reflect.Method.invoke(Method.java:324)
      at org.jboss.mx.capability.ReflectedMBeanDispatcher.invoke(ReflectedMBeanDispatcher.java:284)
      at org.jboss.mx.server.MBeanServerImpl.invoke(MBeanServerImpl.java:491)
      at org.jboss.util.jmx.MBeanProxy.invoke(MBeanProxy.java:174)
      at $Proxy8.start(Unknown Source)
      at org.jboss.resource.RARDeployer.start(RARDeployer.java:212)
      at org.jboss.deployment.MainDeployer.start(MainDeployer.java:692)
      at org.jboss.deployment.MainDeployer.deploy(MainDeployer.java:527)
      at org.jboss.deployment.MainDeployer.deploy(MainDeployer.java:490)
      at sun.reflect.GeneratedMethodAccessor16.invoke(Unknown Source)
      at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
      at java.lang.reflect.Method.invoke(Method.java:324)
      at org.jboss.mx.capability.ReflectedMBeanDispatcher.invoke(ReflectedMBeanDispatcher.java:284)
      at org.jboss.mx.server.MBeanServerImpl.invoke(MBeanServerImpl.java:491)
      at org.jboss.util.jmx.MBeanProxy.invoke(MBeanProxy.java:174)
      at $Proxy4.deploy(Unknown Source)
      at org.jboss.deployment.scanner.URLDeploymentScanner.deploy(URLDeploymentScanner.java:405)
      at org.jboss.deployment.scanner.URLDeploymentScanner.scanDirectory(URLDeploymentScanner.java:586)
      at org.jboss.deployment.scanner.URLDeploymentScanner.scan(URLDeploymentScanner.java:465)
      at org.jboss.deployment.scanner.AbstractDeploymentScanner.startService(AbstractDeploymentScanner.java:237)
      at org.jboss.system.ServiceMBeanSupport.start(ServiceMBeanSupport.java:162)
      at sun.reflect.GeneratedMethodAccessor6.invoke(Unknown Source)
      at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
      at java.lang.reflect.Method.invoke(Method.java:324)
      at org.jboss.mx.capability.ReflectedMBeanDispatcher.invoke(ReflectedMBeanDispatcher.java:284)
      at org.jboss.mx.server.MBeanServerImpl.invoke(MBeanServerImpl.java:491)
      at org.jboss.system.ServiceController$ServiceProxy.invoke(ServiceController.java:867)
      at $Proxy0.start(Unknown Source)
      at org.jboss.system.ServiceController.start(ServiceController.java:341)
      at sun.reflect.GeneratedMethodAccessor5.invoke(Unknown Source)
      at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
      at java.lang.reflect.Method.invoke(Method.java:324)
      at org.jboss.mx.capability.ReflectedMBeanDispatcher.invoke(ReflectedMBeanDispatcher.java:284)
      at org.jboss.mx.server.MBeanServerImpl.invoke(MBeanServerImpl.java:491)
      at org.jboss.util.jmx.MBeanProxy.invoke(MBeanProxy.java:174)
      at $Proxy3.start(Unknown Source)
      at org.jboss.deployment.SARDeployer.start(SARDeployer.java:281)
      at org.jboss.deployment.MainDeployer.start(MainDeployer.java:692)
      at org.jboss.deployment.MainDeployer.deploy(MainDeployer.java:527)
      at org.jboss.deployment.MainDeployer.deploy(MainDeployer.java:490)
      at org.jboss.deployment.MainDeployer.deploy(MainDeployer.java:472)
      at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
      at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
      at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
      at java.lang.reflect.Method.invoke(Method.java:324)
      at org.jboss.mx.capability.ReflectedMBeanDispatcher.invoke(ReflectedMBeanDispatcher.java:284)
      at org.jboss.mx.server.MBeanServerImpl.invoke(MBeanServerImpl.java:491)
      at org.jboss.system.server.ServerImpl.doStart(ServerImpl.java:320)
      at org.jboss.system.server.ServerImpl.start(ServerImpl.java:218)
      at org.jboss.Main.boot(Main.java:142)
      at org.jboss.Main$1.run(Main.java:375)
      at java.lang.Thread.run(Thread.java:536)
      linked exception is:
      java.sql.SQLException: Invalid column type
      at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:168)
      at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:210)
      at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:273)
      at oracle.jdbc.driver.OraclePreparedStatement.setObject(OraclePreparedStatement.java:2034)
      at oracle.jdbc.driver.OraclePreparedStatement.setObject(OraclePreparedStatement.java:2148)
      at org.jboss.resource.adapter.jdbc.local.LocalPreparedStatement.setObject(LocalPreparedStatement.java:625)
      at org.jboss.mq.pm.jdbc2.PersistenceManager.removeMarkedMessages(PersistenceManager.java:498)
      at org.jboss.mq.pm.jdbc2.PersistenceManager.resolveAllUncommitedTXs(PersistenceManager.java:252)
      at org.jboss.mq.pm.jdbc2.PersistenceManager.startService(PersistenceManager.java:1062)
      at org.jboss.system.ServiceMBeanSupport.start(ServiceMBeanSupport.java:162)
      at sun.reflect.GeneratedMethodAccessor6.invoke(Unknown Source)
      at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
      at java.lang.reflect.Method.invoke(Method.java:324)
      at org.jboss.mx.capability.ReflectedMBeanDispatcher.invoke(ReflectedMBeanDispatcher.java:284)
      at org.jboss.mx.server.MBeanServerImpl.invoke(MBeanServerImpl.java:491)
      at org.jboss.system.ServiceController$ServiceProxy.invoke(ServiceController.java:867)
      at $Proxy0.start(Unknown Source)
      at org.jboss.system.ServiceController.start(ServiceController.java:341)
      at org.jboss.system.ServiceController.start(ServiceController.java:359)
      at org.jboss.system.ServiceController.start(ServiceController.java:359)
      at org.jboss.system.ServiceController.start(ServiceController.java:359)
      at sun.reflect.GeneratedMethodAccessor5.invoke(Unknown Source)
      at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
      at java.lang.reflect.Method.invoke(Method.java:324)
      at org.jboss.mx.capability.ReflectedMBeanDispatcher.invoke(ReflectedMBeanDispatcher.java:284)
      at org.jboss.mx.server.MBeanServerImpl.invoke(MBeanServerImpl.java:491)
      at org.jboss.util.jmx.MBeanProxy.invoke(MBeanProxy.java:174)
      at $Proxy8.start(Unknown Source)
      at org.jboss.resource.RARDeployer.start(RARDeployer.java:212)
      at org.jboss.deployment.MainDeployer.start(MainDeployer.java:692)
      at org.jboss.deployment.MainDeployer.deploy(MainDeployer.java:527)
      at org.jboss.deployment.MainDeployer.deploy(MainDeployer.java:490)
      at sun.reflect.GeneratedMethodAccessor16.invoke(Unknown Source)
      at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
      at java.lang.reflect.Method.invoke(Method.java:324)
      at org.jboss.mx.capability.ReflectedMBeanDispatcher.invoke(ReflectedMBeanDispatcher.java:284)
      at org.jboss.mx.server.MBeanServerImpl.invoke(MBeanServerImpl.java:491)
      at org.jboss.util.jmx.MBeanProxy.invoke(MBeanProxy.java:174)
      at $Proxy4.deploy(Unknown Source)
      at org.jboss.deployment.scanner.URLDeploymentScanner.deploy(URLDeploymentScanner.java:405)
      at org.jboss.deployment.scanner.URLDeploymentScanner.scanDirectory(URLDeploymentScanner.java:586)
      at org.jboss.deployment.scanner.URLDeploymentScanner.scan(URLDeploymentScanner.java:465)
      at org.jboss.deployment.scanner.AbstractDeploymentScanner.startService(AbstractDeploymentScanner.java:237)
      at org.jboss.system.ServiceMBeanSupport.start(ServiceMBeanSupport.java:162)
      at sun.reflect.GeneratedMethodAccessor6.invoke(Unknown Source)
      at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
      at java.lang.reflect.Method.invoke(Method.java:324)
      at org.jboss.mx.capability.ReflectedMBeanDispatcher.invoke(ReflectedMBeanDispatcher.java:284)
      at org.jboss.mx.server.MBeanServerImpl.invoke(MBeanServerImpl.java:491)
      at org.jboss.system.ServiceController$ServiceProxy.invoke(ServiceControlle


      In the jbossmq-service.xml the following is my SqlProperties:


      <depends optional-attribute-name="MessageCache">jboss.mq:service=MessageCache
      <depends optional-attribute-name="DataSource">jboss.jca:service=LocalTxDS,name=OracleDS
      jboss.jca:service=LocalTxCM,name=OracleDS

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






      One more thing out of curiosity, why does the jbossmq persistence manager not use jaws... Now we have to configure our datasource in two seperate locations... if it used jaws there may also be less of a need for these custom sqlProperties

      Thanks for any help that you can give...

      Alex

        • 1. Re: jdbc2 and Oracle
          Hiram Chirino Expert

          I agree.. Jaws should make configuring this guy easier. It should be easy to take the jdbc2 PM and create a jaws PM.

          But if you want to get jdbc2 working, you might want to try to set the BLOB_TYPE to BYTES_BLOB or BINARYSTREAM_BLOB

          Regards,
          HIram

          • 2. Re: jdbc2 and Oracle
            Alex Sauve Newbie

            Unfortunately I don't have the time to write a persistence manager that uses jaws, but perhaps in the future...

            But for now I tried using the BYTES_BLOB and I still get the same result... I don't really know if that is the area of concern right now... Looking at the source you find that the persistence manager is trying to resolve the uncommited messages and delete them and then retrive the next MessageID number... The blobType only is used when you are either extracting or adding a message to the DS... Right now I am trying to determine if one of the SQL statements is wrong... I really have to get this working with Oracle...

            Later,
            Alex

            • 3. Re: jdbc2 and Oracle
              Alex Sauve Newbie

              Well I think that I have made some progress that you should know about... THe problem is the jbdc driver for Oracle does not let you set the TXID using the setObject function call... Because TXID is an interger column you should be using the setNull (1, java.sql.Types.BIGINT)... What I did is that I searched through the file and found all occurances of the setObject with the null... the service seems to start up properly but I will have to do some more testing with the storing of the messages and that... But that is the fix if anyone is interested... Sorry that I am not commiting the fix but I am not an official developer for JBoss....

              Later,
              Alex

              • 4. Re: jdbc2 and Oracle
                Hiram Chirino Expert

                please do a "cvs diff -u" and post the differences!

                • 5. Re: jdbc2 and Oracle
                  Alex Sauve Newbie

                  Can't easily do that as there are a lot of debug exception catching that I was doing to try and pin point the issue. But as I said before the only functional changes were:

                  The lines with:
                  stmt.setObject(1, null);
                  should be changed to:
                  stmt.setNull(1, java.sql.Types.BIGINT);

                  If there is still some uncertainty with this then I will post a list of modifications...

                  Later,
                  Alex

                  • 6. Re: jdbc2 and Oracle
                    Alex Sauve Newbie

                    Hi so now the service is started but I am having a hard time trying to persist the messages to the database...

                    I set the MessageCache to 0 for high and max... so essentially there should not be a message cache... I wrote to the testDurableTopic but again nothing in the database and when I finally deployed a bean to subscribe to that topic none of the messages where processed... Something is not right here... IF you have any ideas to get this beast to persist to the Oracle DS...

                    Later,
                    Alex

                    • 7. Re: jdbc2 and Oracle
                      Alex Sauve Newbie

                      Another problem that I see is that I want the MEssageCache to store the messages into the Persistent store... But by default it will write to the CacheStore which is also found on the filesystem (note that it does write any messages there either)... But Persistent store implements CacheStore so it should be able to be used in MessageCache but that will create a cyclic dependency as the Persistence Store depends on the MessageCache... I am confused of when the Persistent message store is used and when the CacheStore is used... I would rather all messages being writen to the database... I was hoping that if machine A has a destination A but no subscriber for destination A then it would write the message to the database... And if machine B has a subscriber for destination A then that subscriber would get the message from the database and process the request... Is this possible???? Thanks for all your help...

                      Alex

                      • 8. Re: jdbc2 and Oracle
                        Hiram Chirino Expert

                        > Another problem that I see is that I want the
                        > MEssageCache to store the messages into the
                        > Persistent store... But by default it will write to
                        > the CacheStore which is also found on the filesystem
                        > (note that it does write any messages there
                        > either)... But Persistent store implements CacheStore
                        > so it should be able to be used in MessageCache but
                        > that will create a cyclic dependency as the
                        > Persistence Store depends on the MessageCache... I
                        > am confused of when the Persistent message store is
                        > used and when the CacheStore is used... I would

                        Just change the:
                        <depends optional-attribute-name="CacheStore">jboss.mq:service=CacheStore
                        to a:
                        jboss.mq:service=PersistenceManager

                        This will make the database act also as the CacheStore.

                        Regards,
                        Hiram

                        • 9. Re: jdbc2 and Oracle
                          Hiram Chirino Expert

                          I just fished commiting the fixes you pointed out along with a few other fixes. I've tested against an Oracle DB and everything seems to be working good.

                          Please get a fresh copy out of CVS and restest with the attached config file for jboss-mq.

                          • 10. Re: jdbc2 and Oracle
                            klee Newbie

                            I am trying to use jdbc2 persistence in Jboss 3.0.6 to Sybase. PersistenceManager failed starting.
                            The exception is ...

                            org.jboss.mq.SpyJMSException: Could not resolve uncommited transactions. Message recovery may not be accurate; - nested throwable: (com.sybase.jdbc2.jdbc.SybSQLException: Invalid column name 'TXID')
                            at org.jboss.mq.pm.jdbc2.PersistenceManager.resolveAllUncommitedTXs(PersistenceManager.java:288)
                            at org.jboss.mq.pm.jdbc2.PersistenceManager.startService(PersistenceManager.java:1129)
                            at org.jboss.system.ServiceMBeanSupport.start(ServiceMBeanSupport.java:165)
                            at sun.reflect.GeneratedMethodAccessor4.invoke(Unknown Source)
                            at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
                            at java.lang.reflect.Method.invoke(Method.java:324)


                            My mbean in jbossmq-service.xml is ....


                            <depends optional-attribute-name="MessageCache">jboss.mq:service=MessageCache
                            <depends optional-attribute-name="DataSource">jboss.jca:service=LocalTxDS,name=SybaseDS
                            jboss.jca:service=LocalTxCM,name=SybaseDS

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



                            Since there is no BLOB in Sybase, so I use BINARY type in MESSAGEBLOB column, and use BLOB_TYPE=BINARYSTREAM_BLOB.

                            I have examined the source code of org.jboss.mq.pm.jdbc2.PersistenceManager.java, stmt.setNull() bug has been fixed.

                            Any input to get this work in Sybase will be appreciated.

                            Thanks.
                            kslee