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

    jdbc2 and firebird

    klchan Newbie

      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.







        • 1. Re: jdbc2 and firebird
          Adrian Brock Master

          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

          • 2. Re: jdbc2 and firebird
            klchan Newbie

            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!




            • 3. Re: jdbc2 and firebird
              jwcone Newbie

              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.