work in progress... last modified 08-Jan-2007 14:15 pacific.

Introduction.

Large Object data types, BLOB (Binary Large Object) and CLOB (Character Large Object) were added to the 1999 version of SQL (a.k.a SQL3, SQL-1999, or SQL:99). This was in part related to adding object/relational features to SQL.

A BLOB can be an image, audio, video, a Java Object, or any other binary data. A CLOB can be an xml document, a manual, or any other large body of text.

Support of BLOB and CLOB data types is supported in SQL/MX Tables. This fact is not mentioned in the SQL Reference manual, not even to point out that the documentation is in the JDBCMx driver programmer’s guide. The data types BLOB and CLOB do not appear in any of the syntax productions.

Creating tables that contain BLOB and CLOB data types is usually done using mxci. However, to insert or retireve BLOB or CLOB data, Tandem relies exclusively on the existing BLOB/CLOB apis provided by Java JDBC and then only in the JdbcMx type-2 driver (local system driver). BLOB/CLOB apis were not included in the Tandem JdbcMx type-4 driver used by remote clients. There is no evidence that BLOB/CLOB work is, or ever will be, supported in any of the embedded SQL languages such as C/C++ or Cobol. Such languages must use a Java application as an agent, for instance a Pathway server class.

A BLOB or CLOB column in a table contains a LOCATOR value. Tandem documentation calls this a data-locator. This LOCATOR value is a reference to the BLOB or CLOB data. Database venders are free to use this “reference” value as best fits their architecture. Oracle uses it differently than DB2 and differently from MySQL and differently from Tandem SQL/MX. You must be careful when reading documentation from other venders to not extrapolate too far toward Tandem.

Tandem uses the data-locator value in a BLOB/CLOB column as a foreign key to a BLOB/CLOB table where it functions as part of a generic key that groups the chunks of data that compose the BLOB/CLOB.

The LOB table has the same structure for either BLOB or CLOB data but there are distinct parameters (jdbcmx.clobTableName and jdbcmx.blobTableName) to identify the nature of the encoding when brought into Java – byte or UTF-16. A LOB table can used with more than one base table and more than one BLOB/CLOB column.

Creating a table with a LOB column using mxci:

 control query default JDBC_PROCESS 'TRUE';
 set schema gemcat.images;
 create table eetable (
   eeId    int not null not droppable,
   eePhoto BLOB,
   primary key (eeId)
 ) location $data2;
 

The JdbcMxLobAdmin utility.

A java utility program has been provided in the driver’s jar file to help generate SQL DML statements for creating the BLOB/CLOB data table and for creating the trigger statements that maintain the integrity between the base table and the BLOB/CLOB table. The standard path to the jar file is

To use the utility the path to the jar file must be included in your CLASSPATH environment variable and the path to the library file must be included in your _RLD_LIB_PATH environment variable. The standard path to the library is

Here is an example of running it.

The SQL DML produced in the –out file. This file, Lobadmin.out, is obeyed in mxci.

Working with BLOB/CLOB.

Access to BLOB/CLOB data is through the jdbc api of Java. No provision was made to provide access methods for these SQL data types in any of the embedded SQL host languages like C/C++ or COBOL. This does not mean that those languages cannot be used, only that they must access BLOB/CLOB data through a Java application. That Java application could be a Pathway server class.

The java application that directly accesses BLOB/CLOB data requires one or more parameters, such as -Djdbcmx.blobTableName or -Djdbcmx.blobTableName (and maybe –Djdbcmx.schema and maybe –Djdbcmx.catalog depending on how terse the table references are in the java code and if you fully qualify the schema parameter). Unfortunetly the Jdbc type-2 driver, unlike the type-4 driver, does not provide a predefined parameter to specify a properties file and automatically pass the properties it finds there to the connection. The property that the type-4 driver implemented was

Inserting a row that contains a BLOB/CLOB column and inserting the BLOB/CLOB data is a two-step process. Those two-steps are implicit when using a PrepraedStatement, or explicit when using Statement.

The first step inserts the base table row data and initializes the BLOB/CLOB columns with a dispensed reference locator value (data-locator). The second step uses the locator value reference to insert BLOB/CLOB data into the BLOB/CLOB table. The locator value is a component of the BLOB/CLOB table’s composite primary key.

If using a PreparedStatement statement, autocommit (the dafault) is left on and both steps are done automatically within a TMF transaction. See Listing 1. The java command for it was

Listing 1.txt

If using a Statement, then an Insert statement is followed by a Select for update statement. Both of these statements must be within one TMF transaction so autocommit must be turned off and the transaction must be explicitly committed after the Select for update. See Listing 2 (insert base table row), and Listing 3 (update to add BLOB data).

Listing 2.txt

Listing 3.txt

To be continued...