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

## Support for Streams

Standard SQLJ provides three specialized classes, listed below, for convenient processing of long data in streams. These stream types can be used for iterator columns to retrieve data from the database, or for input host variables to send data to the database. As with Java streams in general, these classes allow the convenience of processing and transferring large data items in manageable chunks.

- `BinaryStream`

- `AsciiStream`

- `UnicodeStream`

These classes are in the `sqlj.runtime` package.

This section discusses general use of these classes, Oracle SQLJ extended functionality, and stream class methods.

### General Use of SQLJ Streams

With respect to an Oracle8i database, Table 5-1 lists the datatypes you would typically process using these stream classes. To summarize: `AsciiStream` and `UnicodeStream` are typically used for datatype `LONG` (`java.sql.Types.LONGVARCHAR`), but might also be used for datatype `VARCHAR2` (`Types.VARCHAR`); `BinaryStream` is typically used for datatype `LONG RAW` (`Types.LONGVARBINARY`), but might also be used for datatype `RAW` (`Types.BINARY` or `Types.VARBINARY`).

Of course, any use of streams is at your discretion. As Table 5-1 documents, `LONG` and `VARCHAR2` data can also be manifested in Java strings, while `RAW` and `LONGRAW` data can also be manifested in Java byte arrays. Furthermore, if your database supports large object types such as `BLOB` (binary large object) and `CLOB` (character large object), you may find these to be preferable to using types such as `LONG` and `LONG RAW` (although streams may still be used in extracting data from large objects). Oracle8i supports large object types--see "Support for BLOB, CLOB, and BFILE".

All three SQLJ stream classes are subclasses of the standard Java input stream class, `java.io.InputStream`, and act as wrappers to provide the functionality required by SQLJ. This functionality is to communicate to SQLJ the type and length of data in the underlying stream so that it can be processed and formatted properly.

You can use the SQLJ stream types for host variables to send data to the database or iterator columns to receive data from the database.

Note: In using any method that takes an `InputStream` object as input, you can use an object of any of the SQLJ stream classes instead.

### Using SQLJ Streams to Send Data to the Database

Standard SQLJ allows you to use streams as host variables to update the database.

A key point in sending a SQLJ stream to the database is that you must somehow determine the length of the data and specify that length to the constructor of the SQLJ stream. This will be further discussed below.

You can use a SQLJ stream to send data to the database as follows:

1. Determine the length of your data.

2. Create a standard Java input stream--an instance of`java.io.InputStream` or some subclass--as you normally would.

3. Create an instance of the appropriate SQLJ stream class (depending on the type of data), passing the input stream and length (as an `int`) to the constructor.

4. Use the SQLJ stream instance as a host variable in a suitable SQL operation in a SQLJ executable statement.

5. Close the stream (this is not required, but is recommended).

This section now goes into more detail regarding two typical examples of sending a SQLJ stream to the database:

- using an operating system file to update a `LONG` or `LONG RAW` column (this can be either a binary file to update a `LONG RAW` column, or an ASCII or Unicode file to update a `LONG` column)

- using a byte array to update a `LONG RAW` column

#### Updating LONG or LONG RAW from a File

In updating a database column (presumably a `LONG` or `LONG RAW` column) from a file, a step is needed to determine the length. You can do this by creating a `java.io.File` object before you create your input stream.

Here are the steps in updating the database from a file:

1. Create a `java.io.File` object from your file. You can specify the file path name to the `File` class constructor.

2. Use the `length()` method of the `File` object to determine the length of the data. This method returns a `long` value, which you must cast to an `int` for input to the SQLJ stream class constructor.

   Note: Before performing this cast, test the `long` value to make sure it is not too big to fit into an `int` variable. The static constant `MAX_VALUE` in the class `java.lang.Integer` indicates the largest possible Java `int` value.

3. Create a `java.io.FileInputStream` object from your `File` object. You can pass the `File` object to the `FileInputStream` constructor.

