What's the db schema for jdbc2 persistent?

[jbrosenberg]

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)

[jbrosenberg]

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

[adrian.brock]

http://cvs.sourceforge.net/cgi-bin/viewcvs.cgi/jboss/jbossmq/src/etc/server/examples/deploy/oracle-jbossmq-service.xml?annotate=1.1.4.4

Regards,
Adrian

[jbrosenberg]

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

[jbrosenberg]

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