Let’s Review How to Insert Clob or Blob via JDBC

LOBs are a PITA in all databases, as well as in JDBC. Handling them correctly takes a couple of lines of code, and you can be sure that you’ll get it wrong eventually. Because you have to think of a couple of things:

  • Foremost, LOBs are heavy resources that need special lifecycle management. Once you’ve allocated a LOB, you better “free” it as well to decrease the pressure on your GC. This article shows more about why you need to free lobs
  • The time when you allocate and free a lob is crucial. It might have a longer life span than any of your ResultSet, PreparedStatement, or Connection / transaction. Each database manages such life spans individually, and you might have to read up the specifications in edge cases
  • While you may use String instead of Clob, or byte[] instead of Blob for small to medium size LOBs, this may not always be the case, and may even lead to some nasty errors, like Oracle’s dreaded ORA-01461: can bind a LONG value only for insert into a LONG column

So, if you’re working on a low level using JDBC (instead of abstracting JDBC via Hibernate or jOOQ), you better write a small utility that takes care of proper LOB handling.

We’ve recently re-discovered our own utility that we’re using for jOOQ integration testing, at least in some databases, and thought this might be very useful to a couple of our readers who operate directly with JDBC. Consider the following class:

public class LOB implements AutoCloseable {

    private final Connection connection;
    private final List<Blob> blobs;
    private final List<Clob> clobs;

    public LOB(Connection connection) {
        this.connection = connection;
        this.blobs = new ArrayList<>();
        this.clobs = new ArrayList<>();
    }

    public final Blob blob(byte[] bytes) 
    throws SQLException {
        Blob blob;

        // You may write more robust dialect 
        // detection here
        if (connection.getMetaData()
                      .getDatabaseProductName()
                      .toLowerCase()
                      .contains("oracle")) {
            blob = BLOB.createTemporary(connection, 
                       false, BLOB.DURATION_SESSION);
        }
        else {
            blob = connection.createBlob();
        }

        blob.setBytes(1, bytes);
        blobs.add(blob);
        return blob;
    }

    public final Clob clob(String string) 
    throws SQLException {
        Clob clob;

        if (connection.getMetaData()
                      .getDatabaseProductName()
                      .toLowerCase()
                      .contains("oracle")) {
            clob = CLOB.createTemporary(connection, 
                       false, CLOB.DURATION_SESSION);
        }
        else {
            clob = connection.createClob();
        }

        clob.setString(1, string);
        clobs.add(clob);
        return clob;
    }


    @Override
    public final void close() throws Exception {
        blobs.forEach(JDBCUtils::safeFree);
        clobs.forEach(JDBCUtils::safeFree);
    }
}

This simple class has some nice treats:

  • It’s AutoCloseable, so you can free your lobs with the try-with-resources statement
  • It abstracts over the creation of LOBs across SQL dialects. No need to remember the Oracle way

To use this class, simply write something like the following:

try (
    LOB lob = new LOB(connection);
    PreparedStatement stmt = connection.prepareStatement(
        "insert into lobs (id, lob) values (?, ?)")
) {
    stmt.setInt(1, 1);
    stmt.setClob(2, lob.clob("abc"));
    stmt.executeUpdate();
}

That’s it! No need to keep references to the lob, safely freeing it if it’s not null, correctly recovering from exceptions, etc. Just put the LOB container in the try-with-resources statement, along with the PreparedStatement and done.

If you’re interested in why you have to call Clob.free() or Blob.free() in the first place, read our article about it. It’ll spare you one or two OutOfMemoryErrors

Binary data in SQL, more trivia

Yesterday, I blogged about inlining boolean literals in SQL. This goes on with other data types. Let’s look at BLOBs and BINARY data in general. This is also defined in SQL standards, not in SQL 1992, though:

<binary string literal> ::=
  X <quote> [ <space>... ] 
  [ { <hexit> [ <space>... ] <hexit> [ <space>... ] }... ] <quote>

 
But is that supported as such in all databases? Of course, as usual, the answer goes “NO”. Here’s a little summary to see how binary literals are handled in which database:

SQL Server, Sybase ASE, Sybase SQL Anywhere

-- T-SQL databases treat binary literals as other languages
-- treat hexadecimal numbers
INSERT INTO lob_table VALUES (0x01FF);

DB2

-- DB2 follows the standard, but you may need to use
-- a blob constructor. This is not needed for VARCHAR
-- FOR BIT DATA types
INSERT INTO lob_table VALUES (blob(X'01FF'));

Derby, H2, HSQLDB, Ingres, MySQL, SQLite

-- Most databases follow the standard, especially
-- the Java databases
INSERT INTO lob_table VALUES (X'01FF');

Oracle

-- Oracle Omit's the standard's "X"
INSERT INTO lob_table VALUES (hextoraw('01FF'));

Postgres

-- For some odd reason Postgres, is really off this time
-- using octal representation of bytes in literals.
-- There is also hex encoding but I didn't get that to work
-- The explicit cast is important, though
INSERT INTO lob_table VALUES (E'\\001\\377'::bytea);