5 Replies Latest reply on Jun 3, 2004 4:27 PM by Daniel Nanto

    Invalid Column Type

    Daniel Nanto Newbie

      I am trying to reconfigure JMS to use Oracle instead of the Hypersonic database default. To avoid Adrian's wrath, I did quite a bit of research on my own ;)

      Looking on the forums, I found the references to docs/examples/jms, and copied over the XML file for oracle, pointing it to my oracle DS, running against Oracle 9i, JBoss 3.2.3.

      Even though the create_tables flag was set to true, when I first tried to run my sample test, I got exceptions claiming that the table did not exist. So I created the JMS_Messages table using this command:

      CREATE TABLE JMS_MESSAGES ( MESSAGEID integer NOT NULL,
      DESTINATION VARCHAR(255) NOT NULL, TXOP CHAR(1), txid integer,
      MESSAGEBLOB BLOB,
      PRIMARY KEY (MESSAGEID, DESTINATION) );

      Now, when I try and run my tests, I get the following exception:

      [OIL Worker-9] 17:27:52,063 WARN [OILServerILService] Client request resulted in a server exception:
      org.jboss.mq.SpyJMSException: Could not store message: 1 msg=0 hard NOT_STORED PERSISTENT queue=QUEUE.QueueInvokerManage
      r priority=4 hashCode=15590814; - nested throwable: (java.sql.SQLException: Invalid column type)

      Did I not create the JMS table correctly? Are there any problems using the OCI driver for oracle instead of the thin driver?

      Any help would be appreciated. Thanks.

      -Dan

        • 1. Re: Invalid Column Type
          Adrian Brock Master

          Aha, it is working. I can excuse incompetence if it looks like you are making the effort :-)

          Although you broke my other rule. ALWAYS POST THE FULL STACKTRACE.

          For reference:
          http://www.jboss.org/wiki/Wiki.jsp?page=ConfigJBossMQJDBC2Persistence

          OCI works better than thin (thin has a 4k limit on inserting blobs.

          I suspect your problem relates to the original error (why couldn't it create/find the table?).

          Try dropping the JMS_ tables, enable TRACE logging as described in "READ THIS FIRST"
          and post the output from log/server.log for where the PersistenceManager is "starting"
          i.e. doing its recovery, creating the tables and checking the state of the db.

          • 2. Re: Invalid Column Type
            Daniel Nanto Newbie

            Thanks for the quick response. I did what you suggested, and turned on debugging. This lead me to see that the configuration for the persistence manager wasn't being used. It was still trying to create the messageblob as an object. So, I went back to the hsqldb-jdbc2-service.xml file that I had been modifying, and deleted it. And replaced it with the oracle one from the example. This led to the following exception:

            javax.jms.JMSException: Error creating the dlq connection: XAConnectionFactory not bound
            at org.jboss.ejb.plugins.jms.DLQHandler.createService(DLQHandler.java:169)
            at org.jboss.system.ServiceMBeanSupport.create(ServiceMBeanSupport.java:158)
            at org.jboss.ejb.plugins.jms.JMSContainerInvoker.innerCreate(JMSContainerInvoker.java:458)
            at org.jboss.ejb.plugins.jms.JMSContainerInvoker.startService(JMSContainerInvoker.java:674)
            at org.jboss.system.ServiceMBeanSupport.start(ServiceMBeanSupport.java:192)
            at org.jboss.ejb.MessageDrivenContainer.startService(MessageDrivenContainer.java:234)
            at org.jboss.system.ServiceMBeanSupport.start(ServiceMBeanSupport.java:192)
            at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
            at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
            at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)


            But I think the real problem is in the depends element of the oracle-jdbc2-service.xml file. It says this:

            <depends optional-attribute-name="ConnectionManager">jboss.jca:service=LocalTxCM,name=OracleDS

            But I don't have this Connection Manager defined anywhere. There is a similar one defined for hypersonic in the Login-config.xml file. Should I just add one for oracle? I really want it to use a local-tx-datasource that I have already defined. How do I configure it to use my new datasource?

            • 3. Re: Invalid Column Type
              Adrian Brock Master

              Yes, you need to deploy the DataSource (docs/examples/jca).
              I have made this clear on the WIKI

              • 4. Re: Invalid Column Type
                Daniel Nanto Newbie

                Yeah, I read that section in the wiki. I think it was:

                http://www.jboss.org/wiki/Wiki.jsp?page=ConfigJBossMQJDBC2Persistence

                I do have the data source deployed, and it loads it correctly. But I have several Oracle datasources deployed, and I want to tell JMS to use a particular one. How do I configure that?

                • 5. Re: Invalid Column Type
                  Daniel Nanto Newbie

                  Ok, I figured it out. In the

                  <depends optional-attribute-name="ConnectionManager">jboss.jca:service=LocalTxCM,name=OracleDS

                  tag, I needed to change the OracleDS to the JNDI bind name of my new Data Source:

                  <depends optional-attribute-name="ConnectionManager">jboss.jca:service=LocalTxCM,name=MyNewDataSource

                  Once I did this, it appears to create the JMS table just fine, as well as write JMS messages to it.