This week's book giveaway is in the OCPJP forum.
We're giving away four copies of OCA/OCP Java SE 7 Programmer I & II Study Guide and have Kathy Sierra & Bert Bates on-line!
See this thread for details.
The moose likes JDBC and the fly likes Unable to insert BLOB object in an Oracle table Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of OCA/OCP Java SE 7 Programmer I & II Study Guide this week in the OCPJP forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Unable to insert BLOB object in an Oracle table" Watch "Unable to insert BLOB object in an Oracle table" New topic
Author

Unable to insert BLOB object in an Oracle table

Anup Bansal
Ranch Hand

Joined: Sep 12, 2006
Posts: 69
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

Joined: Mar 28, 2007
Posts: 403
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.




Cheers!!!
Ujjwal B Soni <baroda, gujarat, india> <+919909981973>
"Helping hands are better than praying lips......"
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Unable to insert BLOB object in an Oracle table