DB2 Version 9.7 for Linux, UNIX, and Windows

Java data types for retrieving or updating LOB column data in JDBC applications

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.

When the deferPrepares property is set to true, and the IBM® Data Server Driver for JDBC and SQLJ processes a PreparedStatement.setXXX call, the driver might need to do extra processing to determine data types. This extra processing can impact performance.

Input parameters for BLOB columns

For IN parameters for BLOB columns, or INOUT parameters that are used for input to BLOB columns, you can use one of the following techniques:

Use a java.sql.Blob input variable, which is an exact match for a BLOB column:
```
cstmt.setBlob(parmIndex, blobData);
```

Use a CallableStatement.setObject call that specifies that the target data type is BLOB:

byte[] byteData = {(byte)0x1a, (byte)0x2b, (byte)0x3c};
cstmt.setObject(parmInd, byteData, java.sql.Types.BLOB);

Use an input parameter of type of java.io.ByteArrayInputStream with a CallableStatement.setBinaryStream call. A java.io.ByteArrayInputStream object is compatible with a BLOB data type. For this call, you need to specify the exact length of the input data:
```
java.io.ByteArrayInputStream byteStream = 
  new java.io.ByteArrayInputStream(byteData);
int numBytes = byteData.length;
cstmt.setBinaryStream(parmIndex, byteStream, numBytes);
```

Output parameters for BLOB columns

For OUT parameters for BLOB columns, or INOUT parameters that are used for output from BLOB columns, you can use the following technique:

Use the CallableStatement.registerOutParameter call to specify that an output parameter is of type BLOB. Then you can retrieve the parameter value into any variable that has a data type that is compatible with a BLOB data type. For example, the following code lets you retrieve a BLOB value into a byte[] variable:
```
cstmt.registerOutParameter(parmIndex, java.sql.Types.BLOB);
cstmt.execute();
byte[] byteData = cstmt.getBytes(parmIndex);
```

Input parameters for CLOB columns

For IN parameters for CLOB columns, or INOUT parameters that are used for input to 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:
```
cstmt.setClob(parmIndex, clobData);
```

Use a CallableStatement.setObject call that specifies that the target data type is CLOB:

String charData = "CharacterString";
cstmt.setObject(parmInd, charData, java.sql.Types.CLOB);

Use one of the following types of stream input parameters:

A java.io.StringReader input parameter with a cstmt.setCharacterStream call:

java.io.StringReader reader = new java.io.StringReader(charData);
cstmt.setCharacterStream(parmIndex, reader, charData.length);

A java.io.ByteArrayInputStream parameter with a cstmt.setAsciiStream call, for ASCII data:

byte[] charDataBytes = charData.getBytes("US-ASCII");
java.io.ByteArrayInputStream byteStream = 
  new java.io.ByteArrayInputStream (charDataBytes);
cstmt.setAsciiStream(parmIndex, byteStream, charDataBytes.length);

For these calls, you need to specify the exact length of the input data.

Use a String input parameter with a cstmt.setString call:
```
cstmt.setString(parmIndex, charData);
```
If the length of the data is greater than 32KB, and the JDBC driver has no DESCRIBE information about the parameter data type, the JDBC driver assigns the CLOB data type to the input data.
Use a String input parameter with a cstmt.setObject call, and specify the target data type as VARCHAR or LONGVARCHAR:
```
cstmt.setObject(parmIndex, charData, java.sql.Types.VARCHAR);
```
If the length of the data is greater than 32KB, and the JDBC driver has no DESCRIBE information about the parameter data type, the JDBC driver assigns the CLOB data type to the input data.

Output parameters for CLOB columns

For OUT parameters for CLOB columns, or INOUT parameters that are used for output from CLOB columns, you can use one of the following techniques:

Use the CallableStatement.registerOutParameter call to specify that an output parameter is of type CLOB. Then you can retrieve the parameter value into a Clob variable. For example:
```
cstmt.registerOutParameter(parmIndex, java.sql.Types.CLOB);
cstmt.execute();
Clob clobData = cstmt.getClob(parmIndex);
```
Use the CallableStatement.registerOutParameter call to specify that an output parameter is of type VARCHAR or LONGVARCHAR:
```
cstmt.registerOutParameter(parmIndex, java.sql.Types.VARCHAR);
cstmt.execute();
String charData = cstmt.getString(parmIndex);
```
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.