4. Create an appropriate SQLJ stream object. This would be a `BinaryStream` object for a binary file, an `AsciiStream` object for an ASCII file, or a `UnicodeStream` object for a Unicode file. Pass the `FileInputStream` object and data length (as an `int`) to the SQLJ stream class constructor.

   The SQLJ stream constructor signatures are all identical, as follows:

       BinaryStream (InputStream in, int length)
       AsciiStream (InputStream in, int length)
       UnicodeStream (InputStream in, int length)

   An instance of `java.io.InputStream` or of any subclass, such as `FileInputStream`, can be input to these constructors.

5. Use the SQLJ stream object as a host variable in an appropriate SQL operation in a SQLJ executable statement.

The following is an example of writing `LONG` data to the database from a file. Presume you have an HTML file in `/private/mydir/myfile.html` and you want to insert the file contents into a `LONG` column called `asciidata` in a database table named `filetable`.

Imports:

    import java.io.*;
    import sqlj.runtime.*;

Executable code:

    File myfile = new File ("/private/mydir/myfile.html");
    int length = (int)myfile.length();     // Must cast long output to int.
    FileInputStream fileinstream = new FileInputStream(myfile);
    AsciiStream asciistream = new AsciiStream(fileinstream, length);
    #sql { INSERT INTO filetable (asciidata) VALUES (:asciistream) };
    asciistream.close();
    ...

#### Updating LONG RAW from a Byte Array

You must determine the length of the data before updating the database from a byte array. (Presumably you would be updating a `LONG RAW` column.) This is more trivial for arrays than for files, though, because all Java arrays have functionality to return the length.

Here are the steps in updating the database from a byte array:

1. Use the `length` functionality of the array to determine the length of the data. This returns an `int`, which is what you will need for the constructor of any of the SQLJ stream classes.

2. Create a `java.io.ByteArrayInputStream` object from your array. You can pass the byte array to the `ByteArrayInputStream` constructor.

3. Create a `BinaryStream` object. Pass the `ByteArrayInputStream` object and data length (as an `int`) to the `BinaryStream` class constructor.

   The constructor signature is as follows:

       BinaryStream (InputStream in, int length)

   You can use an instance of `java.io.InputStream` or of any subclass, such as the `ByteArrayInputStream` class.

4. Use the SQLJ stream object as a host variable in an appropriate SQL operation in a SQLJ executable statement.

The following is an example of writing `LONG RAW` data to the database from a byte array. Presume you have a byte array `bytearray[]` and you want to insert its contents into a `LONG RAW` column called `BINDATA` in a database table named `BINTABLE`.

Imports:

    import java.io.*;
    import sqlj.runtime.*;

Executable code:

    byte[] bytearray = new byte[100];

    (Populate bytearray somehow.)
    ...
    int length = bytearray.length;
    ByteArrayInputStream arraystream = new ByteArrayInputStream(bytearray);
    BinaryStream binstream = new BinaryStream(arraystream, length);
    #sql { INSERT INTO bintable (bindata) VALUES (:binstream) };
    binstream.close();
    ...

Note: It is not necessary to use a stream as in this example--you can also update the database directly from a byte array.

### Retrieving Data into Streams--Precautions

You can also use the SQLJ stream classes to retrieve data from the database, but the logistics of using streams make certain precautions necessary with some database products.

When reading long data and writing it to a stream using an Oracle8i database and Oracle JDBC driver, you must be careful in how you access and process the stream data.

As the Oracle JDBC drivers access data from an iterator row, they must flush any stream item from the communications pipe before accessing the next data item. Even though the stream data is written to a local stream as the iterator row is processed, this stream data will be lost if you do not read it from the local stream before the JDBC driver accesses the next data item. This is because of the way streams must be processed, due to their potentially large size and unknown length.

Therefore, as soon as your Oracle JDBC driver has accessed a stream item and written it to a local stream variable, you must read and process the local stream before anything else is accessed from the iterator.

This is especially problematic in using positional iterators, with their requisite `FETCH INTO` syntax. With each fetch, all columns are read before any are processed. Therefore, there can be only one stream item, and it must be the last item accessed.

To summarize the precautions you must take:

- When using a positional iterator, you can have only one stream column, and it must be the last column. As soon as you have fetched each row of the iterator, writing the stream item to a local input stream variable in the process, you must read and process the local stream variable before advancing to the next row of the iterator.

