Using Streams

You can retrieve data from LOB columns in binary streams or in character streams. To do this with JDBC, you can use the methods getBinaryStream() and getCharacterStream() of java.sql.ResultSet.

To insert data from a stream, you can use the setBinaryStream() and setCharacterStream() methods of java.sql.PreparedStatement.

When using streams with Open SQL, you should be aware that there are also certain limitations.

Features

Reading LOBs in Streams

Using the getBinaryStream() and getCharacterStream() of the result set is equivalent to calling getBlob().getBinaryStream() and getClob().getCharacterStream(). The following example demonstrates how you can use a binary stream to retrieve a picture from a database table column.

Syntax Syntax

Using streams and JDBC to read LOBs

String query = "select REFMANUAL, PICTURE from BC_CR_VEHICLE " + "where NAME = ?";

PreparedStatement ps = conn.prepareStatement(query);

try {
  ps.setString(1, "Golf");
  ResultSet rs = ps.executeQuery();
  try {
   while (rs.next()) {
	 String refManual = rs.getString(1);
	 InputStream is = rs.getBinaryStream(2);
	 ...
	} finally {
		rs.close();
	}

End of the code.

Writing to LOBs from Streams

When working with JDBC, you can write binary data to a BINARY, VARBINARY, LONGVARBINARY or BLOB column using the setBinaryStream() method of java.sql.PreparedStatement. Character data can be written to a CHAR, VARCHAR, LONGVARCHAR or CLOB column using the setCharacterStream() method of java.sql.PreparedStatement. The following example illustrates the use of streams for inserting a text file in the REFMANUAL column of a database table.

Syntax Syntax

Using streams and JDBC to write to LOBs

String fileName = "refmanual.txt";
File f = new File(fileName);
Reader r = new FileReader(f);
String insertStmt = "insert into VEHICLE " +
	"(NAME, REFMANUAL, PICTURE) VALUES (?, ?, ?)";
PreparedStatement ps = conn.prepareStatement(insertStmt);
try {
	ps.setString(1, "Golf");
	ps.setCharacterStream(2, r, f.length());
	ps.setBytes(3, picture);
	ps.executeUpdate();
} finally {
	ps.close();
}
r.close();

End of the code.

Using streams to write data to the database has some limitations:

The length passed as the third argument of the setBinaryStream() and setCharacterStream() methods or used in the constructor of the sqlj.runtime.BinaryStream and sqlj.runtime.CharacterStream must exactly match the total length of the stream.
A particular stream must not be used repeatedly as the argument of a setBinaryStream() or setCharacterStream() method or as an IN host variable in SQLJ. If you use batching, you must not use a particular instance of a stream as a parameter in multiple rows of the same batch. In addition, if a stream is used as a parameter in a batch, this parameter must be set with a different stream in every row of the batch and must not be left unchanged.
Open SQL does not specify at which point in time the content of the stream is read. To achieve portability, the application must guarantee that the content of the stream does not change - that is, the InputStream argument from the setBinaryStream() or the Reader argument from the setCharacterStream() method must not be changed once you have passed them as parameters to the methods.