# Clob

## 10.1 Clob Overview

A `Clob` object represents the Java programming language mapping of an SQL `CLOB` (Character Large Object). An SQL `CLOB` is a built-in type that stores a Character Large Object as a column value in a row of a database table. Methods in the interfaces `ResultSet`, `CallableStatement`, and `PreparedStatement` allow a programmer to access the SQL3 type `CLOB` in the same way that more basic SQL types are accessed. In other words, an application using the JDBC 2.0 API uses methods such as `getClob` and `setClob` for a `CLOB` value the same way it uses `getInt` and `setInt` for an `INTEGER` value or `getString` and `setString` for a `CHAR` or `VARCHAR` value.

The default is for a JDBC driver to implement the `Clob` interface using the SQL type `LOCATOR(CLOB)` behind the scenes. A `LOCATOR(CLOB)` designates an SQL `CLOB` residing on a database server, and operations on the locator achieve the same results as operations on the `CLOB` itself. This means that a client can operate on a `Clob` instance without ever having to materialize the `CLOB` data on the client machine. The driver uses `LOCATOR(CLOB)` behind the scenes, making its use completely transparent to the JDBC programmer.

The standard behavior for a `Clob` instance is to remain valid until the transaction in which it was created is either committed or rolled back.

The interface `Clob` provides methods for getting the length of an SQL `CLOB` value, for materializing the data in a `CLOB` value on the client, and for searching for a substring or `CLOB` object within a `CLOB` value.

### 10.1.1 Creating a Clob Object

The following code fragment illustrates creating a `Clob` object, where `rs` is a `ResultSet` object:

>     Clob clob = rs.getClob(1);

The variable `clob` can now be used to operate on the `CLOB` value that is stored in the first column of the result set `rs`.

### 10.1.2 Materializing Clob Data

Programmers can invoke methods in the JDBC API on a `Clob` object as if they were operating on the SQL `CLOB` value it designates. However, if they want to operate on a `Clob` object as an object in the Java programming language, they must first materialize the data of the `CLOB` object on the client. The `Clob` interface provides three methods for materializing a `Clob` object as an object in the Java programming language:

- `getAsciiStream` materializes the `CLOB` value as a byte stream containing Ascii bytes

>     Clob notes = rs.getClob("NOTES");
>     java.io.InputStream in = notes.getAsciiStream();
>     byte b = in.read();
>     // in contains the characters in the CLOB value designated by
>     // notes as Ascii bytes; b contains the first character as an Ascii
>     // byte

- `getCharacterStream` materializes the `CLOB` value as a stream of Unicode characters

>     java.io.Reader reader = notes.getCharacterStream();
>     int c = Reader.read();
>     // c contains the first character in the CLOB that notes designates

- `getSubString` materializes all or part of the `CLOB` value as a `String` object

>     String substring = notes.getSubString(10, 5);
>     // substring contains five characters, starting with the tenth
>     // character of the CLOB value that notes designates
>     long len = notes.length();
>     String substring = notes.getSubString(1, len);
>     // substring contains all of the characters in the CLOB object that
>     // notes designates

### 10.1.3 Storing a Clob Object

To store a `Clob` object in the database, it is passed as a parameter to the `PreparedStatement` method `setClob`. For example, the following code fragment stores the `Clob` object `notes` by passing it as the first input parameter to the `PreparedStatement` object `pstmt`:

>     Clob notes = rs.getClob("NOTES");
>     PreparedStatement pstmt = con.prepareStatement(
>     		"UPDATE SALES_STATS SET COMMENTS = ? WHERE SALES > 500000");
>     pstmt.setClob(1, notes);
>     pstmt.executeUpdate();

The `CLOB` value designated by `notes` is now stored in the table `SALES_STATS` in column `COMMENTS` in every row where the value in the column `SALES` is greater than `500000`.

