This week's giveaway is in the Android forum.
We're giving away four copies of Android Security Essentials Live Lessons and have Godfrey Nolan on-line!
See this thread for details.
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 Android Security Essentials Live Lessons this week in the Android 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
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: inserting blob to oracle
 
Similar Threads
Stored procedure question
get the last ID in the auto incremented field
how to handle when procedure returns more than one resultset
inserting BLOBs into MySQL database
How to select a range of rows