Two Laptop Bag*
The moose likes JDBC and the fly likes inserting blob to oracle Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of The Java EE 7 Tutorial Volume 1 or Volume 2 this week in the Java EE forum
or jQuery UI in Action in the JavaScript forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "inserting blob to oracle" Watch "inserting blob to oracle" New topic
Author

inserting blob to oracle

shobhar gupta
Greenhorn

Joined: Mar 30, 2006
Posts: 4
I am trying to insert blobs for a file to oracle, tried everything nothing working, i found out there are two methods:

one is to get a reference to blob in database by getting a result set and then updating the reference to blob by output stream, its not working i don't know why, also this is long way as we have to first insert data, then get reference to it and then again update it by output streams, so 3 calls to database in place of 1.
Another easy way is to use callableStatement and do setBinaryStream like this:

java.io.ByteArrayInputStream byteStream = new java.io.ByteArrayInputStream(byteData);
int numBytes = byteData.length;
cstmt.setBinaryStream(parmIndex, byteStream, numBytes);

but i am not able to send bigger files, it is not allowing something more than 15-20 Kb, even failing at 31 kb i tried, says Data size bigger than max size for this type: , trying to set it into BLob datatype in oracle, i checked oracle specification, it says 4 GB is allowed for BLOB, and setBinaryStream is ok for BLOB as i am able to add smaller files
I got this piece of code from this link:
http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/index.jsp?topic=/com.ibm.db2.doc.java/bjnkmstr26.htm

Can you please help me on this, i am getting really frustrated by this blob thing, help will be really appreciated
stu derby
Ranch Hand

Joined: Dec 15, 2005
Posts: 333
See Oracle's sample code, a complete working program that inserts and retrieves CLOBs and BLOBS, here:
http://www.oracle.com/technology/sample_code/tech/java/sqlj_jdbc/files/advanced/advanced.html
(The LOB example)
shobhar gupta
Greenhorn

Joined: Mar 30, 2006
Posts: 4
I have also tried as in this oracle link, but when i try to update by using an output stream i get a error, ORA-00942: table or view does not exist, can u explain why i am getting this error. Here is my full code.

oracle.sql.BLOB bl = null;
statement.setBlob(4,bl);
statement.setDate(5, messageDate);
statement.setDate(6,messageExpiryDate);
statement.executeUpdate();

CallableStatement statement1 = getStatement();
statement1.registerOutParameter(1, OracleTypes.CURSOR);

statement1.execute();

resultSet = (ResultSet) statement1.getObject(1);

// Get the Blob locator and open output stream for the Blob

if( resultSet.next() )
{

bl = (BLOB)resultSet.getBlob("FILE_ATTACHMENT");
OutputStream byteOutputStream = bl.getBinaryOutputStream();
//
java.io.ByteArrayInputStream byteInputStream =newjava.io.ByteArrayInputStream(fileData);
// here file data is byte[] i got from my file i want to save

byteOutputStream.write(fileData, 0, byteInputStream.available());
// Write to Blob
}


Please help urgently !!!
dnyan ginde
Ranch Hand

Joined: Jan 17, 2006
Posts: 68
hi Shobhar,

try using the schema name along with your table name. For example,

insert into schema_name.table values (?,?);

say your schema name is shobhar and the table name is employee, your query would be:

insert into shobhar.employee values (?,?);

are u converting your blob into byte[]? please try doing this.

try this, if you still face some problems then let us know.
[ April 02, 2006: Message edited by: dnyan ginde ]
pvk Punugoti
Greenhorn

Joined: May 10, 2006
Posts: 2
Example available in:
http://www.terminalxeption.com/index.php?itemid=24


Vishal
 
It is sorta covered in the JavaRanch Style Guide.
 
subject: inserting blob to oracle