I have a problem at work which I have to solve in a hurry due to a deadline.
I wrote a perfectly functioning web application using mySQL (v5), iBatis, and
ABATOR that correctly with no modification needed could insert a BLOB field in
a table and retrieve a BLOB from the table.
Today I converted the application to use ORACLE 9i (not 10i) and I have
solved all the conversion problems except for the BLOBs.
Incidentally, the non-BLOB problem I had was that for mySQL I had defined
all my numeric fields as "int(11) and ABATAOR treated "int(11)" in the DAO's
and DOMAIN's it created as type "Integer". But when I converted to Oracle,
ABATOR turned any fields defined as "NUMBER(11) into a type "Long" in the
DAO's and DOMAIN's, which made my user programs have compile errors because of
the changed method signatures. I experimented with ABATOR and found that for
Oracle it creates type "Integer" for "NUMBER(9)" but creates "Long" for
"NUMBER(10)". I did not test when it starts but ABATOR creates a "BigDecimal"
for "NUMBER(15,5). So, I changed the Oracle table definitions of numeric
columns to "NUMBER(9)" and that made ABATOR generate type "Integer" so that all
my source code except for BLOB fields compiled okay.
The BLOB fields however were changed by ABATOR in the DAO's and DOMAIN's
into type "Object". By contrast for mySQL ABATOR generated the type for BLOBs
as byte[].
I googled and found a suggestion to add modify the ABATOR xml to the
following:
<table schema="NEWS2SD" tableName="news_image">
<columnOverride column="IMAGE_BLOB" jdbcType="BLOB"
javaType="java.lang.byte[]" />
</table>
Adding the "columnOverride" to the ABATOR xml made it create a data type of
"byte[]" for BLOB fields, which resulted in my user java programs compiling
perfectly.
However, when I ran the web application using Oracle I was able to access
everything perfectly except the BLOB fields. When I tried in the user program
to insert a BLOB field I got the following error:
SqlMapClient operation; SQL []; --- The error occurred in
frb/bsd/abator/ibatis/NEWS2SD_NEWS_image_SqlMap.xml. --- The error occurred
while applying a parameter map. --- Check the
NEWS2SD_NEWS_IMAGE.abatorgenerated_insert-InlineParameterMap. --- Check the
statement (update failed). --- Cause:
com.ibm.websphere.ce.cm.StaleConnectionException: Io exception: Connection
reset; nested exception is com.ibatis.common.jdbc.exception.NestedSQLException:
--- The error occurred in frb/bsd/abator/ibatis/NEWS2SD_NEWS_image_SqlMap.xml.
--- The error occurred while applying a parameter map. --- Check the
NEWS2SD_NEWS_IMAGE.abatorgenerated_insert-InlineParameterMap. --- Check the
statement (update failed). --- Cause:
com.ibm.websphere.ce.cm.StaleConnectionException: Io exception: Connection
reset
class org.springframework.dao.DataAccessResourceFailureException
I did some research and discovered that iBATIS and ABATOR appararantly can
handle Oracle 10 BLOB fields okay but that for Oracle 9i fields it required
some hand written code to be written into the user program.
I have the book "iBatis in Action" and on page 270 it explains how to
write a "custom type handler and suggests it may be necessary to access BLOB
field of some vendors. Unfortunately the book does not have an example of how
to write one for an Oracle Blob (or any other blob). The book makes it seems
fairly easy to write and register a "custom type handler" and says it's the
most common form of extension to iBatis.
However I don't have a lot of time and I would really, really appreciate
if someone on the list has written this already and can share it with me (and
probably others on the list as well).
Better yet would be someone who has solved this Oracle 9i problem using
iBatis as well as ABATOR and can share the solution. I'm using the version of
iBatis that comes with the new Spring 2.04 distribution. (iBatis 2.3.0.677) and
"AbatorForEclipse1.0.0.zip".
Thanks in advance...
Robert
