3 Replies Latest reply on Oct 3, 2007 12:19 PM by jwcone

    jdbc2 and firebird

    klchan
    klchan Mar 17, 2004 3:54 AM

      I tried to use jdbc2 persistent manager (jboss3.2.4) with firebird database and jaybird jdbc driver v1.5 and I kept getting the following error messages:


      12:59:24,000 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: (org.firebirdsql.jdbc.FBSQLException: GDS Exception. 335544569. Dynamic SQL Error
SQL error code = -104
Token unknown - line 1, char 8
JMS_MESSAGES) at org.jboss.mq.pm.jdbc2.PersistenceManager.resolveAllUncommitedTXs(PersistenceManager.java:332)
 at org.jboss.mq.pm.jdbc2.PersistenceManager.startService(PersistenceManager.java:1357)
 at org.jboss.system.ServiceMBeanSupport.start(ServiceMBeanSupport.java:192)
 at sun.reflect.GeneratedMethodAccessor26.invoke(Unknown Source)
 at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
 at java.lang.reflect.Method.invoke(Method.java:324)
 at org.jboss.mx.server.ReflectedDispatcher.dispatch(ReflectedDispatcher.java:72)
 at org.jboss.mx.server.Invocation.dispatch(Invocation.java:45)
 at org.jboss.mx.server.Invocation.invoke(Invocation.java:70)
 at org.jboss.mx.server.AbstractMBeanInvoker.invoke(AbstractMBeanInvoker.java:187)
 at org.jboss.mx.server.MBeanServerImpl.invoke(MBeanServerImpl.java:480)
 at org.jboss.system.ServiceController$ServiceProxy.invoke(ServiceController.java:825)
 at $Proxy16.start(Unknown Source)
 at org.jboss.system.ServiceController.start ServiceController.java:360)
 at org.jboss.system.ServiceController.start(ServiceController.java:382)
 at org.jboss.system.ServiceController.start(ServiceController.java:382)
 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.server.ReflectedDispatcher.dispatch(ReflectedDispatcher.java:72)
 at org.jboss.mx.server.Invocation.dispatch(Invocation.java:45)
 at org.jboss.mx.server.Invocation.invoke(Invocation.java:70)
 at org.jboss.mx.server.AbstractMBeanInvoker.invoke(AbstractMBeanInvoker.java:187)
 at org.jboss.mx.server.MBeanServerImpl.invoke(MBeanServerImpl.java:480)
 at org.jboss.mx.util.MBeanProxyExt.invoke(MBeanProxyExt.java:174)
 at $Proxy4.start(Unknown Source)
 at org.jboss.deployment.SARDeployer.start(SARDeployer.java:250)
 at org.jboss.deployment.MainDeployer.start(MainDeployer.java:833)
 at org.jboss.deployment.MainDeployer.deploy(MainDeployer.java:643)
 at org.jboss.deployment.MainDeployer.deploy(MainDeployer.java:606)
 at sun.reflect.GeneratedMethodAccessor18.invoke(Unknown Source)
 at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
 at java.lang.reflect.Method.invoke(Method.java:324)
 at org.jboss.mx.server.ReflectedDispatcher.dispatch(ReflectedDispatcher.java:72)
 at org.jboss.mx.server.Invocation.dispatch(Invocation.java:45)
 at org.jboss.mx.server.Invocation.invoke(Invocation.java:70)
 at org.jboss.mx.server.AbstractMBeanInvoker.invoke(AbstractMBeanInvoker.java:187)
 at org.jboss.mx.server.MBeanServerImpl.invoke(MBeanServerImpl.java:480)
 at org.jboss.mx.util.MBeanProxyExt.invoke(MBeanProxyExt.java:174)
 at $Proxy7.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(A
bstractDeploymentScanner.java:274)
 at org.jboss.system.ServiceMBeanSupport.start(ServiceMBeanSupport.java:192)
 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.server.ReflectedDispatcher.dispatch(ReflectedDispatcher.java:72)
 at org.jboss.mx.server.Invocation.dispatch(Invocation.java:45)
 at org.jboss.mx.server.Invocation.invoke(Invocation.java:70)
 at org.jboss.mx.server.AbstractMBeanInvoker.invoke(AbstractMBeanInvoker.java:187)
 at org.jboss.mx.server.MBeanServerImpl.invoke(MBeanServerImpl.java:480)
 at org.jboss.system.ServiceController$ServiceProxy.invoke(ServiceController.java:825)
 at $Proxy0.start(Unknown Source)
 at org.jboss.system.ServiceController.start(ServiceController.java:360)
 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.server.ReflectedDispatcher.dispatch(ReflectedDispatcher.java:72)
 at org.jboss.mx.server.Invocation.dispatch(Invocation.java:45)
 at org.jboss.mx.server.Invocation.invoke(Invocation.java:70)
 at org.jboss.mx.server.AbstractMBeanInvoker.invoke(AbstractMBeanInvoker.java:187)
 at org.jboss.mx.server.MBeanServerImpl.invoke(MBeanServerImpl.java:480)
 at org.jboss.mx.util.MBeanProxyExt.invoke(MBeanProxyExt.java:174)
 at $Proxy4.start(Unknown Source)
 at org.jboss.deployment.SARDeployer.start(SARDeployer.java:250)
 at org.jboss.deployment.MainDeployer.start(MainDeployer.java:833)
 at org.jboss.deployment.MainDeployer.deploy(MainDeployer.java:643)
 at org.jboss.deployment.MainDeployer.deploy(MainDeployer.java:606)
 at org.jboss.deployment.MainDeployer.deploy(MainDeployer.java:590)
 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.server.ReflectedDispatcher.dispatch(ReflectedDispatcher.java:72)
 at org.jboss.mx.server.Invocation.dispatch(Invocation.java:45)
 at org.jboss.mx.server.Invocation.invoke(Invocation.java:70)
 at org.jboss.mx.server.AbstractMBeanInvoker.invoke(AbstractMBeanInvoker.java:187)
 at org.jboss.mx.server.MBeanServerImpl.invoke(MBeanServerImpl.java:480)
 at org.jboss.mx.util.MBeanProxyExt.invoke(MBeanProxyExt.java:174)
 at $Proxy5.deploy(Unknown Source)
 at org.jboss.system.server.ServerImpl.doStart(ServerImpl.java:404)
 at org.jboss.system.server.ServerImpl.start(ServerImpl.java:311)
 at org.jboss.Main.boot(Main.java:144)
 at org.jboss.Main$1.run(Main.java:389)
 at java.lang.Thread.run(Thread.java:534)
