This week's book giveaways are in the Refactoring and Agile forums.
We're giving away four copies each of Re-engineering Legacy Software and Docker in Action and have the authors on-line!
See this thread and this one for details.
Win a copy of Re-engineering Legacy Software this week in the Refactoring forum
or Docker in Action in the Cloud/Virtualization forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Unable to insert BLOB object in an Oracle table

 
Anup Bansal
Ranch Hand
Posts: 69
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi,

We are facing an issue while inserting a BLOB object in an Oracle table.
The column which accepts the BLOB field is defined as NOT NULL.


Following is a snippet of the code when tryin to insert a hashmap object as BLOB
******************************************************************
Object obj = (Object) outputMap;
bostream = new ByteArrayOutputStream();
serialize = new ObjectOutputStream(bostream);
serialize.writeObject(outputMap);
serialize.flush();
byte[] buffer = bostream.toByteArray();
ByteArrayInputStream baiStream = new ByteArrayInputStream(buffer);
int bLength = buffer.length;

Connection connection = null;
DataSource myDS = null;
InitialContext context = null;
String aJndiName = Constants.JNDI_NAME;

context = new InitialContext();
myDS = (DataSource) context.lookup(aJndiName);
connection = myDS.getConnection();

PreparedStatement pstmt1 = null;
pstmt1 = connection .prepareStatement(SQLConstants.UPDATE_QUERY);
//Set all the columns through appropriate set methods.
pstmt2.setBinaryStream(1, baiStream, abLength);

pstmt2.executeUpdate()
*************************************************

This piece of code woks fine if the BLOB is not very large. However if it is large it gives the following exception:
SQL exception occured while updating the datajava.sql.SQLException: ORA-01407: cannot update ("FCLPRO"."TBLABBAHREQUEST03"."MESSAGE") to NULL.

We modifed the table such that this column would accept null, but in this case the value inserted is null.

Can anyone please explain why the BLOB is being inserted as null?
Will using a CLOB resolve this issue
or is this related to some bug in the database driver types??

Thanks & regards,
Anup
 
ujjwal soni
Ranch Hand
Posts: 403
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi,

I am doing something like this to insert a 20MB gif image.

Inserting a BLOB

// CREATE TABLE t1 (c1 INT PRIMARY KEY NOT NULL, c2 BLOB(5M));


Retrieving a BLOB

// CREATE TABLE t1 (c1 INT PRIMARY KEY NOT NULL, c2 BLOB(5M));



The good thing about this approach is - The function InputStreams encapsulates the logic to chunk the data in pieces.

For the protability part, are you using the JDBC Implementation (classes12.jar for example) corresponding to the Oracle version you are using ? Java just provides the interface in JDBC .. it is completely upto Oracle how they have implemented that in the different versions. But generally speaking, if a method is not depricated, it should work for all versions.



 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic