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

0
72
Spread the love
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  


With most of us AS400 builders operating with substantial volumes of XML information in our interfaces amongst purposes on our AS400 units and concerning other units, or with facts coming from interfaces to net providers managing on other platforms or techniques, we strike the challenge of locating a way to store this data in our db2/400 databases. As of now, DB2/400 will not allow for us to retailer or manipulate XML documents natively in the databases (whilst this is earmarked for i5/os V7R1).

This is a very simple situation to address when we are accessing the databases from java utilizing jdbc and can manipulate CLOBS, but if we are employing RPG to accessibility the databases, we are restricted in our obtain to the AS400 databases and CLOBS. A different concern is the file size on a db2 file is restricted to just below 32K.

We can shop substantial amounts of facts in the databases, in the type of LOBs(Large Objects). The type we can use for our XML storage reasons is the CLOB, which can hold character info which includes XML.

We can use embedded SQL in our RPG plans to put our XML data into a database clob fields, but we have some complications. We can shop up to 2Gb of data in a CLOB. RPG has a subject sizing most of 64K on V5R4 (16M on V6R1), but there is not an simple of finding our 64K of info in an RPG industry into a database CLOB.

The instance underneath, primarily based on a single from Scott Klement, reveals how you can get data from your AS400 RPG area into the CLOB.

The X/Open SQL Get in touch with Amount Interface is a normal for immediate access the a methods SQL Motor without the need of a precompile, we will use this by contacting the SQL CLI apis from RPG fairly than using embedded SQL. It will allow entry to SQL functions instantly by process phone calls to a provider application furnished by DB2 on the AS400. Using the SQL Connect with Amount Interface process phone calls permits you to get ready SQL statements, execute SQL statements, fetch rows of information, and even do state-of-the-art capabilities these as accessing the catalogs, and binding system variables to output columns.

So using the CLI, we can bypass the limitation of AS400 embedded SQL, and go our 64K of details as a host variable using a pointer. An instance that displays how to update a CLOB applying this process is revealed down below:

/contain *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

// Develop an SQL setting & link manage
SQLAllocEnv(env)
SQLSetEnvAttrI(env:SQL_ATTR_OUTPUT_NTS:
SQL_Wrong:%size(SQLINTEGER) )
SQLSetEnvAttrI(env: SQL_ATTR_ENVHNDL_COUNTER
:SQL_Genuine: %sizing(SQLINTEGER) )
SQLAllocConnect(env: conn)

// Established Naming Format
SQLSetConnectAttrI(conn: SQL_ATTR_DBC_SYS_NAMING
:SQL_Accurate: %measurement(SQLINTEGER) )

// Set Determination Level to *CHG
SQLSetConnectAttrI(conn: SQL_ATTR_Dedicate
:SQL_Commit_CHG: %dimensions(SQLINTEGER) )

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

// Make an SQL assertion
SQLAllocStmt(conn: stmt )
rc = SQLPrepare(stmt
: ‘UPDATE FILE Established mymsg =? In which myid =?’
: SQL_NTS )

// Assign assertion parameters
ptr = %addr(xmsg)
msgLen = %len(xmsg)
msgSize = %measurement(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 Assertion
rc = SQLExecute(stmt)

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

cleanup()
return rc

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

So working with the illustration earlier mentioned it should really be rather straight ahead for a developer to updating an AS400 db2 CLOB from a 64K RPG Variable making use of the SQL Simply call Level Interface.

Source by David A Banking institutions

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