JBOSS and Maxdb

[vkasuba]

Hi,

I tried to connect a jboss server to my maxdb. A made my sapdb-ds.xml file. But i dont know how can i create my service file. I tried to use a default mysql-jdbc2-service.xml for this (i rename, and i change inside the datasource), but i got these errors!!

"
2004-06-19 19:39:32,333 INFO [org.jboss.resource.adapter.jdbc.local.LocalManagedConnectionFactory.DefaultDS] Bound connection factory for resource adapter for ConnectionManager 'jboss.jca:service=LocalTxCM,name=DefaultDS to JNDI name 'java:/DefaultDS'
2004-06-19 19:39:32,333 INFO [org.jboss.resource.connectionmanager.TxConnectionManager] Started jboss.jca:service=LocalTxCM,name=DefaultDS
2004-06-19 19:39:33,405 ERROR [org.jboss.mq.pm.jdbc2.PersistenceManager] Starting failed
org.jboss.mq.SpyJMSException: Could not resolve uncommited transactions. Message recovery may not be accurate; - nested throwable: (com.sap.dbtech.jdbc.exceptions.DatabaseException: [-3014] (at 21): Invalid end of SQL statement)
at org.jboss.mq.pm.jdbc2.PersistenceManager.resolveAllUncommitedTXs(PersistenceManager.java:336)
at org.jboss.mq.pm.jdbc2.PersistenceManager.startService(PersistenceManager.java:1356)
at org.jboss.system.ServiceMBeanSupport.start(ServiceMBeanSupport.java:192)
at java.lang.reflect.Method.invoke(Native Method)
at org.jboss.mx.capability.ReflectedMBeanDispatcher.invoke(ReflectedMBeanDispatcher.java:284)
at org.jboss.mx.server.MBeanServerImpl.invoke(MBeanServerImpl.java:546)
at org.jboss.system.ServiceController$ServiceProxy.invoke(ServiceController.java:976)
at $Proxy14.start(Unknown Source)
at org.jboss.system.ServiceController.start(ServiceController.java:394)
at org.jboss.system.ServiceController.start(ServiceController.java:411)
at org.jboss.system.ServiceController.start(ServiceController.java:411)
at java.lang.reflect.Method.invoke(Native Method)
at org.jboss.mx.capability.ReflectedMBeanDispatcher.invoke(ReflectedMBeanDispatcher.java:284)
at org.jboss.mx.server.MBeanServerImpl.invoke(MBeanServerImpl.java:546)
at org.jboss.mx.util.MBeanProxyExt.invoke(MBeanProxyExt.java:177)
at $Proxy4.start(Unknown Source)
at org.jboss.deployment.SARDeployer.start(SARDeployer.java:226)
at org.jboss.deployment.MainDeployer.start(MainDeployer.java:832)
at org.jboss.deployment.MainDeployer.deploy(MainDeployer.java:642)
at org.jboss.deployment.MainDeployer.deploy(MainDeployer.java:605)
at java.lang.reflect.Method.invoke(Native Method)
at org.jboss.mx.capability.ReflectedMBeanDispatcher.invoke(ReflectedMBeanDispatcher.java:284)
at org.jboss.mx.server.MBeanServerImpl.invoke(MBeanServerImpl.java:546)
at org.jboss.mx.util.MBeanProxyExt.invoke(MBeanProxyExt.java:177)
at $Proxy6.deploy(Unknown Source)
at org.jboss.deployment.scanner.URLDeploymentScanner.deploy(URLDeploymentScanner.java:302)
at org.jboss.deployment.scanner.URLDeploymentScanner.scan(URLDeploymentScanner.java:476)
at org.jboss.deployment.scanner.AbstractDeploymentScanner$ScannerThread.doScan(AbstractDeploymentScanner.java:201)
at org.jboss.deployment.scanner.AbstractDeploymentScanner.startService(AbstractDeploymentScanner.java:274)
at org.jboss.system.ServiceMBeanSupport.start(ServiceMBeanSupport.java:192)
at java.lang.reflect.Method.invoke(Native Method)
at org.jboss.mx.capability.ReflectedMBeanDispatcher.invoke(ReflectedMBeanDispatcher.java:284)
at org.jboss.mx.server.MBeanServerImpl.invoke(MBeanServerImpl.java:546)
at org.jboss.system.ServiceController$ServiceProxy.invoke(ServiceController.java:976)
at $Proxy0.start(Unknown Source)
at org.jboss.system.ServiceController.start(ServiceController.java:394)
at java.lang.reflect.Method.invoke(Native Method)
at org.jboss.mx.capability.ReflectedMBeanDispatcher.invoke(ReflectedMBeanDispatcher.java:284)
at org.jboss.mx.server.MBeanServerImpl.invoke(MBeanServerImpl.java:546)
at org.jboss.mx.util.MBeanProxyExt.invoke(MBeanProxyExt.java:177)
at $Proxy4.start(Unknown Source)
at org.jboss.deployment.SARDeployer.start(SARDeployer.java:226)
at org.jboss.deployment.MainDeployer.start(MainDeployer.java:832)
at org.jboss.deployment.MainDeployer.deploy(MainDeployer.java:642)
at org.jboss.deployment.MainDeployer.deploy(MainDeployer.java:605)
at org.jboss.deployment.MainDeployer.deploy(MainDeployer.java:589)
at java.lang.reflect.Method.invoke(Native Method)
at org.jboss.mx.capability.ReflectedMBeanDispatcher.invoke(ReflectedMBeanDispatcher.java:284)
at org.jboss.mx.server.MBeanServerImpl.invoke(MBeanServerImpl.java:546)
at org.jboss.mx.util.MBeanProxyExt.invoke(MBeanProxyExt.java:177)
at $Proxy5.deploy(Unknown Source)
at org.jboss.system.server.ServerImpl.doStart(ServerImpl.java:384)
at org.jboss.system.server.ServerImpl.start(ServerImpl.java:291)
at org.jboss.Main.boot(Main.java:150)
at org.jboss.Main$1.run(Main.java:395)
at java.lang.Thread.run(Thread.java:479)
+ nested throwable:
com.sap.dbtech.jdbc.exceptions.DatabaseException: [-3014] (at 21): Invalid end of SQL statement
at com.sap.dbtech.jdbc.packet.ReplyPacket.createException(ReplyPacket.java:69)
at com.sap.dbtech.jdbc.ConnectionSapDB.throwSQLError(ConnectionSapDB.java:768)
at com.sap.dbtech.jdbc.ConnectionSapDB.execute(ConnectionSapDB.java:434)
at com.sap.dbtech.jdbc.CallableStatementSapDB.sendCommand(CallableStatementSapDB.java:1498)
at com.sap.dbtech.jdbc.StatementSapDB.sendSQL(StatementSapDB.java:725)
at com.sap.dbtech.jdbc.CallableStatementSapDB.doParse(CallableStatementSapDB.java:224)
at com.sap.dbtech.jdbc.CallableStatementSapDB.constructor(CallableStatementSapDB.java:175)
at com.sap.dbtech.jdbc.CallableStatementSapDB.(CallableStatementSapDB.java:79)
at com.sap.dbtech.jdbc.ConnectionSapDB.prepareStatement(ConnectionSapDB.java:660)
at org.jboss.resource.adapter.jdbc.BaseWrapperManagedConnection.prepareStatement(BaseWrapperManagedConnection.java:353)
at org.jboss.resource.adapter.jdbc.WrappedConnection.prepareStatement(WrappedConnection.java:211)
at org.jboss.mq.pm.jdbc2.PersistenceManager.resolveAllUncommitedTXs(PersistenceManager.java:307)
at org.jboss.mq.pm.jdbc2.PersistenceManager.startService(PersistenceManager.java:1356)
at org.jboss.system.ServiceMBeanSupport.start(ServiceMBeanSupport.java:192)
at java.lang.reflect.Method.invoke(Native Method)
at org.jboss.mx.capability.ReflectedMBeanDispatcher.invoke(ReflectedMBeanDispatcher.java:284)
at org.jboss.mx.server.MBeanServerImpl.invoke(MBeanServerImpl.java:546)
at org.jboss.system.ServiceController$ServiceProxy.invoke(ServiceController.java:976)
at $Proxy14.start(Unknown Source)
at org.jboss.system.ServiceController.start(ServiceController.java:394)
at org.jboss.system.ServiceController.start(ServiceController.java:411)
at org.jboss.system.ServiceController.start(ServiceController.java:411)
at java.lang.reflect.Method.invoke(Native Method)
at org.jboss.mx.capability.ReflectedMBeanDispatcher.invoke(ReflectedMBeanDispatcher.java:284)
at org.jboss.mx.server.MBeanServerImpl.invoke(MBeanServerImpl.java:546)
at org.jboss.mx.util.MBeanProxyExt.invoke(MBeanProxyExt.java:177)
at $Proxy4.start(Unknown Source)
at org.jboss.deployment.SARDeployer.start(SARDeployer.java:226)
at org.jboss.deployment.MainDeployer.start(MainDeployer.java:832)
at org.jboss.deployment.MainDeployer.deploy(MainDeployer.java:642)
at org.jboss.deployment.MainDeployer.deploy(MainDeployer.java:605)
at java.lang.reflect.Method.invoke(Native Method)
at org.jboss.mx.capability.ReflectedMBeanDispatcher.invoke(ReflectedMBeanDispatcher.java:284)
at org.jboss.mx.server.MBeanServerImpl.invoke(MBeanServerImpl.java:546)
at org.jboss.mx.util.MBeanProxyExt.invoke(MBeanProxyExt.java:177)
at $Proxy6.deploy(Unknown Source)
at org.jboss.deployment.scanner.URLDeploymentScanner.deploy(URLDeploymentScanner.java:302)
at org.jboss.deployment.scanner.URLDeploymentScanner.scan(URLDeploymentScanner.java:476)
at org.jboss.deployment.scanner.AbstractDeploymentScanner$ScannerThread.doScan(AbstractDeploymentScanner.java:201)
at org.jboss.deployment.scanner.AbstractDeploymentScanner.startService(AbstractDeploymentScanner.java:274)
at org.jboss.system.ServiceMBeanSupport.start(ServiceMBeanSupport.java:192)
at java.lang.reflect.Method.invoke(Native Method)
at org.jboss.mx.capability.ReflectedMBeanDispatcher.invoke(ReflectedMBeanDispatcher.java:284)
at org.jboss.mx.server.MBeanServerImpl.invoke(MBeanServerImpl.java:546)
at org.jboss.system.ServiceController$ServiceProxy.invoke(ServiceController.java:976)
at $Proxy0.start(Unknown Source)
at org.jboss.system.ServiceController.start(ServiceController.java:394)
at java.lang.reflect.Method.invoke(Native Method)
at org.jboss.mx.capability.ReflectedMBeanDispatcher.invoke(ReflectedMBeanDispatcher.java:284)
at org.jboss.mx.server.MBeanServerImpl.invoke(MBeanServerImpl.java:546)
at org.jboss.mx.util.MBeanProxyExt.invoke(MBeanProxyExt.java:177)
at $Proxy4.start(Unknown Source)
at org.jboss.deployment.SARDeployer.start(SARDeployer.java:226)
at org.jboss.deployment.MainDeployer.start(MainDeployer.java:832)
at org.jboss.deployment.MainDeployer.deploy(MainDeployer.java:642)
at org.jboss.deployment.MainDeployer.deploy(MainDeployer.java:605)
at org.jboss.deployment.MainDeployer.deploy(MainDeployer.java:589)
at java.lang.reflect.Method.invoke(Native Method)
at org.jboss.mx.capability.ReflectedMBeanDispatcher.invoke(ReflectedMBeanDispatcher.java:284)
"

