5 Replies Latest reply on May 26, 2004 12:41 PM by rlopez

    storing byte[] CMP field with size > 2000k

    jdauvin Newbie

      Hi all,

      I have a CMP EntityBean with one CMP field of type byte[]

      /**
      * @ejb.persistent-field
      * @ejb.persistence column-name = "binary_file"
      * @ejb.interface-method view-type = "${ejb.interface.type}"
      *
      * @return the binary content of the file
      */
      abstract public byte[] getBinaryFile();

      /**
      * @ejb.interface-method view-type = "${ejb.interface.type}"
      */
      abstract public void setBinaryFile(byte[] value);



      In Oracle 9i its mapped as a BLOB type.

      I made few tests to store and retrieve this field.

      It works fine as far as the byte[] length is less than about 2000ko.

      I need to store more large size files (about 600 ko).

      I tried to modify the "standardjbosscmp-jdbc.xml" for jboss mapping types. I had this entry for Oracle9i


      <java-type>byte[]</java-type>
      <jdbc-type>BLOB</jdbc-type>
      <sql-type>BLOB</sql-type>



      It is still not working.

      Thanks in advance,

      JC


        • 1. Re: storing byte[] CMP field with size > 2000k
          Ricardo Arguello Newbie

          The Oracle JDBC Thin driver is not spec complaint!

          They expect you to use the OCI driver, which is a PITA because of the Oracle Client installation you must do, and horrible TNS configuration headaches. I've had some problems with hot redeployment when using the OCI driver.... For me the OCI driver is not an option.

          Since their driver is broken, you can't use JBoss' CMP to map a CLOB or a BLOB in an Entity Bean to an Oracle database.

          Reading from a CLOB using CMP works, I haven't tried reading from a BLOB. You need to explicitly declare the jdbc-type as CLOB in the jboss.xml deployment descriptor for the CLOB column, and declare the returning type as java.lang.String.

          Inserting and updating a CLOB does NOT work with CMP, again I haven't tried with a BLOB.

          What I suggest you to do is to code the insertion/update against the Oracle CLOB/BLOB interfaces directly. It works ok even using the Thin driver.

          The only problem is that you need to import oracle.* packages into your project.

          Hint: Do not use the select for update or empty_blob(), use the new CLOB/BLOB Java API.

          Here are some examples of how you could do it:
          http://forums.oracle.com/forums/thread.jsp?forum=99&thread=211460&message=584735

          A complex sample from OTN:
          http://otn.oracle.com/sample_code/tech/java/sqlj_jdbc/files/9i_jdbc/NewLOBAPISample/NewLOBAPISample.java.html

          As you can see, the second is an awful, smelly code, example. Use the first one as a basis instead.

          If you don't want to import oracle.* packages into your project, you could use reflection to call Oracle propietary methods.

          I have coded a workaround for JBoss CMP to work with CLOBs/BLOBs using an Oracle Thin driver (using reflextion). I will commit it after I figure out how to invoke the tempClob.freeTemporary() method AFTER executing the PreparedStatement inside the JBoss CMP engine! Alexey?

          Ricardo Arguello

          • 2. Re: storing byte[] CMP field with size > 2000k
            darren hartford Expert

            couple of options:
            -Compress the byte array using ZIP/JAR utilities (and uncompress on the other end).
            -Switch field type. I do not know about Oracle, but in MySQL switching to LONGBLOB can hold an insane amount.

            -D

            • 3. Re: storing byte[] CMP field with size > 2000k
              Alexey Loubyansky Master

              I didn't know about new api. I'll have a look. I hope it can be used with jdk1.3.1.
              Vendor-specific functionality (in this case setting prepared statement parameters) should be plugable.
              Ricardo, please, let me know your experience with this. This is something we needed for a long time. Thanks.

              • 4. Re: storing byte[] CMP field with size > 2000k
                enpuliu Newbie

                use Oracle 10g jdbc driver instead of 9i.
                It works for blob(byte[]), but not for clob.

                • 5. Re: storing byte[] CMP field with size > 2000k
                  rlopez Newbie

                  Try

                  <java-type>[B</java-type>
                  <jdbc-type>VARBINARY</jdbc-type>
                  <sql-type>BLOB(size)</sql-type>


                  size should be in bytes or in kbytes as 2K or magabytes as 2M etc

                  I use

                  <java-type>[B</java-type>
                  <jdbc-type>VARBINARY</jdbc-type>
                  <sql-type>BLOB(3M)</sql-type>

                  for 3 megabyte BLOB.