Oracle8i JDBC Developer's Guide and Reference Release 3 (8.1.7) Part Number A83724-01 |
Library | Service | Contents | Index

## Java Streams in JDBC

This section covers the following topics:

- Streaming LONG or LONG RAW Columns

- Streaming CHAR, VARCHAR, or RAW Columns

- Data Streaming and Multiple Columns

- Streaming and Row Prefetching

- Closing a Stream

- Streaming LOBs and External Files

This section describes how the Oracle JDBC drivers handle Java streams for several datatypes. Data streams allow you to read `LONG` column data of up to 2 gigabytes. Methods associated with streams let you read the data incrementally.

Oracle JDBC drivers support the manipulation of data streams in either direction between server and client. The drivers support all stream conversions: binary, ASCII, and Unicode. Following is a brief description of each type of stream:

- binary stream--Used for `RAW` bytes of data. This corresponds to the `getBinaryStream()` method.

- ASCII stream--Used for ASCII bytes in ISO-Latin-1 encoding. This corresponds to the `getAsciiStream()` method.

- Unicode stream--Used for Unicode bytes with the `UCS-2` encoding. This corresponds to the `getUnicodeStream()` method.

The methods `getBinaryStream()`, `getAsciiStream()`, and `getUnicodeStream()` return the bytes of data in an `InputStream` object. These methods are described in greater detail in Chapter 7, "Working with LOBs and BFILEs".

For a complete sample application showing how to read and write stream data, see "Streams--StreamExample.java".

### Streaming LONG or LONG RAW Columns

When a query selects one or more `LONG` or `LONG` `RAW` columns, the JDBC driver transfers these columns to the client in streaming mode. After a call to `executeQuery()` or `next()`, the data of the `LONG` column is waiting to be read.

To access the data in a `LONG` column, you can get the column as a Java `InputStream` and use the `read()` method of the `InputStream` object. As an alternative, you can get the data as a string or byte array, in which case the driver will do the streaming for you.

You can get `LONG` and `LONG` `RAW` data with any of the three stream types. The driver performs NLS conversions for you, depending on the character set of your database and the driver. For more information about NLS, see "JDBC and NLS".

#### LONG RAW Data Conversions

A call to `getBinaryStream()` returns `RAW` data "as-is". A call to `getAsciiStream()` converts the `RAW` data to hexadecimal and returns the ASCII representation. A call to `getUnicodeStream()` converts the `RAW` data to hexadecimal and returns the Unicode bytes.

For example, if your `LONG` `RAW` column contains the bytes 20 21 22, you receive the following bytes:

LONG RAW | BinaryStream | ASCIIStream | UnicodeStream
--- | --- | --- | ---
20 21 22 | 20 21 22 | 49 52 49 53 49 54 which is also '1' '4' '1' '5' '1' '6' | 0049 0052 0049 0053 0049 0054 which is also: '1' '4' '1' '5' '1' '6'

For example, the `LONG` `RAW` value 20 is represented in hexadecimal as 14 or "1" "4". In ASCII, 1 is represented by "49" and "4" is represented by "52". In Unicode, a padding of zeros is used to separate individual values. So, the hexadecimal value 14 is represented as 0 "1" 0 "4". The Unicode representation is 0 "49" 0 "52".

#### LONG Data Conversions

When you get `LONG` data with `getAsciiStream()`, the drivers assume that the underlying data in the database uses an `US7ASCII` or `WE8ISO8859P1` character set. If the assumption is true, the drivers return bytes corresponding to ASCII characters. If the database is not using an `US7ASCII` or `WE8ISO8859P1` character set, a call to `getAsciiStream()` returns meaningless information.

When you get `LONG` data with `getUnicodeStream()`, you get a stream of Unicode characters in the `UCS-2` encoding. This applies to all underlying database character sets that Oracle supports.

When you get `LONG` data with `getBinaryStream()`, there are two possible cases:

