How to: Oracle BLOB/CLOBs and JBoss
This question seems to keep cropping up from time to time. Maybe Dain
can add the following information as an addendum or something to the
CMP doco.
This information applies to JBoss 3.0.4 and newer.
Before attempting to store LOBs in Oracle, I've found it's useful to know a few things:
1. Oracle allows you to store four kinds of large data fields in a
table:
a) LONG RAW
b) LONG VARCHAR
c) BLOB
d) CLOB
The LONG data types work with earlier versions of JBoss, but you are
limited to one LONG column per table. It's my understanding that
Oracle may phase out this type in the future.
The LOB data types need current JBoss releases, but you can have as
many LOB columns as you like.
2. There are two primary versions of the JDBC driver:
i) classes12.jar supports 1.2 and 1.3 JREs;
ii) ojdbc14.jar supports the 1.4 JRE and it's newer JDBC spec.
You will also see the old "classes12.zip" around too. I recommend
that you use the jar because amongst other things, Oracle have
started using jar manifests with version information.
[Sigh. I've just checked the Oracle web site and classes12.jar
has disappeared, and the zip file has acquired a manifest, BUT the
version info in the manifests does not seem to match the web page -
I can't win :-( ]
If you download a full JDBC distribution from Oracle, you will get
"debug" versions as well, which can be useful. See the associated
release notes for more information.
The current version of the Oracle JDBC driver is 9.2.0.1. We have
found that this resolves a significant memory leak that is present
in 9.2.0.0.
3. Within the jar, there are two types of Oracle JDBC driver:
a) The "thin" driver
This is the 100% java JDBC implementation. Therefore it is
portable and will run in any version compliant JRE;
b) The "oci" driver
This driver uses JNI to make use of native Oracle client
libraries on the local host. Therefore, it will only work on
platforms that support the Oracle client software.
For more information on this, please see:
<http://otn.oracle.com/docs/products/oracle9i/doc_library/release2/java.920/a96654/toc.htm>
You will need a (free) OTN account to see this.
4. I recommend using the Oracle 9.2 client software (especially the
jar files), even if the database itself is an earler release, such
as 8.1.7. Earlier versions of the JDBC drivers in particular were
problematical when trying to use LOBs.
As many users have discovered, it is not possible to store LOB data
that is larger than 4-5k using the "thin" driver and the standard JDBC
apis. I think that it might be possible using Oracle specific
interfaces, but we can't/won't do that in JBoss (and I've not tried it
either).
Therefore, in order to access any reasonably sized LOBs using Oracle
and JBoss, you MUST use the "oci" drivers. A corrollary of this is that
your JBoss deployment platform must also support the native Oracle
client software.
JBoss/LOB support is not automatic. You need to have a
jbosscmp-jdbc.xml file and specify the mappings explicitly:
<cmp-field>
<field-name>whatever</field-name>
<column-name>whatever</column-name>
<jdbc-type>BLOB</jdbc-type>
<sql-type>BLOB</sql-type>
</cmp-field>
For binary data, JBoss stores byte array data directly to the database.
All other java types are serialised using a java.rmi.MarshalledObject,
therefore your object must be serialisable.
I think that's about all there is to it.
Steve Coy
(returning to mundane life landscaping the back yard while on vacation)
