Modifying AS400 Database Character Large Objects From a 64K RPG Variable from jenny hanson's blog

With most of us AS400 developers working with huge volumes of XML data in our interfaces between applications on our AS400 systems and between other systems, or with data coming from interfaces to web services running on other platforms or systems, we hit the problem of finding a way to store this data in our db2/400 database. As of now, DB2/400 doesn't allow us to store or manipulate XML documents natively in the database (although this is earmarked for i5/os V7R1).

 

This is a simple issue to solve when we are accessing the database from java using jdbc and can manipulate CLOBS, but if we are using RPG to access the database, we are restricted in our access to the AS400 database and CLOBS. Another issue is the record length on a db2 file is restricted to just under 32K.

 

We can store large amounts of data in the database, in the form of LOBs(Large Objects). The type we can use for our XML storage purposes is the CLOB, which can hold character data including XML.

 

We can use embedded SQL in our RPG programs to put our XML data into a database clob fields, but we have some difficulties. We can store up to 2Gb of data in a CLOB. RPG has a field size maximum of 64K on V5R4 (16M on V6R1), but there is not an easy of getting our 64K of data in an RPG field into a database CLOB.

 

The example below, based on one from Scott Klement, shows how you can get data from your AS400 RPG field into the CLOB.

 

The X/Open SQL Call Level Interface is a standard for direct access the a systems SQL Engine without a precompile, we will use this by calling the SQL CLI apis from RPG rather than using embedded SQL. 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 AS400 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

 

So using the example above it should be fairly straight forward for a developer to updating an AS400 db2 CLOB from a 64K RPG Variable using the SQL Call Level Interface.