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 three types of streams that the JDBC API provides. These three streams are:
  • setBinaryStream

    Use for streams that contain uninterpreted bytes

  • setAsciiStream

    Use for streams that contain ASCII characters

  • setUnicodeStream

    Use for streams that contain Unicode characters

JDBC 3.0 requires that you specify the length of the stream and Derby enforces this requirement if your application runs on JDK 5 or earlier. If your application runs on JDK 6, then Derby exposes a JDBC 4.0 implementation, which lets you use the streaming interfaces without having to specify the stream length. The stream object passed to these three methods can be either a standard Java stream object or the user's own subclass that implements the standard interface.

According to the JDBC standard, streams can be stored only in columns with the data types shown in the following table.
Table 1. Streamable JDBC Data Types
Column Data Type Corresponding Java Type AsciiStream UnicodeStream BinaryStream
CLOB java.sql.Clob x x  
CHAR   x x  
VARCHAR   x x  
BINARY   x x x
BLOB java.sql.Blob x x x
  • A large X indicates the preferred target data type for the type of stream. See Mapping of java.sql.Types to SQL Types
  • If the stream is stored in a column of a type other than LONG VARCHAR or LONG VARCHAR FOR BIT DATA, the entire stream must be able to fit into memory at one time. Streams stored in LONG VARCHAR and LONG VARCHAR FOR BIT DATA columns do not have this limitation.
  • Streams cannot be stored in columns of the other built-in data types or columns of user-defined data types.


The following example shows how a user can store a streamed in a LONG VARCHAR column:
Statement s = conn.createStatement();
s.executeUpdate("CREATE TABLE atable (a INT, b LONG VARCHAR)");
conn.commit(); file = new"derby.txt");
int fileLength = (int) file.length();
// first, create an input stream fin = new;
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);