Using Locators 

Use

Locators are logical pointers to the LOBs in the database.

Using locators is advantageous if you need to copy the contents between table columns of (different) tables, for example. It is also advantageous if you need to determine only the length of the data.

However, you should be aware that there are certain limitations.

Activities

Reading LOBs Using Locators

Locators are independent of the result sets. BLOB and CLOB locators are accessed using instances of java.sql.Blob and java.sql.Clob, which are returned by the getBlob() and getClob() methods of java.sql.ResultSet. A locator is valid as long as the transaction in which it has beencreated lasts. Therefore, it may remain open even if the result set is closed, unless the autocommit mode is enabled.

The contents of the LOB can be read using the getSubString() and getCharacterStream() methods of the Clob object or using the getBinaryStream() and getBytes() of the Blob object. On a particular instance of Blob or Clob, these methods must be called at most once. The following example uses a Blob object to read the introduction of a song. The content of the LOB is read using the Blob.getBytes() method.

Reading BLOB with JDBC using a locator

byte[] getIntro(Connection conn, int songId, int bytes) throws SQLException {    PreparedStatement stmt = conn.prepareStatement("SELECT song FROM songs WHERE id = ?");    try {       ResultSet rs = stmt.executeQuery();       try {          if (rs.next()) {             Blob blob = rs.getBlob(1);             return blob.getBytes(1, bytes);             } else {             return null;          }       } finally {          rs.close();       }    } finally {    stmt.close(); }

Writing LOBs Using Locators

Data can be written to a BLOB or CLOB columns using the setBlob()and setClob()methods of java.sql.PreparedStatement.

Using Blob and Clob with Open SQL has certain limitations:

●      Open SQL supports only the methods of Blob and Clob as specified in JDBC 2.0. This means that you cannot write to or construct a Blob or Clob object. Therefore, you can use a Blob and Clob object in an INSERT or UPDATE statement only if you have read it in the same transaction.

●      A particular instance of a Blob or Clobobject must not be used repeatedly as the argument of a setBlob() or setClob()method. Moreover, if batching is used, a particular instance of Blob or Clob must not be used as a parameter in multiple rows of the same batch. Furthermore, if a locator is used as a parameter in a batch, this parameter must be set with a different locator in every row of the batch and must not be left unchanged.

The following example demonstrates the use of locators for copying the contents of LOB columns.

Writing to a BLOB with JDBC using a locator

private static final int DEFAULT_IMAGE_ID; ... int newImageId = ...;   PreparedStatement select = conn.prepareStatement("SELECT image FROM images WHERE id = ?"); try {    select.setInt(1, DEFAULT_IMAGE_ID);    ResultSet rs = select.executeQuery();    try {       rs.next();       Blob image = rs.getBlob(1);       PreparedStatement insert =          conn.prepareStatement("INSERT INTO images (id, image) VALUES (?, ?)");       try {          insert.setInt(1, newImageId);          insert.setBlob(2, image);          insert.executeUpdate();       } finally {          insert.close();       }    } finally {       rs.close();    } } finally {    select.close(); }