4 Replies Latest reply on May 29, 2003 1:16 PM by jbrosenberg

    What's the db schema for jdbc2 persistent?

    jbrosenberg Newbie

      Ok,

      I took a fresh jBoss 3.2.1 installation, and removed the Hypersonic data source, and added the oracle data source (and named it 'DefaultDS')....

      And I didn't modify anything else having to do with
      jms.....

      I get this when the PersistenceManager tries to start up, do I need to create a set of tables in my Oracle db before trying to use JMS? Or does it normally create the necessary tables it needs if they are not there?:

      05:13:43,108 ERROR [PersistenceManager] Starting failed
      org.jboss.mq.SpyJMSException: Could not resolve uncommited transactions. Message recovery may not be accurate; - nested throwable: (java.sq
      l.SQLException: ORA-00942: table or view does not exist
      )
      at org.jboss.mq.pm.jdbc2.PersistenceManager.resolveAllUncommitedTXs(PersistenceManager.java:276)
      at org.jboss.mq.pm.jdbc2.PersistenceManager.startService(PersistenceManager.java:1299)
      at org.jboss.system.ServiceMBeanSupport.start(ServiceMBeanSupport.java:192)
      at sun.reflect.GeneratedMethodAccessor33.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:549)
      at org.jboss.system.ServiceController$ServiceProxy.invoke(ServiceController.java:966)

        • 1. Re: What's the db schema for jdbc2 persistent?
          jbrosenberg Newbie

          Ok,

          Actually, I see now that it did create 1 new table:

          JMS_TRANSACTIONS
          It has one column: TXID (Number, nullable)...

          But looking at the SQL statemenst in jbossmq-services.xml, it seems it needs to create
          a JMS_MESSAGE table (why isn't it doing this):

          CREATE_MESSAGE_TABLE = CREATE TABLE JMS_MESSAGES ( MESSAGEID INTEGER NOT NULL, \
          DESTINATION VARCHAR(255) NOT NULL, TXID INTEGER, TXOP CHAR(1), \
          MESSAGEBLOB OBJECT, PRIMARY KEY (MESSAGEID, DESTINATION) )

          I'm guessing some of this is not standard Oracle SQL...But I get no errors complaining about that...

          Here's a little bit more of the messages I get when the PersistenceManager starts, starting 3 lines prior to what I provided in my previous post:


          05:13:42,417 INFO [PersistenceManager] Starting
          05:13:43,098 WARN [WrappedConnection] Closing a statement you left open, please do your own housekeeping
          05:13:43,098 WARN [WrappedConnection] Closing a statement you left open, please do your own housekeeping
          05:13:43,108 ERROR [PersistenceManager] Starting failed
          org.jboss.mq.SpyJMSException: Could not resolve uncommited transactions. Message recovery may not be accurate; - nested throwable: (java.sq
          l.SQLException: ORA-00942: table or view does not exist
          )
          at org.jboss.mq.pm.jdbc2.PersistenceManager.resolveAllUncommitedTXs(PersistenceManager.java:276)
          at org.jboss.mq.pm.jdbc2.PersistenceManager.startService(PersistenceManager.java:1299)
          at org.jboss.system.ServiceMBeanSupport.start(ServiceMBeanSupport.java:192)
          at sun.reflect.GeneratedMethodAccessor33.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:549)
          at org.jboss.system.ServiceController$ServiceProxy.invoke(ServiceController.java:966)
          at $Proxy11.start(Unknown Source)
          at org.jboss.system.ServiceController.start(ServiceController.java:392)
          at org.jboss.system.ServiceController.start(ServiceController.java:408)
          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:549)
          at org.jboss.mx.util.MBeanProxyExt.invoke(MBeanProxyExt.java:177)

          .....


          Thoughts?

          Jason

          • 3. Re: What's the db schema for jdbc2 persistent?
            jbrosenberg Newbie

            Adrian,

            Not sure what to do with this, I spent about 30 minutes here pondering....

            Adrian, do you think you could give me more than a one link pointer? Can you offer a couple sentences of explanation....The biggest thing I'd ask is why doesnt' it just work out of the box? It seems there should be a separation between the data source and the jms config, maybe need to have something like a jbosscmp_jdbc.xml config file, like is done for ejb's....

            The file 'oracle-jbossmq-service.xml' is not part of the binary distribution for jboss 3.2.1....Is that an oversight?

            I downloaded the 3.2.1 source distribution, and unpacked it....I tried replacing the standard jbossmq-service.xml with oracle-jbossmq-service.xml, but it seems like a lot of necessary things are missing.....so I got lots of missing deployment errors on startup.....

            Also, there is no mention of the 'oracle-jbossmq-service.xml' in any of the Advanced Docs I've seen....

            In fact very little on the jdbc persistence store in the doc, in terms of what and how it maps to the db....

            I tried having both oracle-jbossmq-service.xml and
            the vanillay jbossmq-service.xml deployed, that didn't work either (duplicate definitions)....

            Jason

            • 4. Re: What's the db schema for jdbc2 persistent?
              jbrosenberg Newbie

              Ok, I got it working....I just took the jdbc
              PersistenceManager part from oracle-jbossmq-service.xml and spliced it into the standard distribution version of jbossmq-service.xml....

              Geez...

              Ok, will I have similar problems when I go to get this up and running on PostGres? Hmmm.....

              Ok,

              Jason