*
The moose likes JDBC and the fly likes Problem inserting Blob into Oracle DB Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Problem inserting Blob into Oracle DB" Watch "Problem inserting Blob into Oracle DB" New topic
Author

Problem inserting Blob into Oracle DB

Rick Banster
Greenhorn

Joined: Nov 13, 2006
Posts: 9
The code I have works. It uploads the file to the server. I can read the file. I can insert the name of the file into the database; however, when I try to insert the Blob (text file) the query fails. I get the ORA-01465:invalid hex number error.

It seems the query is having trouble inserting the file contents into the Blob field.

My Oracle 9i database looks like:

create table uploads (filename varchar(500), contents blob);

Here is my code:



Please help.
[ November 15, 2006: Message edited by: Rick Banster ]
Hareesh Ram Chanchali
Ranch Hand

Joined: Jan 31, 2005
Posts: 110
Hi,

Try using oracle CLOB class ...but if it is from file then you must first store empty_clob() through insert statement and then use the syntax of
SELECT clobcolumn where ....FOR UPDATE

with the help of streams you can insert the data into the clob

for further clarifications visit
http://www.stanford.edu/dept/itss/docs/oracle/9i/java.920/a96654/oralob.htm#1043272

Let me know if you have any doubts ..I will help you with code


Hareesh Ram Chanchali
SCJP 5.0, IBM Certified Solution Designer
Hareesh Ram Chanchali
Ranch Hand

Joined: Jan 31, 2005
Posts: 110
Check into the book of JDBC book from oreilly...he has given the code without using the Oracle API

if you want to use oracle then visit

http://www.stanford.edu/dept/itss/docs/oracle/9i/java.920/a96654/oralob.htm#1043272

--------------------
Rick Banster
Greenhorn

Joined: Nov 13, 2006
Posts: 9
Hareesh Ram Chanchali,

Thank you for all of your help. I will read up on the link you sent me and try it out either Monday or Tuesday. It makes more sense now after a quick glance from the link you provided.

Crossing my fingers...

Thanks!

Rick


[ November 19, 2006: Message edited by: Rick Banster ]
Hareesh Ram Chanchali
Ranch Hand

Joined: Jan 31, 2005
Posts: 110
Rick,


Try that. If there is any problem I will give the code for both the methods.
But be careful in writing the program since it needs bit of care in accessing the disk.
Rick Banster
Greenhorn

Joined: Nov 13, 2006
Posts: 9
Hareesh Ram Chanchali,

I tried your suggestions. They really helped jump start me. However, when I tried to use java.sql.blob and getblob() method it caused errors, so I started using((weblogic.jdbc.vendor.oracle.OracleThinBlob)rset).getBLOB (2), which works with my Oracle Setup. (Oracle 9i with Weblogic 9.2)

So my code now looks like:



Before, I got the error: ORA-00022920: row containing the LOB value not locked. So, today I added a lock on the field by adding "FOR UPDATE OF" to my query that selects the blob object. Everything looked like it worked; however, it locked the field and never lets go. So when I try to delete the field I get this error: ORA-00054 resource busy and acquire with NOWAIT specified. Meaning the field in the table is locked.

I'm stuck! It seems every change I make I run into another road block. Please help.
[ November 28, 2006: Message edited by: Rick Banster ]
Hareesh Ram Chanchali
Ranch Hand

Joined: Jan 31, 2005
Posts: 110
Rick,

First I suggest for text file it is better to use CLOB.
Hareesh Ram Chanchali
Ranch Hand

Joined: Jan 31, 2005
Posts: 110
Apply the following code at suitable places

import oracle.jdbc.*;

import oracle.sql.BLOB;


//Set Autocommit off
conn.setAutoCommit(false);




// Select the BLOB

ResultSet rset = stmt.executeQuery ("select contents from uploadtable where fileid = '2' FOR UPDATE ";
rset.next();

BLOB blob=((OracleResultSet)rset).getBLOB(1);

File binaryFile = new File("Give the Filename");

FileInputStream instream = new FileInputStream(binaryFile);

OutputStream outstream = blob.getBinaryOutputStream();

int size = blob.getBufferSize();
byte[] buffer = new byte[size];
int length = -1;

while ((length = instream.read(buffer)) != -1)
{
outstream.write(buffer, 0, length);
outstream.flush();
}

instream.close();
outstream.close();








Let me know if you still find any problems
Rick Banster
Greenhorn

Joined: Nov 13, 2006
Posts: 9
I added your suggestions, but it still locking the field and never lets go. When I try to delete the field I get this error: ORA-00054 resource busy and acquire with NOWAIT specified. Meaning the field in the table is still locked. Any way to break the lock?
Muhammad Saifuddin
Ranch Hand

Joined: Dec 06, 2005
Posts: 1321

Originally posted by Rick Banster:
error: ORA-00054 resource busy and acquire with NOWAIT specified.


I found this solution by google regarding this error :
http://oratip.com/ORA-00054.html
hope this help.


Saifuddin..
[Blog][Linkedin] How To Ask Questions On JavaRanch My OpenSource
Hareesh Ram Chanchali
Ranch Hand

Joined: Jan 31, 2005
Posts: 110
Rick,

Before you insert the empty blob, Please make sure that that autocommit is set to false.

Once you insert the values along with empty blob, you make it commit.

Then use select ... update statement.
Rick Banster
Greenhorn

Joined: Nov 13, 2006
Posts: 9
Hareesh,

I finally got the servlet working after mixing some code examples together. Below is the working code.



NOW for the second piece of the puzzle...I want to have a link on a page that when the user clicks it runs a servlet that grabs the file from the BLOB and allows them to download it. I have the link part and the servlet created. All I need to know is how to read the file from the BLOB and how to allow the user to download the file to their computer.

Here is the code I have so far:



It seems like the servlet is reading the file; however, it is just writing bits (bunch of numbers). Any suggestions how I could have the user download the file and it will be like the file was on the server???

Again, thanks for all of your help!

Rick

[ December 01, 2006: Message edited by: Rick Banster ]
[ December 01, 2006: Message edited by: Rick Banster ]
Hareesh Ram Chanchali
Ranch Hand

Joined: Jan 31, 2005
Posts: 110
Nice to see that the code is working fine.
This can be done but I am not exactly confident about that. But the content type should be MIME.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Problem inserting Blob into Oracle DB