Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

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.

like image 809
alessmar Avatar asked Dec 01 '11 21:12

alessmar


People also ask

How do you update a blob?

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.

Can we update BLOB column in Oracle?

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.


3 Answers

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.

like image 157
a_horse_with_no_name Avatar answered Oct 19 '22 03:10

a_horse_with_no_name


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:

  1. 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();
        }
    }
    
  2. 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);
                    }
                }
            }
        }
    }
    
  3. For CLOBs and NCLOBs, you can additionally use PreparedStatement.setString() and setNString(), respectively.

like image 22
Bass Avatar answered Oct 19 '22 04:10

Bass


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.

like image 1
Roboprog Avatar answered Oct 19 '22 02:10

Roboprog