Work with Net.Data Tables in RPG

By Craig Pelkie

Overview: One of the features that makes Net.Data easy to work with is that it automatically creates and processes a table (result set) when you run an SQL SELECT statement. In some cases, it may be difficult to get and format the data you want using SQL. Using a Net.Data table variable, you can fill the table in your RPG program with data and return it for processing to your Net.Data macro.

If you’re an AS/400 programmer working with Net.Data, chances are you also know and use RPG or COBOL. You’re probably aware that you can directly call an RPG or COBOL program from Net.Data, passing it Net.Data variables and getting results back in a parameter list. The parameter passing technique works well for relatively simple functions, but is not that good an option when you need to pass a lot of data back to the Net.Data macro.

Using a set of Net.Data functions that are available in an AS/400 service program, you can access a Net.Data table variable in an RPG or COBOL program. You can use functions to set the number of rows and columns in the table, set or alter column headings, set or alter values in each table cell, and determine the characteristics of the table. After formatting the table with the required data, you can return the table variable to the Net.Data macro, which can then process the table using Net.Data table manipulation functions.

Why use this technique?

It is probably advantageous to code your entire Web application in Net.Data if you can, so that you don’t have to maintain both the Net.Data macro and the called program. However, there are some cases where it might be more convenient to have a program work with and return a table to Net.Data:

· The business logic you need to get data from your database can be more easily expressed in native file operations, rather than SQL in Net.Data. This is particularly the case where a row of data is made up of values from several files, or the row values are the result of calculations on data from the files.

· You want to perform edit-word type editing on row values, above and beyond the formatting provided by Net.Data. Although you can use substring operations in Net.Data to patch together edited fields, that technique is very cumbersome compared to the simplicity of using an ILE RPG built-in function such as %editw (edit a field using an edit word).

· Your data is most easily represented in an array. Net.Data does not have an array construct, although you can use Net.Data string and word functions to emulate an array. If you use a table, you can represent array data in a row or column of the array. The table structure gives you a two-dimensional array construct to work with.

Conceptual Overview

In my experiments, I found that I needed to define the table in Net.Data before calling an ILE RPG program to work with the table. Because the Net.Data macro calls the RPG program, Net.Data is considered to “own” the memory used by the table. After defining the table variable, I can call the RPG program and pass the table variable to the program as an input/output parameter.

Once in the RPG program, I can use any of the 16 Net.Data Language Environment Interface (LEI) function calls. The functions are provided in AS/400 service program (object type *SRVPGM) QHTTPSVR/QTMJLE. Table 1 is a list of the function names and a description of the functions.

Function	Description
dtw_table_AppendRow	Add one or more rows to the end of the table.
dtw_table_Cols	Return current number of columns in the table.
dtw_table_Delete	Delete the table.
dtw_table_DeleteCol	Delete one or more columns beginning at the specified column.
dtw_table_DeleteRow	Delete one or more rows beginning at the specified row.
dtw_table_GetN	Retrieve a column heading.
dtw_table_GetV	Retrieve a value from the table.
dtw_table_InsertCol	Insert one or more columns after the specified column.
dtw_table_InsertRow	Insert one or more rows after the specified row.
dtw_table_MaxRows	Return maximum number of rows allowed in the table.
dtw_table_New	Create a new table.
dtw_table_QueryColnoNj	Return column number associated with a column heading.
dtw_table_Rows	Return current number of rows in the table.
dtw_table_SetCols	Set number of columns for a table.
dtw_table_SetN	Assign a name to a column heading.
dtw_table_SetV	Assign a value in a table.

Table 1: Functions provided in service program QHTTPSVR/QTMJLE.

When the RPG program is called, the table passed to it is “formless”. There are no rows, columns, column headers or values in the table. Before I can put anything into the table, I set the number of columns in the table and add a row to the table. I then set the column heading values and put values into individual table cells by specifying the value, the row and the column.

All values that are put into table cells must be null-terminated character strings. If you have numeric fields that you want in the table, you must convert them to a null-terminated character string.

Upon returning from the program, the formatted table is available to the Net.Data macro. At that point, you can let Net.Data display the table automatically by using default report processing, or use the Net.Data table functions to work with the table in the Net.Data macro.

The Table Example

Figure 1 shows a sample of the Net.Data macro in the browser. The macro displays the 1-row table that was created and filled in the RPG program. The Net.Data macro is called conventionally; there is nothing special you need to do in terms of HTTP server configuration or the URL to invoke a macro that calls an RPG program.

Figure 1: The sample table returned from the RPG program.

%{-------------------------------------------------------------%}

%{ TBTEST.ndm -- Net.Data table processing. Call RPG program %}

%{ to fill in values for table. %}

%{ %}

%{-------------------------------------------------------------%}

%{ Section A - Define section for the macro %}

%{-------------------------------------------------------------%}

