Modifying AS400 Databases Character Huge Objects From a 64K RPG Variable

0
134
Spread the love
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  


With most of us AS400 builders functioning with enormous volumes of XML facts in our interfaces in between apps on our AS400 devices and among other methods, or with info coming from interfaces to net companies running on other platforms or techniques, we hit the dilemma of getting a way to store this info in our db2/400 databases. As of now, DB2/400 isn’t going to make it possible for us to store or manipulate XML files natively in the database (even though this is earmarked for i5/os V7R1).

This is a simple situation to clear up when we are accessing the database from java utilizing jdbc and can manipulate CLOBS, but if we are employing RPG to access the databases, we are limited in our accessibility to the AS400 databases and CLOBS. Yet another issue is the document length on a db2 file is limited to just under 32K.

We can retail outlet massive quantities of knowledge in the database, in the kind of LOBs(Huge Objects). The sort we can use for our XML storage uses is the CLOB, which can maintain character knowledge including XML.

We can use embedded SQL in our RPG plans to put our XML knowledge into a databases clob fields, but we have some difficulties. We can store up to 2Gb of details in a CLOB. RPG has a subject dimension greatest of 64K on V5R4 (16M on V6R1), but there is not an easy of receiving our 64K of knowledge in an RPG industry into a databases CLOB.

The instance beneath, dependent on a single from Scott Klement, demonstrates how you can get details from your AS400 RPG field into the CLOB.

The X/Open up SQL Contact Degree Interface is a conventional for direct entry the a devices SQL Engine without having a precompile, we will use this by calling the SQL CLI apis from RPG instead than employing embedded SQL. It allows accessibility to SQL capabilities directly through treatment calls to a service application presented by DB2 on the AS400. Applying the SQL Call Amount Interface procedure phone calls lets you to prepare SQL statements, execute SQL statements, fetch rows of data, and even do highly developed capabilities these types of as accessing the catalogs, and binding plan variables to output columns.

So utilizing the CLI, we can bypass the limitation of AS400 embedded SQL, and move our 64K of information as a host variable using a pointer. An illustration that exhibits how to update a CLOB utilizing this technique is revealed underneath:

/incorporate *libl/qtxtsrc,MGSQLCLI_H

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

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

xmsg = msg

// Produce an SQL setting & relationship manage
SQLAllocEnv(env)
SQLSetEnvAttrI(env:SQL_ATTR_OUTPUT_NTS:
SQL_Fake:%sizing(SQLINTEGER) )
SQLSetEnvAttrI(env: SQL_ATTR_ENVHNDL_COUNTER
:SQL_Accurate: %dimension(SQLINTEGER) )
SQLAllocConnect(env: conn)

// Established Naming Structure
SQLSetConnectAttrI(conn: SQL_ATTR_DBC_SYS_NAMING
:SQL_Real: %size(SQLINTEGER) )

// Set Commitment Stage to *CHG
SQLSetConnectAttrI(conn: SQL_ATTR_Dedicate
:SQL_Dedicate_CHG: %size(SQLINTEGER) )

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

// Generate an SQL statement
SQLAllocStmt(conn: stmt )
rc = SQLPrepare(stmt
: ‘UPDATE FILE Set mymsg =? Exactly 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
::ptr:msgLen)

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

// Execute Statement
rc = SQLExecute(stmt)

// Free up SQL Means
SQLFreeStmt( stmt: SQL_Fall )

cleanup()
return rc

/Finish-Cost-free
p E
*+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
* Cleanup(): Deallocate/Disconnect SQL CLI handles
*+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
P cleanup B
D cleanup pi
/free of charge
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
/Close-Free
p E

So working with the example higher than it should really be pretty straight forward for a developer to updating an AS400 db2 CLOB from a 64K RPG Variable utilizing the SQL Phone Degree Interface.

Source by David A Banks

Don't miss out!
Subscribe To Newsletter

Receive top technical news, lesson ideas, travel tips and more!

Invalid email address
Give it a try. You can unsubscribe at any time.

Spread the love
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  

LEAVE A REPLY

Please enter your comment!
Please enter your name here