Passing an Entire Result Set as a Parameter, Part 2
October 21, 2009 Michael Sansoterra
Note: The code accompanying this article is available for download here.
In Part 1 of this series, I discussed a generic method of passing a delimited set of text records in CSV (comma separated variable) format into a single stored procedure variable. Once the stored procedure had this text data, a generic Java user-defined table function (UDTF) was used to parse the text data and convert it to a row set that could be by any number of standard DB2 for i statements, such as a SELECT or INSERT. So with a little sweat, it is indeed possible to send multiple rows of data to a stored procedure for processing. Don’t forget, this discussion of passing result sets is done in the context of client/server programming.
While adequate, this generic Java based approach suffered from a few problems:
On the plus side, this Java UDTF could handle the majority of scenarios dealing with CSV data without changing any of the underlying code. The same cannot be said for the RPG/XML technique I will demonstrate in this tip. However, the good news is that the major performance and data typing problems mentioned above do not apply with this technique.
This tip will require the i/OS level to be at V5R4 or higher because it uses RPG’s XML-INTO operation code. (For users stuck on a back-leveled OS, I did implement this technique many years back on a V5R2 system using the 5733XT1 XML Toolkit. However, it’s a lot more work than using the native RPG XML support.) This tip also requires the 5722ST1 SQL Development Kit to be installed because we will be using a little embedded SQL.
The objective in this tip is still the same: we want to pass an entire set of rows in a single parameter to a stored procedure where the parameter is converted into a set of rows that can be used by DB2 for i SQL statements. This time, instead of using the CSV text format we’ll use XML. For the sake of illustration, we’ll stick with implementing the solution using an RPG UTDF, although an RPG program using this same technique could also serve just as well as a standalone stored procedure with the advantage of better performance.
In the first tip, the sample data had three pieces of information: GL Account Number, Debit value, and Credit value. We can represent the same information as an XML definition as follows:
<JrnlLines> <JrnlLine> <Account>100020</Account> <Debit>0</Debit> <Credit>1007.95</Credit> </JrnlLine> <JrnlLine> <Account>100030</Account> <Debit>1007.95</Debit> <Credit>0</Credit> </JrnlLine> </JrnlLines>
Assuming the client-side program can package XML data in this manner, it’s a snap to have RPG read the XML into a data structure array. With the XML-INTO op-code, the XML text is shredded and put into a data structure array with one C-Spec!
RPG program GETJRNDTA acts as a UDTF to transform an XML fragment into a result set with these steps:
Once the RPG program GETJRNDTA is compiled, it must be registered for use with SQL using the CREATE FUNCTION statement as follows:
Create Function xxxxx/GETJRNDTA (XMLData Clob(2M) As Locator) /* Use Clob(64K) for V5R4 */ Returns Table (Account Char(20), Debit Dec(9,2), Credit Dec(9,2)) External Language RPGLE Disallow Parallel Modifies SQL Data Parameter Style DB2SQL Not Fenced
In case you’re not familiar with UDTF functions, they are simply a special program that gathers data in a tabular (row and column) format for use with DB2. In this case, RPG table function GetJrnDta will take the above XML fragment and convert it to a set of rows:
Here’s a sample of how the RPG program would be invoked by DB2 as a table function in a query:
Select * From Table(GetJrnDta(' <JrnlLines> <JrnlLine> <Account>100020</Account> <Debit>0</Debit> <Credit>1007.95</Credit> </JrnlLine> <JrnlLine> <Account>100030</Account> <Debit>1007.95</Debit> <Credit>0</Credit> </JrnlLine> </JrnlLines>')) JournalEntries
Of course, in a client/server environment the XML data would be passed as a parameter to the table function or to a stored procedure that invoked the table function instead of having hard-coded text as illustrated above.
Here is a simple stored procedure that accepts the XML data as a CLOB parameter and uses the GetJrnDta UDTF to convert the XML data into rows:
Create Procedure xxxxx/ProcessJournalEntry (XMLData In CLOB(2M)) Language SQL Set Option Commit=*None, UsrPrf=*Owner, DatFmt=*ISO Begin Declare Debits Dec(11,2) Not Null Default 0; Declare Credits Dec(11,2) Not Null Default 0; Select Sum(Debit),Sum(Credit) Into Debits, Credits From Table(GetJrnDta(XMLData)) BalanceCheck; If Debits<>Credits Then Signal SQLSTATE '38I01' Set Message_Text='Debits Must Equal Credits'; End If; - - Do more stuff here... Insert Into QTEMP.JournalEntry Select * From Table(GetJrnDta(XMLData)) JournalData; End; '
Before I end, there are a few things to note about the RPG function:
Finally, passing XML data in a CLOB variable is an excellent way to pass and process a set of rows in a stored procedure. Thanks to the XML-INTO operation code, RPG is a great language choice for easily parsing XML into a useable form. Any errors encountered while parsing the XML data can be handled by the RPG program. Further, when creating a table function, using XML and RPG allows the data to be returned to DB2 as a strongly typed result set.
Michael Sansoterra is a programmer/analyst for i3 Business Solutions, an IT services firm based in Grand Rapids, Michigan. Send your questions or comments for Mike to Ted Holt via the IT Jungle Contact page.