LOB data API

The OceanBase JDBC driver (OceanBase Client) provides a data API, which is a simplified mechanism for writing and reading the content of the entire LOB.

The data API uses the standard JDBC methods (such as getString and setBytes) to read and write LOB data. The data API simplifies coding and accelerates data access. However, unlike the standard java.sql.Blob, java.sql.Clob, and java.sql.NClob APIs, the data API does not support random access, and the volume of accessed data cannot exceed 2,147,483,648 elements.

Input

The OceanBase JDBC driver 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 (for example, the INSERT statement) executed by the internal driver of the server is 4,000 bytes. This limit does not apply to PL statements. To bypass this limit, 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 specified 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 4001 bytes.

• The setBytes and setBinaryStream methods use stream binding is used for data greater than 4000 bytes.

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

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

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

• The new form of the setCharacterStream method takes a long argument for length and uses LOB binding for length larger than 2147483647. LOB binding is always used for forms where the length is 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 smaller than 32,767 bytes.

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

• The setString, setCharacterStream, and setAsciiStream methods use direct binding for data smaller 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 larger 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 the ResultSet and CallableStatement operations are extended to be used with BLOBs, CLOBs, or OUT parameters. These methods are applicable to any LOBs whose lengths are smaller than 2,147,483,648. Note

The getString and getNString methods cannot be used to retrieve the column values of BLOBs.

The data API accesses the LOB locators in the driver to perform operations without programming. You can enable LOB prefetch as needed to reduce or eliminate other database round trips.

You can read and write BLOB or CLOB data in the same streaming transmission mechanism as LONG RAW and LONG data. You can use the defineColumnType(nn,Types.LONGVARBINARY) or defineColumnType(nn,Types.LONGVARCH method on columns to read data 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.

The IN OUT CLOB parameter of a stored procedure is expected to be set by using the setString method. The same types of IN and OUT parameters must be bound. 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 larger than 32,766 bytes, you can use the setString method for input parameters, and register OUT parameters as Types.VARCHAR and use the getString method for output parameters.

A better solution is to modify the stored procedure to have separate IN and OUT parameters. Example (original):

CREATE PROCEDURE clob_obproc( cc IN OUT CLOB );

Example (modified):

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

Another solution is to use container blocks for calls. The clob_plproc stored procedure can be wrapped with Java strings 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 limit

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 API.