storing byte[] CMP field with size > 2000k

[jdauvin]

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

[ricardoarguello]

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

[dhartford]

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

[aloubyansky]

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.

[enpuliu]

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

[rlopez]

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.