Problem with inserting blob

[Aditya Kumar]

Hi,

I am using Oracle 9i as my DB Server.

I need to read the contents of a binary file (PDF) and store it into a
table within a column of type BLOB. So I am taking the contents of the
file into a FileInputStream object, opening a connection to the DB (and
the connection is getting established), using prepareStatement and then
setting the FileInputStream object using the setBinaryStream method. No
problem till here, but when I execute the (insert) statement with the
executeUpdate (or execute) command, this comes up:


java.sql.SQLException: Io exception: Connection reset
at
oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
.......


I should mention that the data is getting inserted into the table. But, as evident, this isnt a clean execution. I tried to close the connection and the fileInputStream method but this exception is thrown before that. Infact, nothing is executed after the executeUpdate command.


Here is the relevant code, if it may be of help:

   
 
File file = new File("d:\\example.pdf"); 
fs = new FileInputStream(file); 
 

Class.forName("oracle.jdbc.driver.OracleDriver"); 
connection=DriverManager.getConnection("jdbc:oracle:thin:@---","xxx","xxx")�; 
connection.setAutoCommit(false); 
pstmt = connection.prepareStatement("INSERT INTO PDFREADER (ID,PDFFILE) 
VALUES (?,?)" ); 
 

pstmt.setInt(1,123); 
pstmt.setBinaryStream(2,fs,(int)file.length()); 
pstmt.executeUpdate(); 
 

//lines below never get executed... 
System.out.println("Execute called"); 
pstmt.close(); 
fs.close(); 

I will appreciate if someone is able to help me with this. I am stuck
on this since 2 days. I hope to get some help here.


Thank you,

[Scott Selikoff]

I was under the impression (and its been awhile since I've worked with blobs) that inserting a record with a blob was a two stage pass. Step 1 is to create the record without the blob. Step 2 is to query for the blob object that represents the place holder in the record for the blob and perform the update on the object. Its one of the reason there's not way to instantiate a blob object directly.

[Purushoth Thambu]

I tried the same piece of code and it worked for me. I used ojdbc14.jar as JDBC jar. You may need to check the driver you are using. Did you try to insert a blob from SQL*Plus? Just to verify if problem is in DB configuration.

[Aditya Kumar]

[Scott Selikoff] I doubt it would work. But I am to start dealing with the problem now, its a full day ahead, lets see if it works!

[Purushothaman] Yes, I think there is no problem with the code. It **could** turn out to be a driver problem but then again, you mention the ojdbc14.jar file, which is the same one that I am using. Which version of Oracle does this support? 10g? I have the 9i version. Could it be that the 10g driver for JDBC does the trick here? I hope so, I am going to try that.

>>Did you try to insert a blob from SQL*Plus?
"From" or "To"? I am inserting this data to a Orcale(9i) DB table.

[Purushoth Thambu]

I am using Oracle 9i. ojdbc14.jar works on 10g and 9i as well. Is the record actually inserted into the DB and committed?

[Aditya Kumar]

Well, I got it working. The problem was the jdbc driver.

But it is indeed strange, I was using the driver file ojdbc14.jar (the one for version Oracle 9i), and it was throwing exception while inserting Blob. I downloaded the new jar file for Oracle 10g (which is supposed to be backward compatible) and its working with the 9i DB now. No code change whatsoever.

Thank you for all your replies!