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.
Open SQL/SQLJ provides the sqlj.runtime.BinaryStream and sqlj.runtime.CharacterStream classes, which also enable you to read and write LOBs using streams.
When using streams with Open SQL, you should be aware that there are also certain limitations.
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.
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(); } |
In SQLJ you can use sqlj.rutime.BinaryStream and sqlj.runtime.CharacterStream as host variables in an SQLJ single row query, a FETCH statement or an iterator. The following example demonstrates how you can use streams to retrieve a picture from a database table column.
Using streams and SQLJ to read LOBs
int employeeId = ...; String filename = ...; java.io.File file = new File(filename); java.io.FileOutputStream outputStream = new FileOutputStream(file); sqlj.runtime.BinaryStream image;
#sql [ctx] { SELECT image INTO :image FROM employee WHERE employee_id = :employeeId };
java.io.InputStream inputStream = image.getInputStream();
int i; while ((i = inputStream.read()) != -1) { outputStream.write(i); } outputStream.close(); inputStream.close(); |
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.
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(); |
With SQLJ, you can use sqlj.runtime.BinaryStream and sqlj.runtime.CharacterStream as IN host variables in INSERT and UPDATE statements. The following example uses an instance of sqlj.runtime.BinaryStream to replace an image corresponding to the specified employee ID.
Using streams and SQLJ to write to LOBs
Int employeeId = ...; String filename = ...;
java.io.File file = new File(filename); java.io.FileInputStream inputStream = new FileInputStream(file); sqlj.runtime.BinaryStream newImage = new sqlj.runtime.BinaryStream(inputStream, file.length());
#sql [ctx] { UPDATE images SET image = :newImage WHERE employee_id = :employeeId };
inputStream.close(); |
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 will be 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.