- When using a named iterator, you can have multiple stream columns; however, as you process each iterator row, each time you access a stream field, writing the data to a local stream variable in the process, you must read and process the local stream immediately, before reading anything else from the iterator.

  Furthermore, in processing each row of a named iterator, you must call the column accessor methods in the same order in which the database columns were selected in the query that populated the iterator. As mentioned in a similar preceding discussion, this is because stream data remains in the communications pipe after the query. If you try to access columns out of order, then the stream data may be skipped over and lost in the course of accessing other columns.

  Note: Oracle8i and the Oracle JDBC drivers do not support use of streams in `SELECT INTO` statements.

### Using SQLJ Streams to Retrieve Data from the Database

To retrieve data from a database column as a stream, standard SQLJ allows you to select data into a named or positional iterator that has a column of the appropriate SQLJ stream type.

This section covers the basic steps in retrieving data into a SQLJ stream using a positional iterator or a named iterator, taking into account the precautions documented in "Retrieving Data into Streams--Precautions".

These are general steps. For more information, see "Processing SQLJ Streams" and "Examples of Retrieving and Processing Stream Data".

#### Using a SQLJ Stream Column in a Positional Iterator

Use the following steps to retrieve data into a SQLJ stream using a positional iterator:

1. Declare a positional iterator class with the last column being of the appropriate SQLJ stream type.

2. Declare a local variable of your iterator type.

3. Declare a local variable of the appropriate SQLJ stream type. This will be used as a host variable to receive data from each row of the SQLJ stream column of the iterator.

4. Query the database to populate the iterator you declared in step 2.

5. Process the iterator, as usual (see "Using Positional Iterators"). Because the host variables in the INTO-list of the `FETCH INTO` statement must be in the same order as the columns of the positional iterator, the local input stream variable is the last host variable in the list.

6. In the iterator processing loop, after each iterator row is accessed, immediately read and process the local input stream, storing or outputting the stream data as desired.

7. Close the local input stream each time through the iterator processing loop (this is not required, but is recommended).

8. Close the iterator.

#### Using SQLJ Stream Columns in a Named Iterator

Use the following steps to retrieve data into one or more SQLJ streams using a named iterator:

1. Declare a named iterator class with one or more columns of appropriate SQLJ stream type.

2. Declare a local variable of your iterator type.

3. Declare a local variable of some input stream type for each SQLJ stream column in the iterator. These will be used to receive data from the stream-column accessor methods. These local stream variables do not have to be SQLJ stream types; they can be standard `java.io.InputStream` if desired. (They do not have to be SQLJ stream types, because the data was already correctly formatted as a result of the iterator columns being of appropriate SQLJ stream types.)

4. Query the database to populate the iterator you declared in step 2.

5. Process the iterator, as usual (see "Using Named Iterators"). In processing each row of the iterator, as each stream-column accessor method returns the stream data, write it to the corresponding local input stream variable you declared in step 3.

   To ensure that stream data will not be lost, call the column accessor methods in the same order in which columns were selected in the query in step 4.

6. In the iterator processing loop, immediately after calling the accessor method for any stream column and writing the data to a local input stream variable, read and process the local input stream, storing or outputting the stream data as desired.

7. Close the local input stream each time through the iterator processing loop (this is not required, but is recommended).

8. Close the iterator.

   Note: When you populate a SQLJ stream object with data from an iterator or the database, the length attribute of the stream will not be meaningful. This attribute is meaningful only when you set it explicitly, either using the `setLength()` method that each SQLJ stream class provides, or specifying the length to the constructor (as discussed in "Using SQLJ Streams to Send Data to the Database").

### Processing SQLJ Streams

In processing a SQLJ stream column in a named or positional iterator, the local stream variable used to receive the stream data can be either a SQLJ stream type or the standard `java.io.InputStream` type. In either case, standard input stream methods are supported.

If the local stream variable is a SQLJ stream type--`BinaryStream`, `AsciiStream`, or `UnicodeStream`--you have the option of either reading data directly from the SQLJ stream object, or retrieving the underlying `java.io.InputStream` object and reading data from that. This is just a matter of preference--the former approach is simpler; the latter approach involves more direct and efficient data access.

The following important methods of the `InputStream` class--the `skip()` method, `close()` method, and three forms of the `read()` method--are supported by the SQLJ stream classes as well.

- `int read ()`--Reads the next byte of data from the input stream. The byte of data is returned as an `int` value in the range 0 to 255. If the end of the stream has already been reached, then the value -1 is returned. This method blocks until one of the following: 1) input data is available; 2) the end of the stream is detected; or 3) an exception is thrown.

- `int read (byte b[])`--Reads up to `b.length` bytes of data from the input stream, writing the data into the specified `b[]` byte array. It returns an `int` value indicating how many bytes were read or -1 if the end of the stream has already been reached. This method blocks until input is available.

- `int read (byte b[], int off, int len)`--Reads up to `len` (length) bytes of data from the input stream, starting at the byte specified by the offset, `off`, and writing the data into the specified `b[]` byte array. It returns an `int` value indicating how many bytes were read or -1 if the end of the stream has already been reached. This method blocks until input is available.

- `long skip (long n)`--Skips over and discards `n` bytes of data from the input stream. In some circumstances, however, this method will actually skip a smaller number of bytes. It returns a `long` value indicating the actual number of bytes skipped.

- `void close()`--Closes the stream and releases any associated resources.

In addition, SQLJ stream classes support the following important method:

- `InputStream getInputStream()`--Returns the underlying input stream being wrapped, as a `java.io.InputStream` object.

### Examples of Retrieving and Processing Stream Data

This section provides examples of various scenarios of retrieving stream data from the database, as follows:

- using a `SELECT` statement to select data from a `LONG` column and populate a SQLJ `AsciiStream` column in a named iterator

- using a `SELECT` statement to select data from a `LONG RAW` column and populate a SQLJ `BinaryStream` column in a positional iterator

##### Example: Selecting LONG Data into AsciiStream Column of Named Iterator

This example selects data from a `LONG` database column, populating a SQLJ `AsciiStream` column in a named iterator.

Assume there is a table named `FILETABLE` with a `VARCHAR2` column called `FILENAME` that contains file names, and a `LONG` column called `FILECONTENTS` that contains file contents in ASCII.

Imports and declarations:

    import sqlj.runtime.*;
    import java.io.*;

    #sql iterator MyNamedIter (String filename, AsciiStream filecontents);

Executable code:

    MyNamedIter namediter = null;
    String fname;
    AsciiStream ascstream;
    #sql namediter = { SELECT filename, filecontents FROM filetable };
    while (namediter.next()) {
       fname = namediter.filename();
       ascstream = namediter.filecontents();
       System.out.println("Contents for file " + fname + ":");
       printStream(ascstream);
       ascstream.close();
    }
    namediter.close();
    ...
    public void printStream(InputStream in) throws IOException
    {
       int asciichar;
       while ((asciichar = in.read()) != -1) {
          System.out.print((char)asciichar);
       }
    }

Remember that you can pass a SQLJ stream to any method that takes a standard `java.io.InputStream` as an input parameter.

##### Example: Selecting LONG RAW Data into BinaryStream Column of Positional Iterator

This example selects data from a `LONG RAW` database column, populating a SQLJ `BinaryStream` column in a positional iterator.

As explained in "Retrieving Data into Streams--Precautions", there can be only one stream column in a positional iterator, and it must be the last column.

Assume there is a table named `BINTABLE` with a `NUMBER` column called `IDENTIFIER` and a `LONG RAW` column called `BINDATA` that contains binary data associated with the identifier.

Imports and declarations:

    import sqlj.runtime.*;

    #sql iterator MyPosIter (int, BinaryStream);

Executable code:

    MyPosIter positer = null;
    int id=0;
    BinaryStream binstream=null;
    #sql positer = { SELECT identifier, bindata FROM bintable };
    while (true) {
       #sql { FETCH :positer INTO :id, :binstream };
       if (positer.endFetch()) break;

       (...process data as desired...)

       binstream.close();
    }
    positer.close();
    ...

### SQLJ Stream Objects as Output Parameters and Function Return Values

As described in the preceding sections, standard SQLJ supports use of the `BinaryStream`, `AsciiStream`, and `UnicodeStream` classes in the package `sqlj.runtime` for retrieval of stream data into iterator columns.

In addition, the Oracle SQLJ implementation allows the following uses of SQLJ stream types if you are using an Oracle database, Oracle JDBC driver, Oracle customizer, and Oracle SQLJ runtime:

- They can appear as `OUT` or `INOUT` host variables from a stored procedure or function call.

- They can appear as the return value from a stored function call.

#### Streams as Stored Procedure Output Parameters

You can use the types `AsciiStream`, `BinaryStream`, and `UnicodeStream` as the assignment type for a stored procedure or stored function `OUT` or `INOUT` parameter.

Assume the following table definition:

    CREATE TABLE streamexample (name VARCHAR2 (256), data LONG);
    INSERT INTO streamexample (data, name)
       VALUES
       ('0000000000111111111112222222222333333333344444444445555555555',
       'StreamExample');

Also presume the following stored procedure definition, which uses the `STREAMEXAMPLE` table:

    CREATE OR REPLACE PROCEDURE out_longdata
                                (dataname VARCHAR2, longdata OUT LONG) IS
    BEGIN
       SELECT data INTO longdata FROM streamexample WHERE name = dataname;
    END out_longdata;

The following sample code uses a call to the `out_longdata` stored procedure to read the long data.

Imports:

    import sqlj.runtime.*;

Executable code:

    AsciiStream data;
    #sql { CALL out_longdata('StreamExample', :OUT data) };
    int c;
    while ((c = data.read ()) != -1)
       System.out.print((char)c);
    System.out.flush();
    data.close();
    ...

Note: Closing the stream is recommended, but not required.

#### Streams as Stored Function Results

You can use the types `AsciiStream`, `BinaryStream` and `UnicodeStream` as the assignment type for a stored function return result.

Assume the same `STREAMEXAMPLE` table definition as in the preceding stored procedure example.

Also assume the following stored function definition, which uses the `STREAMEXAMPLE` table:

    CREATE OR REPLACE FUNCTION get_longdata (dataname VARCHAR2) RETURN long
       IS longdata LONG;
    BEGIN
       SELECT data INTO longdata FROM streamexample WHERE name = dataname;
       RETURN longdata;
    END get_longdata;

The following sample code uses a call to the `get_longdata` stored function to read the long data.

Imports:

    import sqlj.runtime.*;

Executable code:

    AsciiStream data;
    #sql data = { VALUES(get_longdata('StreamExample')) };
    int c;
    while ((c = data.read ()) != -1)
       System.out.print((char)c);
    System.out.flush();
    data.close();
    ...

Note: Closing the stream is recommended, but not required.

### Stream Class Methods

The SQLJ stream classes in the `sqlj.runtime` package--`BinaryStream`, `AsciiStream`, and `UnicodeStream`--are all subclasses of the `sqlj.runtime.StreamWrapper` class.

The `StreamWrapper` class provides the following methods inherited by the SQLJ stream classes:

- `InputStream getInputStream()`--As discussed in "Processing SQLJ Streams", you can optionally use this method to get the underlying `java.io.InputStream` object of any SQLJ stream object. This is not required, however, as you can also process SQLJ stream objects directly.

- `void setLength(int length)`--You can use this to set the `length` attribute of a SQLJ stream object. This is not necessary if you have already set `length` in constructing the stream object, unless you want to change it for some reason.

  Bear in mind that the `length` attribute must be set to an appropriate value before you send a SQLJ stream to the database.

- `int getLength()`--This method returns the value of the `length` attribute of a SQLJ stream. This value is meaningful only if you explicitly set it using the stream object constructor or the `setLength()` method. When you retrieve data into a stream, the `length` attribute is not set automatically.

  Note: The `sqlj.runtime.StreamWrapper` class is a subclass of the `java.io.FilterInputStream` class, which is a subclass of the `java.io.InputStream` class.

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