- If the driver is JDBC OCI and the client character set is not `US7ASCII` or `WE8ISO8859P1`, then a call to `getBinaryStream()` returns `UTF-8`. If the client character set is `US7ASCII` or `WE8ISO8859P1`, then the call returns a `US7ASCII` stream of bytes.

- If the driver is JDBC Thin and the database character set is not `US7ASCII` or `WE8ISO8859P1`, then a call to `getBinaryStream()` returns `UTF-8`. If the server-side character set is `US7ASCII` or `WE8ISO8859P1`, then the call returns a `US7ASCII` stream of bytes.

For more information on how the drivers return data based on character set, see "JDBC and NLS".

Note: Receiving `LONG` or `LONG` `RAW` columns as a stream (the default case) requires you to pay special attention to the order in which you receive data from the database. For more information, see "Data Streaming and Multiple Columns".

Table 3-3 summarizes `LONG` and `LONG` `RAW` data conversions for each stream type.

##### Table 3-3 LONG and LONG RAW Data Conversions

Datatype | BinaryStream | AsciiStream | UnicodeStream
--- | --- | --- | ---
LONG | bytes representing characters in Unicode `UTF-8`. The bytes can represent characters in `US7ASCII` or `WE8ISO8859P1` if: the value of `NLS_LANG` on the client is `US7ASCII` or `WE8ISO8859P1`. or: the database character set is `US7ASCII` or `WE8ISO8859P1`. | bytes representing characters in ISO-Latin-1 (`WE8ISO8859P1`) encoding | bytes representing characters in Unicode `UCS-2` encoding
LONG RAW | as-is | ASCII representation of hexadecimal bytes | Unicode representation of hexadecimal bytes

#### Streaming Example for LONG RAW Data

One of the features of a `getXXXStream()` method is that it allows you to fetch data incrementally. In contrast, `getBytes()` fetches all the data in one call. This section contains two examples of getting a stream of binary data. The first version uses the `getBinaryStream()` method to obtain `LONG` `RAW` data; the second version uses the `getBytes()` method.

##### Getting a LONG RAW Data Column with getBinaryStream()

This Java example writes the contents of a `LONG` `RAW` column to a file on the local file system. In this case, the driver fetches the data incrementally.

The following code creates the table that stores a column of `LONG` `RAW` data associated with the name LESLIE:

    -- SQL code:
    create table streamexample (NAME varchar2 (256), GIFDATA long raw);
    insert into streamexample values ('LESLIE', '00010203040506070809');

The following Java code snippet writes the data from the LESLIE `LONG` `RAW` column into a file called `leslie.gif:`

    ResultSet rset = stmt.executeQuery
                     ("select GIFDATA from streamexample where NAME='LESLIE'");

    // get first row
    if (rset.next())
    {
        // Get the GIF data as a stream from Oracle to the client
        InputStream gif_data = rset.getBinaryStream (1);
       try
       {
          FileOutputStream file = null;
          file = new FileOutputStream ("leslie.gif");
          int chunk;
          while ((chunk = gif_data.read()) != -1)
             file.write(chunk);
       }
       catch (Exception e)
       {
          String err = e.toString();
          System.out.println(err);
       }
       finally
       {
          if file != null()
             file.close();
       }
    }

In this example the contents of the `GIFDATA` column are transferred incrementally in `chunk`-sized pieces between the database and the client. The `InputStream` object returned by the call to `getBinaryStream()` reads the data directly from the database connection.

##### Getting a LONG RAW Data Column with getBytes()

This version of the example gets the content of the `GIFDATA` column with `getBytes()` instead of `getBinaryStream()`. In this case, the driver fetches all the data in one call and stores it in a byte array. The previous code snippet can be rewritten as:

    ResultSet rset2 = stmt.executeQuery
                      ("select GIFDATA from streamexample where NAME='LESLIE'");

    // get first row
    if (rset2.next())
    {
       // Get the GIF data as a stream from Oracle to the client
       byte[] bytes = rset2.getBytes(1);
       try
       {
          FileOutputStream file = null;
          file = new FileOutputStream ("leslie2.gif");
          file.write(bytes);
       }
       catch (Exception e)
       {
          String err = e.toString();
          System.out.println(err);
       }
       finally
       {
          if file != null()
             file.close();
       }
    }

