LOB operations with the IBM Data Server Driver for JDBC and SQLJ

The IBM® Data Server Driver for JDBC and SQLJ supports methods for updating and retrieving data from BLOB, CLOB, and DBCLOB columns in a table, and for calling stored procedures or user-defined functions with BLOB or CLOB parameters.

Among the operations that you can perform on LOB data under the IBM Data Server Driver for JDBC and SQLJ are:

  • Specify a BLOB or column as an argument of the following ResultSet methods to retrieve data from a BLOB or CLOB column:
    For BLOB columns:
    • getBinaryStream
    • getBlob
    • getBytes
    For CLOB columns:
    • getAsciiStream
    • getCharacterStream
    • getClob
    • getString
  • Call the following ResultSet methods to update a BLOB or CLOB column in an updatable ResultSet:
    For BLOB columns:
    • updateBinaryStream
    • updateBlob
    For CLOB columns:
    • updateAsciiStream
    • updateCharacterStream
    • updateClob

    If you specify -1 for the length parameter in any of the previously listed methods, the IBM Data Server Driver for JDBC and SQLJ reads the input data until it is exhausted.

  • Use the following PreparedStatement methods to set the values for parameters that correspond to BLOB or CLOB columns:
    For BLOB columns:
    • setBytes
    • setBlob
    • setBinaryStream
    • setObject, where the Object parameter value is an InputStream.
    For CLOB columns:
    • setString
    • setAsciiStream
    • setClob
    • setCharacterStream
    • setObject, where the Object parameter value is a Reader.

    If you specify -1 for length, the IBM Data Server Driver for JDBC and SQLJ reads the input data until it is exhausted.

  • Retrieve the value of a JDBC CLOB parameter using the CallableStatement.getString method.
Restriction: With IBM Data Server Driver for JDBC and SQLJ type 2 connectivity, you cannot call a stored procedure that has DBCLOB OUT or INOUT parameters.
If you are using the IBM Data Server Driver for JDBC and SQLJ version 4.0 or later, you can perform the following additional operations:
  • Use ResultSet.updateXXX or PreparedStatement.setXXX methods to update a BLOB or CLOB with a length value of up to 2GB for a BLOB or CLOB. For example, these methods are defined for BLOBs:
    ResultSet.updateBlob(int columnIndex, InputStream x, long length)
ResultSet.updateBlob(String columnLabel, InputStream x, long length)
ResultSet.updateBinaryStream(int columnIndex, InputStream x, long length)
ResultSet.updateBinaryStream(String columnLabel, InputStream x, long length)
PreparedStatement.setBlob(int columnIndex, InputStream x, long length)
PreparedStatement.setBlob(String columnLabel, InputStream x, long length)
PreparedStatement.setBinaryStream(int columnIndex, InputStream x, long length)
PreparedStatement.setBinaryStream(String columnLabel, InputStream x, long length)
  • Use ResultSet.updateXXX or PreparedStatement.setXXX methods without the length parameter when you update a BLOB or CLOB, to cause the IBM Data Server Driver for JDBC and SQLJ to read the input data until it is exhausted. For example:
    ResultSet.updateBlob(int columnIndex, InputStream x)
ResultSet.updateBlob(String columnLabel, InputStream x)
ResultSet.updateBinaryStream(int columnIndex, InputStream x)
ResultSet.updateBinaryStream(String columnLabel, InputStream x)
PreparedStatement.setBlob(int columnIndex, InputStream x)
PreparedStatement.setBlob(String columnLabel, InputStream x)
PreparedStatement.setBinaryStream(int columnIndex, InputStream x)
PreparedStatement.setBinaryStream(String columnLabel, InputStream x)
  • Create a Blob or Clob object that contains no data, using the Connection.createBlob or Connection.createClob method.
  • Materialize a Blob or Clob object on the client, when progressive streaming or locators are in use, using the Blob.getBinaryStream or Clob.getCharacterStream method.
  • Free the resources that a Blob or Clob object holds, using the Blob.free or Clob.free method.