Win a copy of Clojure in Action this week in the Clojure forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Problem inserting Blob into Oracle DB

 
Rick Banster
Greenhorn
Posts: 9
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 110
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 110
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 9
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 110
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 9
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 110
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Rick,

First I suggest for text file it is better to use CLOB.
 
Hareesh Ram Chanchali
Ranch Hand
Posts: 110
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 9
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 1324
Android Java Windows
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Hareesh Ram Chanchali
Ranch Hand
Posts: 110
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 9
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 110
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic