Prepared statements and streaming columns

setXXXStream requests stream data between the application and the database.

JDBC allows an IN parameter to be set to a Java input stream for passing in large amounts of data in smaller chunks. When the statement is run, the JDBC driver makes repeated calls to this input stream. Derby supports the following JDBC stream methods for PreparedStatement objects:
  • setBinaryStream

    Use for streams that contain uninterpreted bytes

  • setAsciiStream

    Use for streams that contain ASCII characters

  • setCharacterStream

    Use for streams that contain characters

Note: Derby does not support the setNCharacterStream method or the deprecated setUnicodeStream method.

JDBC 3.0 requires that you specify the length of the stream, and Derby enforces this requirement if your application runs on JDK 5. If your application runs on JDK 6 or later, Derby exposes a JDBC 4.0 implementation, which lets you use the streaming interfaces without having to specify the stream length.

Note: If you omit the length argument when the stream object is a LOB greater than a single page in size, performance will be impaired if you later retrieve the length of the LOB. However, if you are simply inserting or reading data, performance is unaffected.

The stream object passed to setBinaryStream and setAsciiStream can be either a standard Java stream object or the user's own subclass that implements the standard java.io.InputStream interface. The object passed to setCharacterStream must be a subclass of the abstract java.io.Reader class.

According to the JDBC standard, streams can be stored only in columns with the data types shown in the following table. The word "Preferred" indicates the preferred target data type for the type of stream. See Mapping of java.sql.Types to SQL types.

Table 1. Streamable JDBC data types
Column Data Type Corresponding Java Type AsciiStream CharacterStream BinaryStream
CLOB java.sql.Clob Yes Yes No
CHAR None Yes Yes No
VARCHAR None Yes Yes No
LONGVARCHAR None Preferred Preferred No
BINARY None Yes Yes Yes
BLOB java.sql.Blob Yes Yes Yes
VARBINARY None Yes Yes Yes
LONGVARBINARY None Yes Yes Preferred
Note: Streams cannot be stored in columns of the other built-in data types or columns of user-defined data types.

Example

The following code fragment shows how a user can store a streamed, ASCII-encoded java.io.File in a LONG VARCHAR column:

    Statement s = conn.createStatement();
    s.executeUpdate("CREATE TABLE atable (a INT, b LONG VARCHAR)");
    conn.commit();

    java.io.File file = new java.io.File("derby.txt");
    int fileLength = (int) file.length();

    // create an input stream
    java.io.InputStream fin = new java.io.FileInputStream(file);
    PreparedStatement ps = conn.prepareStatement(
        "INSERT INTO atable VALUES (?, ?)");
    ps.setInt(1, 1);

    // set the value of the input parameter to the input stream
    ps.setAsciiStream(2, fin, fileLength);
    ps.execute();
    conn.commit();