When I search the web for inserting BLOBs into Oracle database with jdbc thin driver, most of the webpages suggest a 3-step approach:
- insert
empty_blob()value. - select the row with
for update. - insert the real value.
This works fine for me, here is an example:
Connection oracleConnection = ...
byte[] testArray = ...
PreparedStatement ps = oracleConnection.prepareStatement(
"insert into test (id, blobfield) values(?, empty_blob())");
ps.setInt(1, 100);
ps.executeUpdate();
ps.close();
ps = oracleConnection.prepareStatement(
"select blobfield from test where id = ? for update");
ps.setInt(1, 100);
OracleResultSet rs = (OracleResultSet) ps.executeQuery();
if (rs.next()) {
BLOB blob = (BLOB) rs.getBLOB(1);
OutputStream outputStream = blob.setBinaryStream(0L);
InputStream inputStream = new ByteArrayInputStream(testArray);
byte[] buffer = new byte[blob.getBufferSize()];
int byteread = 0;
while ((byteread = inputStream.read(buffer)) != -1) {
outputStream.write(buffer, 0, byteread);
}
outputStream.close();
inputStream.close();
}
There are some webpages where the authors suggest using a simpler 1-step solution. Previous example with this solution:
Connection oracleConnection = ...
byte[] testArray = ...
PreparedStatement ps = oracleConnection.prepareStatement(
"insert into test(id, blobfield) values(?, ?)");
BLOB blob = BLOB.createTemporary(oracleConnection, false, BLOB.DURATION_SESSION);
OutputStream outputStream = blob.setBinaryStream(0L);
InputStream inputStream = new ByteArrayInputStream(testArray);
byte[] buffer = new byte[blob.getBufferSize()];
int byteread = 0;
while ((byteread = inputStream.read(buffer)) != -1) {
outputStream.write(buffer, 0, byteread);
}
outputStream.close();
inputStream.close();
ps.setInt(1, 100);
ps.setBlob(2, blob);
ps.executeUpdate();
ps.close();
The second code is much more easier, so my question is: What is the point of first (popular) solution? Is there (was there) some kind of constraint for the second solution (Oracle server version number, jdbc driver version, size of the blob,...)? Is the first solution better (speed, memory consumption,...)? Any reasons for not using the simpler second approach?
The exact same question applies for CLOB fields.
