I'm trying to write and update a pdf document in a blob column but I'm just able to update the blob only writing more data than the previous stored data. If I try to update the blob column with a smaller document data I get only a corrupted pdf.
First the blob column has been initialized using empty_blob() function. I wrote the sample Java class below to test this behaviour. I run it the first time with 'true' as first parameter of the main method so in the first row there's stored a document of about 31kB and in the second row there's a document of 278kB. Then I run it with 'false' as parameter, in this way the two rows should be updated swapping the documents. The result is that I get a correct result only when I write more data than the existing one.
How is it possible to write a method that writes and updates a blob in a reliable way without worring about binary data's size?
import static org.apache.commons.io.IOUtils.copy;
import java.io.FileInputStream;
import java.io.OutputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import oracle.jdbc.OracleDriver;
import oracle.jdbc.OracleResultSet;
import oracle.sql.BLOB;
import org.apache.commons.lang.ArrayUtils;
/**
* Prerequisites:
* 1) a table named 'x' must exists [create table x (i number, j blob);]
* 2) that table should have two columns [insert into x (i, j) values (1, empty_blob()); insert into x (i, j) values (2, empty_blob()); commit;]
* 3) download lsp.pdf from http://www.objectmentor.com/resources/articles/lsp.pdf
* 4) download dotguide.pdf from http://www.graphviz.org/Documentation/dotguide.pdf
*/
public class UpdateBlob {
public static void main(String[] args) throws Exception {
processFiles(new String[]{"lsp.pdf", "dotguide.pdf"}, Boolean.valueOf(args[0]));
}
public static void processFiles(String [] fileNames, boolean forward) throws Exception {
if(!forward){
ArrayUtils.reverse(a);
}
int idx = 1;
for(String fname : fileNames){
insert(idx++, fname);
}
}
private static void insert(int idx, String fname) throws Exception{
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
DriverManager.registerDriver(new OracleDriver());
conn = DriverManager.getConnection("jdbc:oracle:thin:@"+db+":"+port+":"+sid, user, pwd);
ps = conn.prepareStatement("select j from x where i = ? for update");
ps.setLong(1, idx);
rs = ps.executeQuery();
if (rs.next()) {
FileInputStream instream = new FileInputStream(fname);
BLOB blob = ((OracleResultSet)rs).getBLOB(1);
OutputStream outstream = blob.setBinaryStream(1L);
copy(instream, outstream);
instream.close();
outstream.close();
}
rs.close();
ps.close();
conn.close();
} catch (SQLException e) {
e.printStackTrace();
throw new Exception(e);
}
}
}
Oracle version: 11.1.0.7.0 - 64bit
I even tried the standard JDBC API without using Oracle's specific one (like in the example above) without any success.
You cannot update a Blob directly. You must create a new Blob, read the old Blob data into a buffer where you can edit or modify it, then write the modified data to the new Blob.
Updating blobs works the same as for any other datatype: SQL> create table t1 as select 1 id, to_blob (utl_raw. cast_to_raw ('Hello World')) bl from dual / Table created.
It's a lot easier:
PreparedStatement pstmt =
conn.prepareStatement("update blob_table set blob = ? where id = ?");
File blob = new File("/path/to/picture.png");
FileInputStream in = new FileInputStream(blob);
// the cast to int is necessary because with JDBC 4 there is
// also a version of this method with a (int, long)
// but that is not implemented by Oracle
pstmt.setBinaryStream(1, in, (int)blob.length());
pstmt.setInt(2, 42); // set the PK value
pstmt.executeUpdate();
conn.commit();
pstmt.close();
It works the same when using an INSERT statement. No need for empty_blob()
and a second update statement.
In addition to a_horse_with_no_name's answer (which relies on PreparedStatement.setBinaryStream(...) API), there're at least two more options for BLOBs, and 3 more for CLOBs and NCLOBs:
Explicitly create a LOB, write to it, and use PreparedStatement.setBlob(int, Blob)
:
int insertBlobViaSetBlob(final Connection conn, final String tableName, final int id, final byte value[])
throws SQLException, IOException {
try (final PreparedStatement pstmt = conn.prepareStatement(String.format("INSERT INTO %s (ID, VALUE) VALUES (?, ?)", tableName))) {
final Blob blob = conn.createBlob();
try (final OutputStream out = new BufferedOutputStream(blob.setBinaryStream(1L))) {
out.write(value);
}
pstmt.setInt(1, id);
pstmt.setBlob(2, blob);
return pstmt.executeUpdate();
}
}
Update an empty LOB (inserted via DBMS_LOB.EMPTY_BLOB()
or DBMS_LOB.EMPTY_CLOB()
) via SELECT ... FOR UPDATE
. This is Oracle-specific and requires two statements executed instead of one. Additionally, this is what you were trying to accomplish in the first place:
void insertBlobViaSelectForUpdate(final Connection conn, final String tableName, final int id, final byte value[])
throws SQLException, IOException {
try (final PreparedStatement pstmt = conn.prepareStatement(String.format("INSERT INTO %s (ID, VALUE) VALUES (?, EMPTY_BLOB())", tableName))) {
pstmt.setInt(1, id);
pstmt.executeUpdate();
}
try (final PreparedStatement pstmt = conn.prepareStatement(String.format("SELECT VALUE FROM %s WHERE ID = ? FOR UPDATE", tableName))) {
pstmt.setInt(1, id);
try (final ResultSet rset = pstmt.executeQuery()) {
while (rset.next()) {
final Blob blob = rset.getBlob(1);
try (final OutputStream out = new BufferedOutputStream(blob.setBinaryStream(1L))) {
out.write(value);
}
}
}
}
}
For CLOBs and NCLOBs, you can additionally use PreparedStatement.setString()
and setNString()
, respectively.
FWIW, for something that fits in memory, I found I could simply pass in a byte array as the prepared statement parameter, rather than going through the "stream" rigor morale (or worse Oracle specific/suggested things)
Using a Spring "JDBC template" wrapper (org.springframework.jdbc.core.JdbcTemplate) to put the contents of a "large" (or not) string into a BLOB column, the code is something like the following:
jdbc.update( "insert into a_table ( clob_col ) values ( ? )", largeStr.getBytes() );
There is no step 2.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With