Because a `LONG` `RAW` column can contain up to 2 gigabytes of data, the `getBytes()` example will probably use much more memory than the `getBinaryStream()` example. Use streams if you do not know the maximum size of the data in your `LONG` or `LONG` `RAW` columns.

#### Avoiding Streaming for LONG or LONG RAW

The JDBC driver automatically streams any `LONG` and `LONG` `RAW` columns. However, there may be situations where you want to avoid data streaming. For example, if you have a very small `LONG` column, you might want to avoid returning the data incrementally and instead, return the data in one call.

To avoid streaming, use the `defineColumnType()` method to redefine the type of the `LONG` column. For example, if you redefine the `LONG` or `LONG RAW` column as type `VARCHAR` or `VARBINARY`, then the driver will not automatically stream the data.

If you redefine column types with `defineColumnType()`, you must declare the types of all columns in the query. If you do not, `executeQuery()` will fail. In addition, you must cast the `Statement` object to an `oracle.jdbc.driver.OracleStatement` object.

As an added benefit, using `defineColumnType()` saves the driver two round trips to the database when executing the query. Without `defineColumnType()`, the JDBC driver has to request the datatypes of the column types.

Using the example from the previous section, the `Statement` object `stmt` is cast to the `OracleStatement` and the column containing `LONG` `RAW` data is redefined to be of the type `VARBINARAY`. The data is not streamed--instead, it is returned in a byte array.

    //cast the statement stmt to an OracleStatement
    oracle.jdbc.driver.OracleStatement ostmt =
       (oracle.jdbc.driver.OracleStatement)stmt;

    //redefine the LONG column at index position 1 to VARBINARY
    ostmt.defineColumnType(1, Types.VARBINARY);

    // Do a query to get the images named 'LESLIE'
    ResultSet rset = ostmt.executeQuery
             ("select GIFDATA from streamexample where NAME='LESLIE'");

    // The data is not streamed here
    rset.next();
    byte [] bytes = rset.getBytes(1);

### Streaming CHAR, VARCHAR, or RAW Columns

If you use the `defineColumnType()` Oracle extension to redefine a `CHAR`, `VARCHAR`, or `RAW` column as a `LONGVARCHAR` or `LONGVARBINARY`, then you can get the column as a stream. The program will behave as if the column were actually of type `LONG` or `LONG RAW`. Note that there is not much point to this, because these columns are usually short.

If you try to get a `CHAR`, `VARCHAR`, or `RAW` column as a data stream without redefining the column type, the JDBC driver will return a Java `InputStream`, but no real streaming occurs. In the case of these datatypes, the JDBC driver fully fetches the data into an in-memory buffer during a call to the `executeQuery()` method or `next()` method. The `getXXXStream()` entry points return a stream that reads data from this buffer.

### Data Streaming and Multiple Columns

If your query selects multiple columns and one of the columns contains a data stream, then the contents of the columns following the stream column are not available until the stream has been read, and the stream column is no longer available once any following column is read. Any attempt to read a column beyond a streaming column closes the streaming column. See "Streaming Data Precautions" for more information.

#### Streaming Example with Multiple Columns

Consider the following query:

    ResultSet rset = stmt.executeQuery
            ("select DATECOL, LONGCOL, NUMBERCOL from TABLE");
    while rset.next()
    {
       //get the date data
       java.sql.Date date = rset.getDate(1);

       // get the streaming data
       InputStream is = rset.getAsciiStream(2);

       // Open a file to store the gif data
       FileOutputStream file = new FileOutputStream ("ascii.dat");

       // Loop, reading from the ascii stream and
       // write to the file
       int chunk;
       while ((chunk = is.read ()) != -1)
          file.write(chunk);
       // Close the file
       file.close();

       //get the number column data
       int n = rset.getInt(3);
    }

