Java™ data types for retrieving or updating LOB column data in SQLJ applications

When the deferPrepares property is set to true, and the IBM® Data Server Driver for JDBC and SQLJ processes an uncustomized SQLJ statement that includes host expressions, the driver might need to do extra processing to determine data types. This extra processing can impact performance.

When the JDBC driver cannot immediately determine the data type of a parameter that is used with a LOB column, you need to choose a parameter data type that is compatible with the LOB data type.

Input parameters for BLOB columns

For input parameters for BLOB columns, you can use either of the following techniques:

Use a java.sql.Blob input variable, which is an exact match for a BLOB column:
```
java.sql.Blob blobData; 
#sql {CALL STORPROC(:IN blobData)};
```
Before you can use a java.sql.Blob input variable, you need to create a java.sql.Blob object, and then populate that object.

Use an input parameter of type of sqlj.runtime.BinaryStream. A sqlj.runtime.BinaryStream object is compatible with a BLOB data type. For example:

java.io.ByteArrayInputStream byteStream = 
  new java.io.ByteArrayInputStream(byteData);
int numBytes = byteData.length;
sqlj.runtime.BinaryStream binStream = 
  new sqlj.runtime.BinaryStream(byteStream, numBytes);
#sql {CALL STORPROC(:IN binStream)};

You cannot use this technique for INOUT parameters.

Output parameters for BLOB columns

For output or INOUT parameters for BLOB columns, you can use the following technique:

Declare the output parameter or INOUT variable with a java.sql.Blob data type:

java.sql.Blob blobData = null;
#sql CALL STORPROC (:OUT blobData)};

java.sql.Blob blobData = null;
#sql CALL STORPROC (:INOUT blobData)};

Input parameters for CLOB columns

For input parameters for CLOB columns, you can use one of the following techniques:

Use a java.sql.Clob input variable, which is an exact match for a CLOB column:
```
#sql CALL STORPROC(:IN clobData)};
```
Before you can use a java.sql.Clob input variable, you need to create a java.sql.Clob object, and then populate that object.

Use one of the following types of stream IN parameters:

A sqlj.runtime.CharacterStream input parameter:

java.lang.String charData;
java.io.StringReader reader = new java.io.StringReader(charData);
sqlj.runtime.CharacterStream charStream =
  new sqlj.runtime.CharacterStream (reader, charData.length);
#sql {CALL STORPROC(:IN charStream)};

A sqlj.runtime.UnicodeStream parameter, for Unicode UTF-16 data:

byte[] charDataBytes = charData.getBytes("UnicodeBigUnmarked");
java.io.ByteArrayInputStream byteStream = 
  new java.io.ByteArrayInputStream(charDataBytes);
sqlj.runtime.UnicodeStream uniStream =
  new sqlj.runtime.UnicodeStream(byteStream, charDataBytes.length );
#sql {CALL STORPROC(:IN uniStream)};

A sqlj.runtime.AsciiStream parameter, for ASCII data:

byte[] charDataBytes = charData.getBytes("US-ASCII");
java.io.ByteArrayInputStream byteStream = 
  new java.io.ByteArrayInputStream (charDataBytes);
sqlj.runtime.AsciiStream asciiStream =
  new sqlj.runtime.AsciiStream (byteStream, charDataBytes.length);
#sql {CALL STORPROC(:IN asciiStream)};

For these calls, you need to specify the exact length of the input data. You cannot use this technique for INOUT parameters.

Use a java.lang.String input parameter:

java.lang.String charData;
#sql {CALL STORPROC(:IN charData)};

Output parameters for CLOB columns

For output or INOUT parameters for CLOB columns, you can use one of the following techniques:

Use a java.sql.Clob output variable, which is an exact match for a CLOB column:
```
java.sql.Clob clobData = null;
#sql CALL STORPROC(:OUT clobData)};
```
Use a java.lang.String output variable:
```
java.lang.String charData = null;
#sql CALL STORPROC(:OUT charData)};
```
This technique should be used only if you know that the length of the retrieved data is less than or equal to 32KB. Otherwise, the data is truncated.

Output parameters for DBCLOB columns

DBCLOB output or INOUT parameters for stored procedures are not supported.