How to write / update Oracle blob in a reliable way?

Question

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.

Perhaps I'm missing something obvious... But where is the copy method defined? My hunch is that the copy method is not doing what you expect and is only replacing the first N bytes if the new data is smaller than the old data. — Justin Cave
– Justin Cave, Commented Dec 1, 2011 at 21:54
Btw: you should not open a new connection for each call of the function. That is going to be dead slow. — user330315
– user330315, Commented Dec 1, 2011 at 23:36
@JustinCave copy method is defined in the org.apache.commons.io.IOUtils class. There's a static import at the beginning of the code. Static imports makes the less readable, but I think that in some cases it's convenient to use them. — alessmar
– alessmar, Commented Dec 5, 2011 at 6:19
@a_horse_with_no_name The code above is just a really quick POC. I always use a connection pool to manage db connection. — alessmar
– alessmar, Commented Dec 5, 2011 at 6:21

Dave Jarvis · Accepted Answer · 2016-10-02 08:49:11Z

33

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.

edited Oct 2, 2016 at 8:49

Dave Jarvis

31.3k43 gold badges186 silver badges326 bronze badges

answered Dec 1, 2011 at 23:35

user330315

Sign up to request clarification or add additional context in comments.

7 Comments

alessmar Over a year ago

Thanks your solutions works perfectly (even if the correct setBinaryStream's signature is (int parameterIndex, InputStream x, int length)).

hari Over a year ago

What if it is a java object and not a file?

Jeremy Brooks Over a year ago

Hari: use an ObjectInputStream or a ByteArrayInputStream

Zibbobz Over a year ago

@a_horse_with_no_name I'm having the same problem and trying to implement this solution. What should I set the PK value to? Is it dependent on the database?

user330315 Over a year ago

@Zibbobz: you have to know what the PK value is. This could be a sequence value, or a GUID or something else. You are the only one who can answer that question.

|

Community · Accepted Answer · 2017-05-23 12:02:51Z

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.

Roboprog · Accepted Answer · 2016-01-16 01:39:41Z

1

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.

answered Jan 16, 2016 at 1:39

Roboprog

3,1542 gold badges29 silver badges27 bronze badges

Collectives™ on Stack Overflow

How to write / update Oracle blob in a reliable way?

3 Answers 3

7 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

7 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related