Caused by: org.firebirdsql.jdbc.FBSQLException: GDS Exception. 335544569. Dynamic SQL Error
SQL error code = -104
Token unknown - line 1, char 8
JMS_MESSAGES
 at org.firebirdsql.jdbc.AbstractPreparedStatement.<init>(AbstractPreparedStatement.java:91)
 at org.firebirdsql.jdbc.FBPreparedStatement.<init>(FBPreparedStatement.java:34)
 at org.firebirdsql.jdbc.AbstractConnection.prepareStatement(AbstractConnection.java:682)
 at org.firebirdsql.jdbc.AbstractConnection.prepareStatement(AbstractConnection.java:232)
 at org.jboss.mq.pm.jdbc2.PersistenceManager.resolveAllUncommitedTXs(PersistenceManager.java:303)
 ... 86 more


      I tried using BLOB_TYPE=BLOB or BINARYSTREAM_BLOB or BYTES_BLOB or OBJECT_BLOB (see below) and none of these work. However, i checked that the tables are indeed created in the firebird database. Here is my 'firebird-jdbc2-service.xml': 

      <mbean code="org.jboss.mq.pm.jdbc2.PersistenceManager" name="jboss.mq:service=PersistenceManager">
<depends optional-attribute-name="ConnectionManager">jboss.jca:service=TxCM,name=FirebirdDS</depends>
<attribute name="SqlProperties">
 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=? 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=?
 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 JMS_MESSAGES FROM JMS_MESSAGES, JMS_TRANSACTIONS WHERE
JMS_MESSAGES.TXID = JMS_TRANSACTIONS.TXID AND JMS_MESSAGES.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(150) 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
</attribute>
</mbean>



      any help is greatly appreciated.

      thanks.







      • 4132 Views
      • Tags:
        • 1. Re: jdbc2 and firebird
          adrian.brock Mar 17, 2004 4:22 AM (in response to klchan)

          DELETE JMS_MESSAGES FROM JMS_MESSAGES, JMS_TRANSACTIONS WHERE
          JMS_MESSAGES.TXID = JMS_TRANSACTIONS.TXID AND JMS_MESSAGES.TXOP=?

          This is not valid SQL.

          P.S. Does firebird support deletes through a join?
          I take it you copied the MySQL version (MySQL does not support subselects).

          Regards,
          Adrian

            • Actions
          • 2. Re: jdbc2 and firebird
            klchan
            klchan Mar 17, 2004 12:50 PM (in response to klchan)

            Thanks Adrain!

            1. I replaced it with:

            DELETE_MARKED_MESSAGES_WITH_TX = DELETE FROM JMS_MESSAGES WHERE TXID IN (SELECT TXID FROM JMS_TRANSACTIONS) AND TXOP=?

            2. also reduced the DESTINATION VARCHAR(250) to VARCHAR(190)

            3. and change BLOB_TYPE=BINARYSTREAM_BLOB.

            It worked now for jdbc2 PM. I also tested firebird with jdbc3 PM and it worked too.

            thanks again!




              • Actions
            • 3. Re: jdbc2 and firebird
              jwcone Oct 3, 2007 12:19 PM (in response to klchan)

              A couple more notes on Firebird:

              If you want SELECT_MAX_TX to work like the Oracle version (i.e. operating on a union derived from JMS_MESSAGES and JMS_TRANSACTIONS) you can define the union as a VIEW, and then have SELECT_MAX_TX select from that VIEW.

              The JMS_MESSAGES_DESTINATION index is likely to have exceptionally poor http://www.firebirdfaq.org/faq167/ in many use cases. Since index selectivity can have a significant impact on Firebird performance, you might consider dropping it.

                • Actions