Hi,
My need is inserting and retrieving binary objects (byte[]) in the most common database on the market.
Oracle database is one of them and, after searching around on the forums, I found that the only way to insert a blob in Oracle database is inserting first an empty Oracle BLOB like this (not tested):
// TABLE : binary(id INTEGER ,myblob BLOB)
pstmt = con.prepareStatement("insert into binary values(2,empty_blob())");
pstmt.executeUpdate();
pstmt = con.prepareStatement("select myblob from binary where id = ?");
pstmt.setInt(1,2);
rs = pstmt.executeQuery();
rs.next();
oracle.jdbc.BLOB blob = ((oracle.jdbc.OracleResultSet)rs).getBLOB(1);
OutputStream os = blob.getBinaryOutputStream();
byte[] bytes=getMyObjectByteArray();
os.write(bytes, 0, bytes.length);
os.close();
And that's NOT a database-independent way to code it because you need to have Oracle driver in your classpath to compile and it will work only for Oracle.
I tried this code:
byte[] bytes=getMyObjectByteArray();
pstmt = con.prepareStatement("insert into binary values(?,?)");
psmt.setInt(1,2);
psmt.setBytes(1,bytes);
pstmt.executeUpdate();
It's working on MySQL (in LONGBLOB column type) and in HSQL (in LONGVARBINARY column type) but it's not working in Oracle (in BLOB column type) using thin driver: the inserted byte[] array is not the same as the retrieved one ...
I also tried using the driver getBlob() method but the thin driver doesn't implement it ...
Is there a way to do this in a database-independent way ?
Thank you for your help !