Problem when readin blob

[dnyan ginde]

Hello,

I am inserting a blob in my Oracle DB.I first insert an empty_blob() and then update this same empty_blob with a pdf file.This is how i update it:

BufferedInputStream bis = new BufferedInputStream(new ByteArrayInputStream(byteData));
Connection conn = getDataSource().getConnection();
PreparedStatement ps = conn.prepareStatement("UPDATE TPI_DBA.SCHEDULED_REPORTS SET SHR_REPORT = ? WHERE SHR_REPORT_TYPE='MT202' ");
ps.setBinaryStream(1, bis, byteData.length);
ps.executeUpdate();
ps.close();
conn.commit();

I am then trying to read this blob with the following code:

InputStream stream = null;
Connection conn = getDataSource().getConnection();
Statement stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
ResultSet result    = stmt.executeQuery("Select SHR_REPORT from TPI_DBA.SCHEDULED_REPORTS where SHR_REPORT_TYPE = 'MT202'");
if(result.next())
{
BLOB blob = (BLOB) ((OracleResultSet)result).getBLOB("SHR_REPORT");
byteArray = blob.getBytes();               
}

the problem is that when i am reading the blob, the blob object is coming as null and hence i am getting a null pointer exception. Can anybody tell me where i am going wrong?

[Jeff Albertson]

have you tried the getBlob method of java.sql.ResultSet, or tried avoiding java.sql.Blob and using getBinaryStream or getBytes?

[dnyan ginde]

Hello,

Yes i have tried using getBinaryStream() and getBytes() methods but the InputStream and the byte[] is coming as null. When i am inserting the blob into the DB i am getting no exception and that makes me feel asthough the blob is getting inserted properly. But then again when i am retrieving it why is it coming as null. Any suggestions?

[dnyan ginde]

Guys, after struggling with the Blob for quite a long time ive finally inserted and retrieved the blob. Just for your information Oracle just accepts Blob/Clob instances created via its own proprietary BLOB/CLOB API, and additionally doesn't accept large streams for PreparedStatement's corresponding setter methods. So anybody out there facing problems with blob or clob please use Oracle API's if your database is Oracle.