The incoming data for each row has the following shape:

    <a date><the characters of the long column><a number>

As you process each row of the iterator, you must complete any processing of the stream column before reading the number column.

An exception to this behavior is LOB data, which is also transferred between server and client as a Java stream. For more information on how the driver treats LOB data, see "Streaming LOBs and External Files".

#### Bypassing Streaming Data Columns

There might be situations where you want to avoid reading a column that contains streaming data. If you do not want to read the data for the streaming column, then call the `close()` method of the stream object. This method discards the stream data and allows the driver to continue reading data for all the non-streaming columns that follow the stream. Even though you are intentionally discarding the stream, it is good programming practice to call the columns in SELECT-list order.

In the following example, the stream data in the `LONG` column is discarded and the data from only the `DATE` and `NUMBER` column is recovered:

    ResultSet rset = stmt.executeQuery
            ("select DATECOL, LONGCOL, NUMBERCOL from TABLE");

    while rset.next()
    {
       //get the date
       java.sql.Date date = rset.getDate(1);

       // access the stream data and discard it with close()
       InputStream is = rset.getAsciiStream(2);
       is.close();

       // get the number column data
       int n = rset.getInt(3);
    }

### Streaming LOBs and External Files

The term large object (LOB) refers to a data item that is too large to be stored directly in a database table. Instead, a locator is stored in the database table and points to the location of the actual data. External files (binary files, or BFILEs) are managed similarly. The JDBC drivers can support these types through the use of streams:

- BLOBs (unstructured binary data)

- CLOBs (character data)

- BFILEs (external files)

LOBs and BFILEs behave differently from the other types of streaming data described in this chapter. The driver transfers data between server and client as a Java stream. However, unlike most Java streams, a locator representing the data is stored in the table. Thus, you can access the data at any time during the life of the connection.

#### Streaming BLOBs and CLOBs

When a query selects one or more `CLOB` or `BLOB` columns, the JDBC driver transfers to the client the data pointed to by the locator. The driver performs the transfer as a Java stream. To manipulate CLOB or BLOB data from JDBC, use methods in the Oracle extension classes `oracle.sql.BLOB` and `oracle.sql.CLOB`. These classes provide functionality such as reading from the CLOB or BLOB into an input stream, writing from an output stream into a CLOB or BLOB, determining the length of a CLOB or BLOB, and closing a CLOB or BLOB.

For a complete discussion of how to use streaming CLOB and BLOB data, see "Reading and Writing BLOB and CLOB Data".

Important: The JDBC 2.0 specification states that `PreparedStatement` methods `setBinaryStream()` and `setObject()` can be used to input a stream value as a BLOB, and that the `PreparedStatement` methods `setAsciiStream()`, `setUnicodeStream()`, `setCharacterStream()`, and `setObject()` can be used to input a stream value as a CLOB. This bypasses the LOB locator, going directly to the LOB data itself. In the implementation of the Oracle JDBC drivers, this functionality is supported only for a configuration using an 8.1.6 database and 8.1.6 JDBC OCI driver. Do not use this functionality for any other configuration, as data corruption can result.

#### Streaming BFILEs

An external file, or BFILE, is used to store a locator to a file outside the database, stored somewhere on the filesystem of the data server. The locator points to the actual location of the file.

When a query selects one or more `BFILE` columns, the JDBC driver transfers to the client the file pointed to by the locator. The transfer is performed in a Java stream. To manipulate BFILE data from JDBC, use methods in the Oracle extension class `oracle.sql.BFILE`. This class provides functionality such as reading from the BFILE into an input stream, writing from an output stream into a BFILE, determining the length of a BFILE, and closing a BFILE.

For a complete discussion of how to use streaming BFILE data, see "Reading BFILE Data".

### Closing a Stream