## 10.2 Clob Interface Definition

    package java.sql;
    public interface java.sql.Clob {
    	long length() throws SQLException;
    	InputStream getAsciiStream() throws SQLException;
    	Reader getCharacterStream() throws SQLException;
    	String getSubString(long pos, int length) throws SQLException;
    	long position(String searchstr, long start) throws SQLException;
    	long position(Clob searchstr, long start) throws SQLException;
    }

## 10.3 Clob Methods

#### getAsciiStream

    InputStream getAsciiStream() throws SQLException

> Materializes the `CLOB` value designated by this `Clob` object as a stream of Ascii bytes.

> RETURNS:

> an `InputStream` object with all the data in the `CLOB` value designated by this `Clob` object as Ascii bytes

> EXAMPLE:

> >     java.io.InputStream in = clob.getAsciiStream();
> >     byte b = in.read();
> >     // in has all of the characters in the CLOB value designated by
> >     // clob as Ascii bytes; b designates the first character as an Ascii
> >     // byte

#### getCharacterStream

    Reader getCharacterStream() throws SQLException

> Materializes the `CLOB` value designated by this `Clob` object as a stream of Unicode characters.

> RETURNS:

> a `Reader` object with all the data in the `CLOB` value designated by this `Clob` object as Unicode characters

> EXAMPLE:

> >     Reader read = clob.getCharacterStream();
> >     // read has all the data in the CLOB value designated by clob
> >     // as Unicode characters

#### getSubString

    length getSubString(long pos, int length) throws SQLException

> Returns a copy of the portion of the `CLOB` value represented by this `Clob` object that starts at position `pos` and has up to `length` consecutive characters.

> PARAMETERS:

> pos |  | the position of the first `char` to extract from the `CLOB` value designated by this `Clob` object; the initial position is `1`
> length |  | the number of consecutive characters to be copied

> RETURNS:

> a `String` object containing a copy of up to `length` consecutive characters from the `CLOB` value represented by this `Clob` object, starting with the `char` at position `pos`

> EXAMPLE:

> >     String substr = clob.getSubString(1, 100);
> >     // substr contains the first 100 characters in the CLOB value
> >     // designated by clob (those in positions 1 through 100, inclusive)

#### length

    long length() throws SQLExceptions

> Returns the number of characters in the `CLOB` value designated by this `Clob` object.

> RETURNS:

> the length in characters of the `CLOB` value that this `Clob` object represents

> EXAMPLE:

> >     Clob clob = rs.getClob(3);
> >     long len = clob.length();
> >     // len contains the number of characters in the CLOB value
> >     // designated by clob

#### position

    long position(Clob searchstr, long start) throws SQLException

> Determines the character position at which the `Clob` object `searchstr` begins within the `CLOB` value that this `Clob` object represents. The search begins at position `start`.

> PARAMETERS:

> searchstr |  | the `Clob` object for which to search
> start |  | the position at which to begin searching; the first character is at position `1`

> RETURNS:

> the position at which the `Clob` object `searchstr` begins, which will be `start` or larger if the search, starting at position `start`, is successful; `-1` otherwise

> EXAMPLE:

> >     Clob clob2 = rs.getClob(4);
> >     long beginning = clob.position(clob2, 1024);
> >     // if clob2 is contained in clob starting at position 1024 or later,
> >     // beginning will contain the position at which clob2 begins

#### position

    long position(String searchstr, long start) throws SQLException

> Determines the position at which the `String` `searchstr` begins within the `CLOB` value that this `Clob` object represents. The search begins at position `start`.

> PARAMETERS:

> searchstr |  | the string for which to search
> start |  | the position at which to begin searching; the first character is at position `1`

> RETURNS:

> the position at which the `String` object `searchstr` begins, which will be `start` or larger if the search, starting at position `start`, is successful; `-1` otherwise

> EXAMPLE:

> >     String searchstr= clob.getSubString(5, 100);
> >     long beginning = clob.position(searchstr, 1024);
> >     // if searchstr is contained in clob from position 1024 on, beginning
> >     // will contain the position at which searchstr begins

Copyright © 2000, Sun Microsystems, Inc. All rights reserved.