Updating an as400 db2 CLOB with a 64K RPG Variable

With most of us working with large amounts of XML data in our interfaces between applications on our as400 systems or with XML data coming from web services running on other systems, we run up against the issue of how to get this data into the database.

This is a fairly straightforward issue to deal with if we are working with java, but if we are working with good old RPG, how do we our XML data into the as400 database. Especially as the record length on a db2 file is restricted to 32K.

We do have a facility for storing large amounts of data in the database, in the form of LOBs(Large Objects). The type we are specifically interested in is the CLOB, as we can store character data such as XML in there.

In theory we should be able to use embedded SQL in our RPG programs to put our XML data into a database clob fields. We can store up to 2Gb of data in our CLOB. Unfortunately we have another issue. Although RPG has a field size maximum of 64K on V5R4 (16M on V6R1 I believe), we have no simple way of getting our 64K of data in out RPG field into our CLOB.

Looking around online there were plenty of examples of how to read data in from a CLOB, and how to save CLOB data to an IFS file. But there was only one example, from Scott Klement, that showed how you could get data from your as400 RPG field into the CLOB.

The way this is achieved is to bypass embedded SQL and use the SQL Call Level Interface directly. The X/Open SQL Call Level Interface is a standard for direct access the a systems SQL Engine without a precompile. It allows access to SQL functions directly through procedure calls to a service program provided by DB2 on the as400. Using the SQL Call Level Interface procedure calls allows you to prepare SQL statements, execute SQL statements, fetch rows of data, and even do advanced functions such as accessing the catalogs, and binding program variables to output columns.

So using the CLI, we can bypass the limitation of embedded SQL, and pass our 64K of data as a host variable using a pointer. An example that shows how to update a CLOB using this method is shown below

/include *libl/qtxtsrc,MGSQLCLI_H

************************************************************************
* updateClob - Update Clob
************************************************************************
P updateClob B Export
D updateClob pi 10i 0
d Id 10i 0 const
d msg 65535A const

D id s 10s 0
D ptr s *
D rc s 10i 0
D msgLen s 10i 0
D msgSize s 10i 0
D env s like(SQLHENV) inz(SQL_NULL_HENV)
d xmsg s 65535A
/Free

xmsg = msg;

// Create an SQL environment & connection handle
SQLAllocEnv(env);
SQLSetEnvAttrI(env:SQL_ATTR_OUTPUT_NTS:
SQL_FALSE:%size(SQLINTEGER) );
SQLSetEnvAttrI(env: SQL_ATTR_ENVHNDL_COUNTER
:SQL_TRUE: %size(SQLINTEGER) );
SQLAllocConnect(env: conn);

// Set Naming Format
SQLSetConnectAttrI(conn: SQL_ATTR_DBC_SYS_NAMING
:SQL_TRUE: %size(SQLINTEGER) );

// Set Commitment Level to *CHG
SQLSetConnectAttrI(conn: SQL_ATTR_COMMIT
:SQL_COMMIT_CHG: %size(SQLINTEGER) );

// Connect to Database
SQLConnect(conn:'*LOCAL':SQL_NTS:*NULL
:SQL_NTS:*NULL:SQL_NTS );

// Create an SQL statement
SQLAllocStmt(conn: stmt );
rc = SQLPrepare(stmt
: 'UPDATE FILE SET mymsg = ? WHERE myid = ?'
: SQL_NTS );

// Assign statement parameters
ptr = %addr(xmsg);
msgLen = %len(xmsg);
msgSize = %size(xmsg);
rc = SQLBindParam(stmt:1:SQL_CLOB:SQL_CLOB:msgSize
:0:ptr:msgLen);

rc = SQLBindParam(stmt:2:SQL_NUMERIC:SQL_NUMERIC
:%len(id):%decpos(id): %addr(id):0);

// Execute Statement
rc = SQLExecute(stmt);

// Free up SQL Resources
SQLFreeStmt( stmt : SQL_DROP );

cleanup();
return rc;

/End-Free
p E
*+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
* Cleanup(): Deallocate/Disconnect SQL CLI handles
*+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
P cleanup B
D cleanup pi
/free
if (stmt <> SQL_NULL_HSTMT);
SQLFreeStmt( stmt : SQL_DROP );
stmt = SQL_NULL_HSTMT;
endif;

if (conn <> SQL_NULL_HDBC);
SQLDisconnect( conn );
SQLFreeConnect( conn );
conn = SQL_NULL_HDBC;
endif;
/End-Free
p E

Updating an as400 db2 CLOB with a 64K RPG Variable