You can discard the data from a stream at any time by calling the stream's `close()` method. You can also close and discard the stream by closing its result set or connection object. You can find more information about the `close()` method for data streams in "Bypassing Streaming Data Columns". For information on how to avoid closing a stream and discarding its data by accident, see "Streaming Data Precautions".

### Notes and Precautions on Streams

This section discusses several noteworthy and cautionary issues regarding the use of streams:

- Streaming Data Precautions

- Using Streams to Avoid Limits on setBytes() and setString()

- Streaming and Row Prefetching

#### Streaming Data Precautions

This section describes some of the precautions you must take to ensure that you do not accidentally discard or lose your stream data. The drivers automatically discard stream data if you perform any JDBC operation that communicates with the database, other than reading the current stream. Two common precautions are described:

- Use the stream data after you access it.

  To recover the data from a column containing a data stream, it is not enough to `get` the column; you must immediately process its contents. Otherwise, the contents will be discarded when you get the next column.

- Call the stream column in SELECT-list order.

  If your query selects multiple columns, the database sends each row as a set of bytes representing the columns in the `SELECT` order. If one of the columns contains stream data, the database sends the entire data stream before proceeding to the next column.

  If you do not use the SELECT-list order to access data, then you can lose the stream data. That is, if you bypass the stream data column and access data in a column that follows it, the stream data will be lost. For example, if you try to access the data for the `NUMBER` column before reading the data from the stream data column, the JDBC driver first reads then discards the streaming data automatically. This can be very inefficient if the `LONG` column contains a large amount of data.

  If you try to access the `LONG` column later in the program, the data will not be available and the driver will return a "`Stream Closed`" error.

The second point is illustrated in the following example:

    ResultSet rset = stmt.executeQuery
            ("select DATECOL, LONGCOL, NUMBERCOL from TABLE");
    while rset.next()
    {
       int n = rset.getInt(3);  // This discards the streaming data
       InputStream is = rset.getAsciiStream(2);
                                // Raises an error: stream closed.
    }

If you get the stream but do not use it before you get the `NUMBER` column, the stream still closes automatically:

    ResultSet rset = stmt.executeQuery
                     ("select DATECOL, LONGCOL, NUMBERCOL from TABLE");
    while rset.next()
    {
       InputStream is = rset.getAsciiStream(2); // Get the stream
       int n = rset.getInt(3);
       // Discards streaming data and closes the stream
    }
    int c = is.read(); // c is -1: no more characters to read-stream closed

#### Using Streams to Avoid Limits on setBytes() and setString()

There is a limit on the maximum size of the array which can be bound using the `PreparedStatement` class `setBytes()` method, and on the size of the string which can be bound using the `setString()` method.

Above the limits, which depend on the version of the server you use, you should use `setBinaryStream()` or `setCharacterStream()` instead.

When connecting to an Oracle8 database, the limit for `setBytes()` is 2000 bytes (the maximum size of a `RAW` in Oracle8) and the limit for `setString()` is 4000 bytes (the maximum size of a `VARCHAR2` in Oracle8).

When connecting to an Oracle7 database, the limit for `setBytes()` is 255 bytes (the maximum size of a `RAW` in Oracle7) and the limit for `setString()` is 2000 bytes (the maximum size of a `VARCHAR2` in Oracle7).

The 8.1.6 Oracle JDBC drivers may not raise an error if you exceed the limit when using `setBytes()` or `setString()`, but you may receive the following error:

    ORA-17070: Data size bigger than max size for this type

Future versions of the Oracle drivers will raise an error if the length exceeds these limits.

Note: This discussion applies to binds in SQL, not PL/SQL.

#### Streaming and Row Prefetching

If the JDBC driver encounters a column containing a data stream, row prefetching is set back to 1.

Row prefetching is an Oracle performance enhancement that allows multiple rows of data to be retrieved with each trip to the database. See "Oracle Row Prefetching".

| Copyright © 1996-2000, Oracle Corporation. All Rights Reserved. |
Library | Service | Contents | Index