Friday, February 26, 2010

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

Thursday, February 11, 2010

Proactively Manage your Critical System Messages and Resources on the System i

Many IT pros struggle to effectively monitor and manage their critical system messages and resources on System i. This webcast provides tips for improving your System i management and explores how you can automate actions to instantly resolve problems across a single system or a multi-system environment. Learn how to monitor your System i from any location via multiple interfaces, including green screen, PC GUI, web browser and handheld device


http://event.on24.com/eventRegistration/EventLobbyServlet?target=lobby.jsp&playerwidth=950&playerheight=680&totalwidth=800&align=left&eventid=190661&sessionid=1&partnerref=bizcard&key=4B3EFC29235408022AFAD0E2114931BB&eventuserid=32990463

Saturday, February 06, 2010

Create PDF on iseries spooled files using Zend_PDF and i5 Toolkit for PHP

Zend_Pdf is a PDF manipulation framework for PHP available on iseries via the Zend Core. It can help any PHP application dynamically create or modify PDF documents.

Zend_Pdf offers the following features:
Create or load documents
Manipulate pages within a document
Drawing lines, rectangles, polygons, circles, ellipses and sectors, images and rotations
Use of the 14 built-in fonts or TrueType fonts.


include the Zend.pdf framework in your PHP code:
require_once 'Zend/Pdf.php'

Create a PDF class, an instance of the Zend_Pdf object:

$pdf = new Zend_Pdf();

A document can be loaded from a file using the instance:
$pdf = Zend_PDF::load($filename);

Changes to the PDF document can be saved:
$pdf->save($filename, true);

Zend Core for i5/OS includes the i5 Toolkit for PHP. This gives access to i5 function from PHP. i5_spool allows administration of spool files. 


Combining this with Zend_PDF will give iseries and i5/os users free coversion of spooled files to PDFs