Home » Developer & Programmer » JDeveloper, Java & XML » ClassCastException in OraclePreparedStatement.setBlob
ClassCastException in OraclePreparedStatement.setBlob [message #91108] Tue, 09 April 2002 00:39 Go to next message
Stephen Battey
Messages: 3
Registered: April 2002
Junior Member
Hi
I am using the Oracle JDBC library (version dated around January 2001) and having problems with the setBlob method in oracle.sql.OraclePreparedStatement.

The JDBC interface is defined as:
          void setBlob( int index, java.sql.Blob blob )
but Oracle's implementation of this interface immediately casts the blob argument up to an oracle.sql.BLOB.

This is a clear bug.
The method should work with any java.sql.Blob but a ClassCastException is raised unless the blob is an Oracle BLOB.

Does anyone know anything about this bug?
Is it a known problem, is it down to be fixed, fixed already, etc ...

Many thanks for your help
Steve
Re: ClassCastException in OraclePreparedStatement.setBlob [message #91157 is a reply to message #91108] Sun, 12 May 2002 01:37 Go to previous messageGo to next message
bharat
Messages: 11
Registered: February 2000
Junior Member
I would very strongly recommend that you use
oracle.sql.BLOB only for dealing with the image and
BLOB datatypes in Oracle.
It will make your life a lot more simpler.

Use only
setBLOB in Jdbc. You will not face any issues.

Get back to me at braku@hotmail.com if you have
any questions
Re: ClassCastException in OraclePreparedStatement.setBlob [message #91160 is a reply to message #91108] Mon, 13 May 2002 02:52 Go to previous messageGo to next message
Stephen Battey
Messages: 3
Registered: April 2002
Junior Member
Thanks for your response.
It does appear that the Oracle driver only likes to work with Oracle BLOBs. However, our code has to be compatible with several databases and therefore must not implement anything above JDBC.

To solve the problem I wrote a wrapper class that takes the handling of BLOBs away from our Database Access Objects.
The wrapper class for Oracle creates an Oracle BLOB from the JDBC BLOB by copying across the data from one instance to the other. Not brilliantly efficient, but it does mean our application code is database-independent.
Re: (Solution to) ClassCastException in OraclePreparedStatement.setBlob [message #91192 is a reply to message #91160] Wed, 22 May 2002 02:36 Go to previous message
Stephen Battey
Messages: 3
Registered: April 2002
Junior Member
This seems to be a common problem - I've been asked to provide details about the solution I implemented. I can't post source code because the copyright is owned by our customer, but here is the general design ...

In our database package there is a new interface that looks something like this:
public interface DatabaseSpecificUtils
{
  public int setBlobParameter( PreparedStatement statement, int index, Blob blob ) throws SQLException;
  public void setBlobParameterCleanup( int blobWriteId );
}


Alongside the interface are two classes (PostgresDatabaseSpecificUtils and OracleDatabaseSpecificUtils) which implement this interface. In an appropriate class in your program you need to export a public static constant that is instantiated with the correct util class for the database you are going to use ...
public class MyDatabaseUtils
{
   public static constant DatabaseSpecificUtils DATABASE_SPECIFIC_UTILS;
    
   static
   {
      boolean UseOracle;
      boolean UsePostgres;
    
      /* read config file to find out which database is going to be used */
      ...
    
      /* assign correct class to util constant */
      if (UseOracle)
        DATABASE_SPECIFIC_UTILS = new OracleDatabaseSpecificUtils();
      else if (UsePostgres)
        DATABASE_SPECIFIC_UTILS = new PostgresDatabaseSpecificUtils();
      else if (...)
        DATABASE_SPECIFIC_UTILS = new ...DatabaseSpecificUtils();
      ...
   }
}

The DAOs can now write BLObs into the database using the interface:
   int blobWriteId = -1;
   try
   {
     PreparedStatement blobWriteStmt = dbConnection.prepareStatement( SQL_STMT );
     ...
     blobWriteId = MyDatabaseUtils.DATABASE_SPECIFIC_UTILS.setBlobParameter( blobWriteStmt, 1, myBlob );
     ...
     blobWriteStmt.executeUpdate();
   }
   catch (SQLException sqlEx)
   {
      ...
   }
   finally
   {
     MyDatabaseUtils.DATABASE_SPECIFIC_UTILS.setBlobParameterCleanup( blobWriteId );
   }

You're probably wondering what the extra call to setBlobParameterCleanup is doing and why we need to store a blobWriteId.
Well, for most databases the blobWriteId and setBlobParameterCleanup call are doing nothing. However, due to the problems with Oracle and BLObs there has to be a tidy up routine, called after the prepared statement in the DAO has been executed.

Things to bear in mind for the Oracle implementation of DatabaseSpecificUtils:
  • you will need intermediate statements to stream the BLOb into the database (into a temporary table)
  • to set the BLOb into the 'target' statement, you will need a further statement to select the BLOb out of the temporary table
  • this select statement must only be closed in the clean-up routine to ensure the reference to the data (the BLOb selected out of the temporary table) still exists when the DAO executes the 'target' statement
  • you may also want to delete the BLOb out of the temporary table in the clean-up routine - once it has been inserted into the target table


I recognise the work-around for Oracle is very complex and these pointers don't reveal the complete solution.
I will ask my superiors if they'll consider publishing these classes in some sort of 'VEGA Java Package'.

Best regards,
Steve
Previous Topic: Re: JSP with Oracle
Next Topic: Inserting a String into a Clob datatype
Goto Forum:
  


Current Time: Wed Aug 04 13:30:17 CDT 2021