%define {

DTW_HTML_TABLE = "YES"

tbtest = %table

%include "ErrorMsg_Block.ndm"

%{-------------------------------------------------------------%}

%{ Section B - GETTABLE - get data for Net.Data table %}

%{-------------------------------------------------------------%}

%function(DTW_DIRECTCALL) GETTABLE(inout dtwtable tbtest) {

%EXEC { /QSYS.LIB/NETDATA.LIB/TBTEST.PGM %}

%{-------------------------------------------------------------%}

%{ Section C - INPUT - initial section called %}

%{-------------------------------------------------------------%}

%html(INPUT) {

<html>

<head>

<title>Net.Data macro TBTEST.ndm</title>

</head>

<body>

<h1>Net.Data macro TBTEST.ndm6</h1>

@GETTABLE(tbtest)

</center>

</body>

</html>

Net.Data macro tbtest.ndm shown above contains three sections of code:

Section A – Define section

The code in this section indicates that the default report is to be displayed using HTML table tags, and that the variable named “tbtest” is a Net.Data table variable.

Section B – Call the RPG program

This section uses the DTW_DIRECTCALL language environment to call RPG program TBTEST. The name of this section is GETTABLE, and it is invoked in Section C (the INPUT section). The table variable tbtest is passed to the called program as an input/output parameter of type dtwtable. It is important that the parameter type be specified as dtwtable so that Net.Data will properly pass the table variable to and receive the table variable from the called program.

You can pass up to 50 parameters to a program from a Net.Data macro. You can mix parameter types as required; for example, you may want to pass additional parameters from the Net.Data macro along with the Net.Data table variable. You can also pass multiple Net.Data table variables if necessary.

Section C – The INPUT section

This is the initial section of the macro called from the invoking URL (see the Address entry in the browser, Figure 1). Because this is defined as an %html section, Net.Data emits the HTML inside this section and returns it to the browser. The @GETTABLE(tbtest) line of code invokes the GETTABLE section, passing it the name of the Net.Data table variable.

RPG program TBTEST

*************************************************************

* TBTEST - sample RPGLE program showing the following:

* - receive Net.Data table variable as input parameter

* - add 1 row, 3 columns to table variable

* - assign column headers to the 3 rows

* - put edited values into the row

* - return table variable to Net.Data

* Program compile commands:

* crtrpgmod module(netdata/tbtest)

* srcfile(netdata/qrpglesrc)

* crtpgm pgm(netdata/tbtest)

* bndsrvpgm(qhttpsvr/qtmjle)

*************************************************************

* craig@web400.com

*************************************************************

D*************************************************************

D* dtw_table_AppendRow - add one or more rows to end of table

D*************************************************************

Ddtw_table_AppendRow...

D pr 10I 0 extproc('dtw_table_AppendRow')

D table *

D rows 10I 0 value

D*************************************************************

D* dtw_table_SetCols - set number of columns for a table

D*************************************************************

Ddtw_table_SetCols...

D pr 10I 0 extproc('dtw_table_SetCols')

D table *

D cols 10I 0 value

D*************************************************************

D* dtw_table_SetN - assign a name to a column heading

D*************************************************************

Ddtw_table_SetN...

D pr 10I 0 extproc('dtw_table_SetN')

D table *

D src * value options(*string)

D col 10I 0 value

D*************************************************************

D* dtw_table_SetV - assign a value in a table

D*************************************************************

Ddtw_table_SetV...

D pr 10I 0 extproc('dtw_table_SetV')

D table *

D src * value options(*string)

D row 10I 0 value

D col 10I 0 value

D*************************************************************

D* SetTable - set a table column heading or table value

D*************************************************************

DSetTable...

D pr

D table *

D value 256 value varying

D type 1 value

D col 10I 0 value

D row 10I 0 value options(*nopass)

D*************************************************************

D* Work fields and Constants for testing

D*************************************************************

D amtField s 11

D charField s 20

D dateField s 8

D rc s 10I 0

D table s *

D COLHDG1 c 'Amount Field'

D COLHDG2 c 'Character Field'

D COLHDG3 c 'Date Field'

*************************************************************

* Receive table variable from Net.Data macro

*************************************************************

C *entry plist

C parm table

*************************************************************

* Create a 1 x 3 (rows x columns) table

*************************************************************

C eval rc = dtw_table_SetCols(table : 3)

C eval rc = dtw_table_AppendRow(table : 1)

*************************************************************

* Set column headings

*************************************************************

C callp SetTable(table : COLHDG1: 'N' : 1)

C callp SetTable(table : COLHDG2: 'N' : 2)

C callp SetTable(table : COLHDG3: 'N' : 3)

*************************************************************

* Set values for each column

*************************************************************

C eval amtField = %editw(12345.67 : '$ , 0. ')

C eval charField = 'Test Net.Data tables'

C eval dateField = %editc(udate: 'Y')

C callp SetTable(table : amtField : 'V' : 1 : 1)

C callp SetTable(table : charField : 'V' : 2 : 1)

C callp SetTable(table : dateField : 'V' : 3 : 1)

C eval *inlr = *on

C*************************************************************

C* SetTable - set a table column heading or table value

C* table - Net.Data table to work with

C* value - column heading or column value

C* type - type of function call

C* N = dtw_table_SetN

C* V = dtw_table_SetV

C* col - column number to work with

C* row - row number to work with

C*************************************************************

PSetTable b

D pi

D table *

D value 256 value varying

D type 1 value

D col 10I 0 value

D row 10I 0 value

D options(*nopass)

D n s 5 0

D ptr s *

D rc s 10I 0

C eval n = %len(value) + 1

C alloc n ptr

C eval %str(ptr : n) = value

C if type = 'N'

C eval rc = dtw_table_setN(table : ptr : col)

C endif

C if type = 'V'

C eval rc = dtw_table_setV(table : ptr : row : col)

C endif

C dealloc ptr

PSetTable e

RPG program TBTEST shown above is called from the Net.Data macro.

Function prototypes

The first four sets of D-specs are used to define function prototypes for the Net.Data Language Environment Interface functions used in the program. These functions are documented by IBM in the IBM Net.Data Language Environment Interface Reference manual, which is available for on-line viewing or download at http://www.iseries.ibm.com/netdata in the documentation section. The documentation shows the C programming language definitions.

Each of the functions used in the program returns an integer value, which is defined in RPG as an “I” type field of length 10. Each function also uses the Net.Data table variable that was passed to the RPG program on the call from the Net.Data macro. The table is defined as a pointer. The pointer points to the area of memory that is “owned” by Net.Data and associated with the name of the table variable. Numeric parameters used in the functions are also integers.

Following the function prototypes, there is an additional function prototype for the SetTable program defined function (described below) and D-specs for data fields used in the program.

The *ENTRY PLIST

The *ENTRY PLIST that opens the program defines the RPG variable “table”, which was defined as a pointer variable. By accepting this variable into the program, the RPG program now has access to the Net.Data table.

Set columns, append row

The first two eval statements in the program are used to set the number of columns in the table and to append a row to the end of the table. The functions dtw_table_SetCols and dtw_table_AppendRow each take the pointer variable “table” as their first parameter, followed by an integer value. The number of columns in the table is set to three. The number of rows appended to the table is one.

Note that the column headings row does not count as one of the data rows. The table is assumed to have a “row” that is used for column headings, which is distinct from rows used to contain data values.

Set column headings

The next section of code is used to set the column heading values. The SetTable program defined function is called to set the column heading values, using the values defined as named constants (COLHDG1).

Set values for each column

In this section, I first set the values for the work fields I am using in the program. I use editing functions to prepare the values, then call the SetTable function to insert the values into the table.

The SetTable Function

Because the two Net.Data LEI functions dtw_table_SetN and dtw_table_SetV require similar processing, I created the SetTable function. The parameters are described in the program comments.

Both of the LEI functions require a null-terminated character string to insert as either a column heading or table cell value. The null-terminated character string itself is not passed to the LEI functions, instead, a pointer to the null-terminated character string is used. To create the null terminated string and the pointer, I first had to calculate the length of the value passed to the SetTable function (either a column heading or a cell value) and add one to that length. The extra byte is used for the null character.

After determining the length, I use the RPG ALLOC operation code to allocate length number of bytes of storage. The storage is pointed to by a pointer variable. After allocating the memory, I use the ILE RPG %str built-in function to set the value of the allocated memory to the null-terminated character string. At this point, I have satisfied the LEI function requirements of having a null-terminated character string and a pointer that points to the value.

I then call either the dtw_table_SetN or dtw_table_SetV functions, depending upon the value of the type parameter passed to the SetTable function. The dtw_table_SetN function takes as its parameters the Net.Data table (pointer), the pointer to the column heading null-terminated character string, and the column number that the heading should appear in. The dtw_table_SetV function takes the same parameters, with the addition of a row parameter to indicate which row the table cell is in.

Following the function calls, I use the dealloc operation to deallocate the work memory that is pointed to by the pointer variable.

Summary

You’ve now seen a sample of how you can extend the power of Net.Data by using calls to your own programs. By using the techniques shown to pass a Net.Data table variable to your program, assign column headings and data values, and return the table to Net.Data, you can have complete control over the content of each table cell.

Craig Pelkie is an independent consultant based in Southern California. He frequently speaks at AS/400 seminars and user group meetings, and provides consulting services for companies using Net.Data, WebSphere, and Microsoft Active Server Pages. Craig can be contacted at craig@web400.com or at his Web site at http://www.web400.com, where you can also download a complete set of Net.Data LEI function prototypes for ILE RPG.