10 Replies Latest reply on Oct 31, 2002 9:32 AM by sieroka

    Oracle8i CLOB with JBoss

    kevinsit

      Hi,

      I have a few EJBs that use Oracle CLOB fields, however, none of them work for me. The container only returns NULL and the database did not throw any exception during the loading process.

      So, I'm just wondering, what do I need to do if I want to have CLOB fields working under JBoss? Thanks a lot in advance.

      Configuration/DD changes that I tried:
      1) jbosscmp-jdbc.xml

      <cmp-field>
      <field-name>notes</field-name>
      <column-name>NOTES</column-name>
      <jdbc-type>CLOB</jdbc-type>
      <sql-type>CLOB</sql-type>
      </cmp-field>

      2) turn on row locking in jbosscmp-jdbc.xml for EJBs that use CLOB

      Cheers,
      Kev

        • 1. Re: Oracle8i CLOB with JBoss
          scoy

          It should work in 3.0.4. It certainly works with Oracle 9i.

          I was unable to test it with 8i.

          You will probably find that you must use the "oci" drivers in order to work with CLOBs bigger than about 4k. You may also need to use the 9i JDBC drivers. ie Use 9i "client" software against your 8i "server".

          If you want, you can check out Branch_3_0 from cvs and give it a go.

          • 2. Re: Oracle8i CLOB with JBoss
            rupinder

            even i am using Oracle8i with JBoss 3.0.3

            i added following entry in the standardjbosscmp-jdbc.xml

            <java-type>java.lang.Object</java-type>
            <jdbc-type>CLOB</jdbc-type>
            <sql-type>CLOB</sql-type>

            while fetching the data from the table i am getting following exception :
            java.sql.SQLException: Unable to load to deserialize result: java.io.StreamCorruptedException: invalid stream header at org.jboss.ejb.plugins.cmp.jdbc.JDBCUtil.convertByteArrayToObject(JDBCUtil.java:612) at org.jboss.ejb.plugins.cmp.jdbc.JDBCUtil.getResult(JDBCUtil.java:311)
            at org.jboss.ejb.plugins.cmp.jdbc.bridge.JDBCAbstractCMPFieldBridge.loadArgumentResultsJDBCAbstractCMPFieldBridge.java:350)
            at org.jboss.ejb.plugins.cmp.jdbc.bridge.JDBCAbstractCMPFieldBridge.loadInstanceResults(JDBCAbstractCMPFieldBridge.java:304)
            at org.jboss.ejb.plugins.cmp.jdbc.JDBCLoadEntityCommand.execute(JDBCLoadEntityCommand.java:147)
            at org.jboss.ejb.plugins.cmp.jdbc.JDBCLoadEntityCommand.execute(JDBCLoadEntityCommand.java:62)
            at org.jboss.ejb.plugins.cmp.jdbc.JDBCStoreManager.loadEntity(JDBCStoreManager.java:572)
            at org.jboss.ejb.plugins.CMPPersistenceManager.loadEntity(CMPPersistenceManager.java:410)
            at org.jboss.resource.connectionmanager.CachedConnectionInterceptor.loadEntity(CachedConnectionInterceptor.java:353)

            thanks in advance
            rupinder

            • 3. Re: Oracle8i CLOB with JBoss
              scoy

              It's fixed and works in 3.0.4.

              There's a whole new testcase just for CLOBs and BLOBs.

              • 4. Re: Oracle8i CLOB with JBoss
                sieroka

                That that include large clobs with 8i? If so, when is 3.0.4 going to be available?

                The following is from 3.0.3:

                2002-10-30 21:21:10,088 ERROR [org.jboss.ejb.plugins.cmp.jdbc.JDBCCreateEntityCommand.Clob] Could not create entity
                javax.ejb.EJBException: Internal error setting parameters for field clobData; CausedByException is:
                Data size bigger than max size for this type: 8398
                at org.jboss.ejb.plugins.cmp.jdbc.bridge.JDBCAbstractCMPFieldBridge.setArgumentParameters(JDBCAbstractCMPFieldBridge.java:289)
                at org.jboss.ejb.plugins.cmp.jdbc.bridge.JDBCAbstractCMPFieldBridge.setInstanceParameters(JDBCAbstractCMPFieldBridge.java:262)
                at org.jboss.ejb.plugins.cmp.jdbc.JDBCCreateEntityCommand.insertEntity(JDBCCreateEntityCommand.java:192)

                • 5. Re: Oracle8i CLOB with JBoss
                  sieroka

                  Almost forgot. The Java type we are using is String. Also, if the CLOB value in the db is null, it throws a NullPointerException. I'm assuming something like new String( null ) is happening. Again with 3.0.3 and 8i.

                  • 6. Re: Oracle8i CLOB with JBoss
                    scoy

                    CLOBs work fine in 3.0.4, even when they are NULL.

                    Please read what I wrote above about oci drivers again.

                    For more information on this, look at http://otn.oracle.com/docs/products/oracle9i/doc_library/release2/java.920/a96654/toc.htm.

                    I believe that the 3.0.4 release will be real soon now.

                    • 7. Re: Oracle8i CLOB with JBoss
                      sieroka

                      To get the oci drivers, wouldn't I have to install an Oracle client locally? We're trying to support multiple dbs, so that's not a great solution.

                      Is using the new JDBC drivers with 3.0.4 enough?

                      • 8. Re: Oracle8i CLOB with JBoss
                        scoy

                        Just for you guys stuck in Oracle 8 land, I located an 8i instance running in a test env here in my office, and ran the test suite across it.

                        I can confirm that CLOBS and BLOBS of all sizes work fine, BUT:

                        1. I used 9.2 client software
                        2. I used the OCI drivers

                        I repeat my advice to check out the Oracle JDBC URL I provided above.

                        • 9. Re: Oracle8i CLOB with JBoss
                          scoy

                          Yes, you need a local Oracle client installed.

                          Unfortunately, my testing has revealed that the "thin" drivers, even in their current incarnations, do not support large CLOB/BLOB columns.

                          I've been led to believe that you can use LONG VARCHAR instead of CLOBs with the thin drivers, but you can only have one of those per table, and I've not checked it myself.

                          Steve

                          • 10. Re: Oracle8i CLOB with JBoss
                            sieroka

                            That's actually what we're using:
                            LONG for CLOB
                            LONG RAW for BLOB

                            BLOBs seem to be ok, and we did have large CLOBs working this way with char[] in Java using Orion. When we switched to String and JBoss it stopped working.