ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Robert Glover <robertglove...@yahoo.com>
Subject Re: Oracle BLOB problem with iBatis and ABATOR
Date Wed, 02 May 2007 14:03:50 GMT
   In reply to my own post about a problem inserting a BLOB using iBatis and
ABATOR to Oracle 9 (please note that Oracle 10 works fine), I have pasted below
the solution I am using currently to solve this problem. What you will see is
that I comment out the line of code that would normally to the insert using
ABATOR and replace it with hand written code that inserts the BLOB into Oracle
i manually. This works fine.  The retrieval of the BLOB works perfectly using
the iBatis and ABATOR with no manual code required at all.
  I welcome suggestions how to integrate this code better with ABATOR and
iBATIS.  I researched this problem on the web and found that everytime someone
asked about this problem writing BLOBS to Oracle 9 using iBatis and ABATOR,
someone replied with the solution that only works for Oracle 10 and is useless
for writing to Oracle 9.  Here is the solution that works for Oracle 9 using
manually inserted java code:

       manuallyInsertBlob(newsFile, multipartFile);
      log.debug("doInsertEntry RDG: exiting write_NewsFile after insert");
    * This method was added as an afterthought when it was discovered that
    * required special manual code for a blob insert.  Up to that point in the 
    * development process the database had been mySQL5, which worked fine
    * any manual code.  So, this hastily written method enjoys the luxory of
    * passed into it an instance of NewsFile that has all of it's properties
	private void manuallyInsertBlob(NewsFile newsFile, MultipartFile
		throws Exception
		Connection conn = getDataSource().getConnection();
		log.debug("RDG: enterring manuallyInsertBlob");
		StringBuffer sbInsertPstmt =
			new StringBuffer("INSERT INTO NEWS2SD.NEWS_FILE(")
				.append("ID_NEWS_FILE, ")
				.append("FILE_ORDER, ")
				.append("FK_NEWS_ENTRY, ")  
				.append("FILE_SIZE_ORIGINAL, ")  
				.append("FILE_CONTENT_TYPE, ")  
				.append("FILE_ORIGINAL_NAME, ") 
				.append("FILE_DISPLAY_NAME, ") //7
				.append("FILE_BLOB, ")
				.append("FILE_CREATED_BY, ")
				.append("FILE_CREATED_DATE, ")
				.append("VALUES(  ")
				.append("?, ?, ?, ?, ?, ?, ?, empty_blob(), ?, ?, ?)");
		log.debug("RDG: manuallyInsertBlob point 00a");
		PreparedStatement insertPstmt =
		insertPstmt.setInt(1, newsFile.getIdNewsFile().intValue());
		insertPstmt.setInt(2, newsFile.getFileOrder().intValue());
		insertPstmt.setInt(3, newsFile.getFkNewsEntry().intValue());
		insertPstmt.setInt(4, newsFile.getFileSizeOriginal().intValue());
		insertPstmt.setString(5, newsFile.getFileContentType());
		insertPstmt.setString(6, newsFile.getFileOriginalName());
		insertPstmt.setString(7, newsFile.getFileDisplayName());
		insertPstmt.setString(8, newsFile.getFileCreatedBy());
		insertPstmt.setDate(9, (new java.sql.Date(System.currentTimeMillis())));
		insertPstmt.setInt(10, newsFile.getFileSizeCompressed().intValue());		

		PreparedStatement selectPstmt =
		selectPstmt.setInt(1, newsFile.getIdNewsFile().intValue());
		ResultSet rs = selectPstmt.executeQuery();
		BLOB oracleBlob = null;
		while (rs.next())
			java.sql.Blob sqlBlob = rs.getBlob("FILE_BLOB");
			oracleBlob = (BLOB) sqlBlob;
		log.debug("exiting  manuallyInsertBlob");

--- user-java-digest-help@ibatis.apache.org wrote:

> user-java Digest 30 Apr 2007 02:10:33 -0000 Issue 941
> ----------------------------------------------------------------------
> > Date: Fri, 27 Apr 2007 15:05:44 -0700 (PDT)
> From: Robert Glover <robertgloverjr@yahoo.com>
> Subject: Oracle BLOB problem with iBatis and ABATOR
> To: user-java@ibatis.apache.org
>    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
> 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
> and
> "AbatorForEclipse1.0.0.zip".
>     Thanks in advance...
> Robert

View raw message