LOB data interface

OceanBase Connector/J provides a data interface, which is a simplified mechanism for writing and reading the content of the entire LOB.

The data interface uses standard Java Database Connectivity (JDBC) methods, such as getString and setBytes, to read and write LOB data. The data interface simplifies coding and accelerates data access. However, unlike the standard java.sql.Blob, java.sql.Clob, and java.sql.NClob interfaces, the data interface provided by OceanBase Connector/J does not support random access, and the volume of accessed data cannot exceed 2,147,483,648 elements.

Input

OceanBase Connector/J extends the setBytes, setBinaryStream, setString, setCharacterStream, and setAsciiStream methods of PreparedStatement, to enhance the capability of using BLOBs, CLOBs, and NCLOBs.

The maximum length of an SQL statement (such as the INSERT statement) executed by the internal driver of the server is 4,000 bytes. This limitation does not apply to PL statements. To bypass this limitation, you can encapsulate the INSERT statement into a PL block by using the following method:

BEGIN
 INSERT id, name INTO clob_emp VALUES(?,?);
END

Three input modes are available for large-scale data:

• Direct binding

  The binding size in this mode is limited, but the input efficiency is the highest. The data in all input columns is concatenated in a data block and then sent to the server. All data, including data involved in multiple executions of batch processing, is sent in a single network operation.

• Stream binding

  The data is put at the end. Only one stream is sent at a time. Therefore, multiple round trips may be required.

• LOB binding

  A temporary LOB is created. The data is copied to the LOB and bound to LOB locators. The temporary LOB is automatically released after execution. Multiple round trips are required for creating the temporary LOB and writing data to the LOB. The locators can be input in batches.

Take note of the following instructions on the input modes for SQL statements:

• The setBytes and setBinaryStream methods use direct binding for data less than 4,001 bytes.

• The setBytes and setBinaryStream methods use stream binding for data greater than 4,000 bytes.

• The setAsciiStream, setBinaryStream, and setCharacterStream methods take a long argument as the form length and use LOB binding for data with more than 2,147,483,648 elements. LOB binding is always used for forms whose lengths are not specified.

• The setString, setCharacterStream, and setAsciiStream methods use direct binding for data less than 32,767 characters.

• The setString, setCharacterStream, and setAsciiStream methods use stream binding for data greater than 32,766 characters.

• The new form of the setCharacterStream method needs to use the length defined by the long argument and use LOB binding for data with more than 2,147,483,648 elements. LOB binding is always used for forms whose lengths are not specified.

Take note of the following instructions on the input modes for PL statements:

• The setBytes and setBinaryStream methods use direct binding for data less than 32,767 bytes.

• The setBytes and setBinaryStream methods use LOB binding for data greater than 32,766 bytes.

• The setString, setCharacterStream, and setAsciiStream methods use direct binding for data less than 32,767 bytes in the database character set.

  Note

  The setString method is not applicable to BLOBs.

• The setString, setCharacterStream, and setAsciiStream methods use LOB binding for data greater than 32,766 bytes in the database character set.

Automatic switching of input modes affects some programs. Automatic switching may also result in additional parsing by the server, to cope with changes of parameter types. If the data size is changing around the threshold when a statement is repeatedly executed, the performance is affected. Switching to the stream binding mode affects batch processing.

Output

The getBytes, getBinaryStream, getString, getCharacterStream, and getAsciiStream methods of ResultSet and CallableStatement are extended so that they can be used with BLOBs, CLOBs, or OUT parameters. These methods are applicable to any LOBs whose lengths are less than 2,147,483,648.

The data interface accesses the LOB locators in the driver to perform operations and is transparent to application programming. You can enable LOB prefetch as needed to reduce or eliminate other database round-trip communication.

You can read and write BLOB or CLOB data in the same streaming transmission mechanism as with LONG RAW and LONG data. You can use the defineColumnType(nn, Types.LONGVARBINARY) or defineColumnType(nn, Types.LONGVARCH) method on columns to define the column type for data read and generate direct streams, which is similar to reading data from the LONG RAW or LONG columns.

CallableSatement and IN OUT parameters

PL requires that the same Java types be used for input and output of IN OUT parameters.

An IN OUT CLOB parameter of a stored procedure is expected to be set by using the setString method. For any IN and OUT parameters, the binds must be of the same type. If you are not sure about the data size, automatic switching of input modes will cause errors. For example, if you have learned that neither the input data nor the output data is greater than 32,766 bytes, you can use the setString method for input parameters, register OUT parameters as Types.VARCHAR, and use the getString method for output parameters.

A better solution is to modify the stored procedure so that it has separate IN and OUT parameters. Here is an example:

CREATE PROCEDURE clob_obproc( cc IN OUT CLOB );

The preceding example can be rewritten as follows:

CREATE PROCEDURE clob_obproc( cc_in IN CLOB, cc_out OUT CLOB );

Another solution is to use container blocks for calls. In the following example, the clob_plproc stored procedure can be wrapped with a Java string and be used for prepareCall statements:

"DECLARE cc_temp; BEGIN cc_temp := ?; clob_plproc( cc_temp); ? := cc_temp; END;"

In either solution, the setString method can be used for the first parameter, and the registerOutParameter method and Types.CLOB can be used for the second parameter.

Size limits

If you create a very large byte array or string, the performance of your Java memory management system may be affected. Read the information provided by the Java virtual machine (JVM) supplier about the impact of massive amounts of data elements on memory management, and try using a streaming interface.