4 Replies Latest reply on Mar 2, 2004 9:52 AM by Javier

    JMS Persistence ORACLE

    mitchellac Newbie

       

      "mitchellac" wrote:
      When a message is getting persisted to my ORACLE 9i database, I get the following error message:

      ...
      Caused by: java.sql.SQLException: Invalid column type
      at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
      at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:179)
      at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:269)
      at oracle.jdbc.driver.OraclePreparedStatement.setObject(OraclePreparedStatement.java:2886)
      at oracle.jdbc.driver.OraclePreparedStatement.setObject(OraclePreparedStatement.java:3015)
      at org.jboss.resource.adapter.jdbc.WrappedPreparedStatement.setObject(WrappedPreparedStatement.java:625)
      at org.jboss.mq.pm.jdbc2.PersistenceManager.setBlob(PersistenceManager.java:778)
      at org.jboss.mq.pm.jdbc2.PersistenceManager.add(PersistenceManager.java:715)
      at org.jboss.mq.pm.jdbc2.PersistenceManager.add(PersistenceManager.java:670)
      ...

      I am using JBOSS 3.2 with the following jms configuration file:

      <?xml version="1.0" encoding="UTF-8"?>

      <!-- $Id: jbossmq-service.xml,v 1.10.2.8 2003/04/03 23:53:27 ejort Exp $ -->

      <server>

      <!-- ==================================================================== -->
      <!-- JBossMQ -->
      <!-- ==================================================================== -->


      <!-- ==================================================================== -->
      <!-- Invocation Layers -->
      <!-- ==================================================================== -->
      <!--
      | InvocationLayers are the different transport methods that can
      | be used to access the server.
      -->

      <mbean code="org.jboss.mq.il.jvm.JVMServerILService"
      name="jboss.mq:service=InvocationLayer,type=JVM">
      <depends optional-attribute-name="Invoker">jboss.mq:service=Invoker</depends>
      <attribute name="ConnectionFactoryJNDIRef">java:/ConnectionFactory</attribute>
      <attribute name="XAConnectionFactoryJNDIRef">java:/XAConnectionFactory</attribute>
      <attribute name="PingPeriod">0</attribute>
      </mbean>

      <mbean code="org.jboss.mq.il.rmi.RMIServerILService"
      name="jboss.mq:service=InvocationLayer,type=RMI">
      <depends optional-attribute-name="Invoker">jboss.mq:service=Invoker</depends>
      <attribute name="ConnectionFactoryJNDIRef">RMIConnectionFactory</attribute>
      <attribute name="XAConnectionFactoryJNDIRef">RMIXAConnectionFactory</attribute>
      <attribute name="PingPeriod">60000</attribute>
      </mbean>

      <mbean code="org.jboss.mq.il.oil.OILServerILService"
      name="jboss.mq:service=InvocationLayer,type=OIL">
      <depends optional-attribute-name="Invoker">jboss.mq:service=Invoker</depends>
      <attribute name="ConnectionFactoryJNDIRef">ConnectionFactory</attribute>
      <attribute name="XAConnectionFactoryJNDIRef">XAConnectionFactory</attribute>
      <attribute name="ServerBindPort">8090</attribute>
      <attribute name="PingPeriod">60000</attribute>
      <attribute name="EnableTcpNoDelay">true</attribute>
      </mbean>

      <mbean code="org.jboss.mq.il.uil.UILServerILService"
      name="jboss.mq:service=InvocationLayer,type=UIL">
      <depends optional-attribute-name="Invoker">jboss.mq:service=Invoker</depends>
      <attribute name="ConnectionFactoryJNDIRef">UILConnectionFactory</attribute>
      <attribute name="XAConnectionFactoryJNDIRef">UILXAConnectionFactory</attribute>
      <attribute name="ServerBindPort">8091</attribute>
      <!-- FIXME: ping disabled because of deadlock problem -->
      <attribute name="PingPeriod">0</attribute>
      <!-- <attribute name="PingPeriod">60000</attribute> -->
      <attribute name="EnableTcpNoDelay">true</attribute>
      </mbean>

      <mbean code="org.jboss.mq.il.oil2.OIL2ServerILService"
      name="jboss.mq:service=InvocationLayer,type=OIL2">
      <depends optional-attribute-name="Invoker">jboss.mq:service=Invoker</depends>
      <attribute name="ConnectionFactoryJNDIRef">OIL2ConnectionFactory</attribute>
      <attribute name="XAConnectionFactoryJNDIRef">OIL2XAConnectionFactory</attribute>
      <attribute name="ServerBindPort">8092</attribute>
      <attribute name="PingPeriod">60000</attribute>
      <attribute name="EnableTcpNoDelay">true</attribute>
      </mbean>

      <!--
      New Unified Invocation Layer as of 3.0.7 - experimental
      Synchronization moved to the message level to improve throughput
      -->
      <mbean code="org.jboss.mq.il.uil2.UILServerILService"
      name="jboss.mq:service=InvocationLayer,type=UIL2">
      <depends optional-attribute-name="Invoker">jboss.mq:service=Invoker</depends>
      <attribute name="ConnectionFactoryJNDIRef">UIL2ConnectionFactory</attribute>
      <attribute name="XAConnectionFactoryJNDIRef">UIL2XAConnectionFactory</attribute>
      <attribute name="ServerBindPort">8093</attribute>
      <attribute name="PingPeriod">60000</attribute>
      <attribute name="EnableTcpNoDelay">true</attribute>
      <!-- Used to disconnect the client if there is no activity -->
      <!-- Ensure this is greater than the ping period -->
      <attribute name="ReadTimeout">70000</attribute>
      <!-- The size of the buffer (in bytes) wrapping the socket -->
      <!-- The buffer is flushed after each request -->
      <attribute name="BufferSize">2048</attribute>
      <!-- Large messages may block the ping/pong -->
      <!-- A pong is simulated after each chunk (in bytes) for both reading and writing -->
      <!-- It must be larger than the buffer size -->
      <attribute name="ChunkSize">1000000</attribute>
      </mbean>

      <!--
      | The HTTP IL is configured in the deploy directory and available by
      | default in both the "default" and "all" server configurations. To customize
      | its attributes please see the jboss-service.xml file included in
      | the META-INF directory of jbossmq-httpil.sar directory. The rationale
      | for not including its configuration here is due to the fact that it
      | includes a required dependant web module which would have required
      | a stand alone WAR file. It was elected, therefore, to put everything
      | in the deploy directory.
      -->

      <!-- ==================================================================== -->
      <!-- JBossMQ Interceptor chain configuration -->
      <!-- ==================================================================== -->
      <!-- To tune performance, you can have the Invoker skip over the TracingInterceptor -->
      <!-- and/or the SecurityManager, but then you loose the ability to trace and/or enforce security. -->
      <mbean code="org.jboss.mq.server.jmx.Invoker" name="jboss.mq:service=Invoker">
      <depends optional-attribute-name="NextInterceptor">jboss.mq:service=TracingInterceptor</depends>
      </mbean>

      <mbean code="org.jboss.mq.server.jmx.InterceptorLoader" name="jboss.mq:service=TracingInterceptor">
      <attribute name="InterceptorClass">org.jboss.mq.server.TracingInterceptor</attribute>
      <depends optional-attribute-name="NextInterceptor">jboss.mq:service=SecurityManager</depends>
      </mbean>

      <mbean code="org.jboss.mq.security.SecurityManager" name="jboss.mq:service=SecurityManager">
      <depends optional-attribute-name="NextInterceptor">jboss.mq:service=DestinationManager</depends>
      </mbean>

      <!--
      | The ClientMonitorInterceptor disconnects clients that have been idle for to long.
      | This interceptor is not enabled by default since the server might disconnect clients
      | when the it is under high load.
      -->
      <!--
      <mbean code="org.jboss.mq.server.jmx.ClientMonitorInterceptor" name="jboss.mq:service=ClientMonitorInterceptor">
      <attribute name="ClientTimeout">80000</attribute>
      <depends optional-attribute-name="NextInterceptor">jboss.mq:service=ClientReconnectInterceptor</depends>
      </mbean>
      -->

      <!--
      | The ClientReconnectInterceptor is used to allow a client to connect to the server even
      | if it's clientID is allready being used by another client. This interceptor will disconnect
      | the previously connected client to allow the new connection to succeed. This is not enabled
      | by default since the JMS spec states that the 2nd client connecting to the server with the same
      | id should get an exception.
      -->
      <!--
      <mbean code="org.jboss.mq.server.jmx.InterceptorLoader" name="jboss.mq:service=ClientReconnectInterceptor">
      <attribute name="InterceptorClass">org.jboss.mq.server.ClientReconnectInterceptor</attribute>
      <depends optional-attribute-name="NextInterceptor">jboss.mq:service=DestinationManager</depends>
      </mbean>
      -->

      <mbean code="org.jboss.mq.server.jmx.DestinationManager" name="jboss.mq:service=DestinationManager">
      <depends optional-attribute-name="PersistenceManager">jboss.mq:service=PersistenceManager</depends>
      <depends optional-attribute-name="StateManager">jboss.mq:service=StateManager</depends>
      </mbean>

      <!--
      | The MessageCache decides where to put JBossMQ message that
      | are sitting around waiting to be consumed by a client.
      |
      | The memory marks are in Megabytes. Once the JVM memory usage hits
      | the high memory mark, the old messages in the cache will start getting
      | stored in the DataDirectory. As memory usage gets closer to the
      | Max memory mark, the amount of message kept in the memory cache aproaches 0.
      |
      | ATTENTION: When the "file" or "rollinglogged" Persistence Manager is used
      | you have to set the "CacheStore" to the CacheStore (the commented out line)
      | and not to the PM itself.
      -->
      <mbean code="org.jboss.mq.server.MessageCache"
      name="jboss.mq:service=MessageCache">
      <attribute name="HighMemoryMark">500</attribute>
      <attribute name="MaxMemoryMark">600</attribute>
      <!--
      <depends optional-attribute-name="CacheStore">jboss.mq:service=CacheStore</depends>
      -->
      <attribute name="CacheStore">jboss.mq:service=PersistenceManager</attribute>
      </mbean>

      <!--
      | The CacheStore decides where to store JBossMQ message that
      | that the MessageCache has decided to move in secondary storage.
      |
      | Now you can specify a absolut path by using an ULR like:
      | file:///c:/temp
      | ATTENTION: the directory MUST exists because it will not be
      | created.
      -->
      <mbean code="org.jboss.mq.pm.file.CacheStore"
      name="jboss.mq:service=CacheStore">
      <attribute name="DataDirectory">tmp/jbossmq</attribute>
      </mbean>

      <!--
      | The StateManager is used to keep JMS persistent state data.
      | For example: what durable subscriptions are active.
      -->
      <mbean code="org.jboss.mq.sm.file.DynamicStateManager"
      name="jboss.mq:service=StateManager">
      <!-- This file is pulled from the configuration URL of the server -->
      <attribute name="StateFile">jbossmq-state.xml</attribute>
      </mbean>

      <!--
      | The PersistenceManager is used to store messages to disk.
      |
      | Now you can specify a absolut path by using an ULR like:
      | file:///c:/temp
      | ATTENTION: the directory MUST exists because it will not be
      | created. Also for the "file" Persistance Manager the directory
      | MUST be empty otherwise the startup fails ("rollinglogged" works
      | fine.
      -->
      <!--
      <mbean code="org.jboss.mq.pm.file.PersistenceManager"
      name="jboss.mq:service=PersistenceManager">
      <attribute name="DataDirectory">data/jbossmq/file</attribute>
      <depends optional-attribute-name="MessageCache">jboss.mq:service=MessageCache</depends>
      </mbean>
      -->

      <!--
      | The jdbc2 PersistenceManager is the new improved JDBC implementation.
      | This implementation allows you to control how messages are stored in
      | the database.
      |
      | Use this PM if you want the reliablity a relational database can offer
      | you. The default configuration is known to work with hsqldb, other databases
      | will requrie teaking of the SqlProperties.
      -->
      <mbean code="org.jboss.mq.pm.jdbc2.PersistenceManager"
      name="jboss.mq:service=PersistenceManager">
      <depends optional-attribute-name="MessageCache">jboss.mq:service=MessageCache</depends>
      <depends optional-attribute-name="ConnectionManager">jboss.jca:service=LocalTxCM,name=OracleDS</depends>
      <!--
      <depends optional-attribute-name="ConnectionManager">jboss.jca:service=XATxCM,name=XAOracleDS</depends>
      -->
      <attribute name="SqlProperties">
      BLOB_TYPE=BINARYSTREAM_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_MESSAGE = DELETE FROM JMS_MESSAGES WHERE MESSAGEID=? AND DESTINATION=?
      CREATE_MESSAGE_TABLE = CREATE TABLE JMS_MESSAGES ( MESSAGEID INTEGER NOT NULL, \
      DESTINATION VARCHAR(255) NOT NULL, TXID INTEGER, TXOP CHAR(1), \
      MESSAGEBLOB BLOB, PRIMARY KEY (MESSAGEID, DESTINATION) )
      CREATE_TX_TABLE = CREATE TABLE JMS_TRANSACTIONS ( TXID INTEGER )
      </attribute>
      </mbean>

      <!-- ==================================================================== -->
      <!-- System Destinations -->
      <!-- ==================================================================== -->

      <!-- Dead Letter Queue -->
      <mbean code="org.jboss.mq.server.jmx.Queue"
      name="jboss.mq.destination:service=Queue,name=DLQ">
      <depends optional-attribute-name="DestinationManager">jboss.mq:service=DestinationManager</depends>
      <depends optional-attribute-name="SecurityManager">jboss.mq:service=SecurityManager</depends>
      </mbean>

      </server>


      I would greatly appreciate anyone's help in this matter.