It said "invalid end of sql statement"
It means something wrong in my service file?

Thank
Vick

[bl4ck3lk]

hello,

i have nearly the same problem with jboss jboss-4.0.3RC1 and sapdb 7.6 aka MaxDB.
i tried to create my own sapdb-service.xml for jms but :

21:07:03,533 WARN [ServiceController] Problem starting service jboss.mq:service=PersistenceManager
org.jboss.mq.SpyJMSException: Could not resolve uncommited transactions. Message recovery may not be accurate; - nested throwable: (com.sap.dbtech.jdbc.exceptions.DatabaseException: [-3014] (at 21): Invalid end of SQL statement)

maybe someone found the solution during the time and can help ?
thank you !

stefan h.

[rfkat]

This snippet works for me:

BLOB_TYPE=BYTES_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_TEMPORARY_MESSAGES = DELETE FROM JMS_MESSAGES WHERE TXOP='T'
DELETE_MESSAGE = DELETE FROM JMS_MESSAGES WHERE MESSAGEID=? AND DESTINATION=?
CREATE_MESSAGE_TABLE = CREATE TABLE JMS_MESSAGES (MESSAGEID INTEGER NOT NULL, DESTINATION VARCHAR(150) NOT NULL, TXID INTEGER, TXOP CHAR(1), MESSAGEBLOB LONG BYTE, PRIMARY KEY (MESSAGEID, DESTINATION))
CREATE_TX_TABLE = CREATE TABLE JMS_TRANSACTIONS ( TXID INTEGER, PRIMARY KEY (TXID) )
CREATE_IDX_MESSAGE_TXOP_TXID = CREATE INDEX JMS_MESSAGES_TXOP_TXID ON JMS_MESSAGES (TXOP, TXID)
CREATE_IDX_MESSAGE_DESTINATION = CREATE INDEX JMS_MESSAGES_DESTINATION ON JMS_MESSAGES (DESTINATION)
CREATE_TABLES_ON_STARTUP = TRUE

It seems the problem was the original statement for DELETE_MARKED_MESSAGES_WITH_TX

[mstueche]

Hi there,

I had the same problem with my ORACLE file oracle-jdbc2-service.xml

the problem: my editor added some word wrappings.
the solution: add a '\' at the end of a word wrapped statement. see the original file hsqldb-jdbc2-service.xml at the CREATE_MESSAGE_TABLE = CREATE CACHED TABLE JMS_MESSAGES .... statement.

cheers
Michael

[cinzia]

The code posted by rfkat worked also for me (thanks rfkat).

I had a similar error:

WARN [ServiceController] Problem starting service jboss.mq:service
=PersistenceManager
org.jboss.mq.SpyJMSException: Could not resolve uncommited transactions. Messag
e recovery may not be accurate; - nested throwable: (java.sql.SQLException: Ever
y derived table must have its own alias)
.....

with JBoss 4.0.3SP1 and MySQL 5.0 on winxp pro.

Cinzia