Using Net.Data Table Functions

By Craig Pelkie

Synopsis: Although you can create functional Net.Data queries with just a few lines of code, there may be times where you need more control over the resulting HTML output. Using the Net.Data table functions, you can have complete control over HTML table rows and table column data. This article demonstrates the alternatives and describes some typical uses of the Net.Data table functions.

One of the first options you’ll probably explore when you start Web programming is Net.Data. Net.Data is provided with IBM HTTP Server for AS/400, which is a no-charge feature for the AS/400 system, so the entry price is right. Best of all, you can create simple Net.Data queries using just a few lines of code. For example, the code shown in Figure 1 is a small Net.Data macro that queries the QCUSTCDT database file. The resulting HTML table output, shown in Figure 2, is generated entirely by Net.Data. Because of this simplicity and power, I sometimes refer to Net.Data as “DFU for the Web.”

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

%{ Table1.ndm -- Net.Data table processing, example 1 %}

%{ Use Net.Data default table processing to format HTML. %}

%{ %}

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

%{ Define section for the macro %}

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

%define {

DATABASE = "*LOCAL"

DTW_HTML_TABLE = "YES"

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

%{ RUNSQL- run the SQL statement, generate default report %}

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

%function(DTW_SQL) RUNSQL() {

select * from qiws.qcustcdt order by cusnum

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

%{ INPUT - initial section called, calls RUNSQL macro function %}

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

%html(INPUT) {

<html>

<head>

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

</head>

<body>

<h1>Net.Data macro Table1.ndm</h1>

@RUNSQL()

</center>

</body>

</html>

Figure 1: Net.Data macro Table1.ndm.

Figure 2: The output of macro Table1.ndm.

To review, the Net.Data macro Table1.ndm shown in Figure 1 starts processing at the %html(INPUT) section (you can see the URL used to launch the macro in the address location in Figure 1). The INPUT section is mostly HTML code, with a call to the RUNSQL function, which is defined as a Net.Data DTW_SQL function type. That function includes the SQL SELECT statement. Because there are no additional options specified for working with the result set, Net.Data generates the default table output. The DTW_HTML_TABLE option in the %define section causes Net.Data to use HTML table tags, rather than the default <PRE> (preformatted) tag. If you run the macro, you should remove the DTW_HTML_TABLE tag to see how the table is displayed. You will undoubtedly remember to always include the DTW_HTML_TABLE tag after you see the alternative.

You can use the Table1.ndm macro as a ready-to-use template for any database file on your AS/400 system. By simply changing the SQL statement, you can quickly query any database file and use Net.Data to generate an HTML table.

Improving the Appearance

Although the Table1.ndm macro is good for “quick and dirty” applications and prototypes, chances are that you will want to create better looking Web applications. For example, Figure 3 shows the output of another version of the macro, Table2.ndm, with several visual enhancements:

The column headings now include descriptive wording, in addition to the field names from the database file.
The column headings are displayed using a selected background color.
The data rows are displayed using a different background color.
Some of the data elements in the columns are center or right aligned (CHGCOD, BALDUE, CDTDUE).
The font used for the headings and table data is smaller, which gives the table a more pleasing appearance.

Figure 3: Table2.ndm output is more refined.

The code for the Table2.ndm is shown in Figure 4. In the %define section, I have removed the DTW_HTML_TABLE statement, since I will be creating the HTML Table. The %html(INPUT) section is the same as the Table1.ndm macro, but the RUNSQL function is much more involved.

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

%{ Table2.ndm -- Net.Data table processing, example 2 %}

%{ Use %report block in SQL function for more control. %}

%{ %}

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

%{ Define section for the macro %}

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

%define {

DATABASE = "*LOCAL"

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

%{ RUNSQL- run the SQL statement, generate default report %}

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

%function(DTW_SQL) RUNSQL() {

select * from qiws.qcustcdt order by cusnum

%report{

<!---------------------------------------------------

Table headers

---------------------------------------------------->

<table border="0"

cellpadding="1"

bgcolor="lightcyan">

<th>$(N1 ) Customer Number</th>

<th>$(N3 ) Initials </th>

<th>$(N4 ) Street </th>

<th>$(N6 ) State </th>

<th>$(N8 ) Credit Limit </th>

<th>$(N9 ) Charge Code </th>

<th>$(N10) Balance Due </th>

<th>$(N11) Credit Due </th>

</tr>

<!---------------------------------------------------

Table rows

---------------------------------------------------->

%row{

<tr>

</tr>

</table>

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

%{ INPUT - initial section called, calls RUNSQL macro function %}

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

%html(INPUT) {

<html>

<head>

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

</head>

<body>

<h1>Net.Data macro Table2.ndm</h1>

@RUNSQL()

</center>

</body>

</html>

Figure 4: Net.Data macro Table2.ndm.

The RUNSQL function now includes a Net.Data %report block, immediately following the SQL statement itself. When Net.Data encounters a %report block, it assumes that you want to control the HTML generation, so it does not generate the default HTML table. There are two sections to a %report block:

A section to work with table headers.
A %row block section that is applied to each of the rows in the SQL result set.

The table headers section includes the HTML needed to define a table and the table row for the table headers. Within the table row, there are table header tags for each of the column headers. The Net.Data table variable $(Nx) (where “x” is a number from 1 to 11) is used to retrieve the name of the column, in position order as defined in the result set. The table header tag also includes an HTML break tag and the text of the column header.

The %row block is applied to each of the rows returned from the SQL statement. Note that you do not have to define a looping construct; Net.Data provides an automatic loop over each of the result set rows. The code within the %row block is applied to each of the result set rows.

Within the %row block, the <tr> tag defines the table row, with <td> tags for each of the table data elements. The value of each element is referenced by the Net.Data variable $(Vx), where “x” is a number from 1 to 11. An alternative to the $(Vx) table variable is $(V_name), where _name is the actual column name from the database. For example, you can refer to the value of the columns returned as $(V_cusnum), $(V_lstnam), etc. Although it is simpler to use the $(Vx) format, you might prefer to use the $(V_name) format, since it is more descriptive.

Although you have more control of the HTML table generation with the %report block technique, the processing for each row is the same. If you need to perform unique operations on each row, you need to use the next technique.

Complete Control

Figure 5 shows the output of the Table3.ndm macro. You can see that I have applied a different background color to each table row, which creates a more striking visual appearance. Figure 6 shows a further enhancement to the table, in which the customer number column is now used as a link to a customer detail Web page. To get this level of control, where you can apply specific formatting to individual rows and cells, you need to use Net.Data table functions.

Figure 5: Complete control in Table3.ndm.

Figure 6: The table now has a clickable link.

Figure 7 shows part of the code for the Table3.ndm macro that generated the output shown in Figure 5. There are many differences between this macro and the previous two macros:

The %define section omits the DTW_HTML_TABLE statement and includes two other statements.
The RUNSQL function now uses an output parameter.
A new %macro_function OUTPUT_TABLE is used to process the table that is returned from the SQL statement.
The %html(INPUT) section uses a revised function invocation for the RUNSQL function, and includes a function invocation for the OUTPUT_TABLE function.

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

%{ Table3.ndm -- Net.Data table processing, example 3 %}

%{ Use Net.Data table variables for complete control of table display. %}

%{ %}

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

%{ Define section for the macro %}

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

%define {

DATABASE = "*LOCAL"

DTW_DEFAULT_REPORT = "NO"

sqlTable = %TABLE

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

%{ RUNSQL - run the SQL statement, return %TABLE variable %}

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

%function(DTW_SQL) RUNSQL(OUT sqlTable) {

select * from qiws.qcustcdt order by cusnum

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

%{ OUTPUT_TABLE - macro function to work with %TABLE variable %}

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

%macro_function OUTPUT_TABLE (IN sqlTable) {

<!---------------------------------------------------

Table headers

---------------------------------------------------->

<table border="0"

cellpadding="1"

bgcolor="lightcyan">

<th>

@dtw_tb_rgetn(sqlTable, "1") Customer Number

</th>

<th>

@dtw_tb_rgetn(sqlTable, "2") Last Name

</th>

<th>

@dtw_tb_rgetn(sqlTable, "3") Initials

</th>

<th>

@dtw_tb_rgetn(sqlTable, "4") Street

</th>

<th>

@dtw_tb_rgetn(sqlTable, "5") City

</th>

<th>

@dtw_tb_rgetn(sqlTable, "6") State

</th>

<th>

@dtw_tb_rgetn(sqlTable, "7") Zip Code

</th>

<th>

@dtw_tb_rgetn(sqlTable, "8") Credit Limit

</th>

<th>

@dtw_tb_rgetn(sqlTable, "9") Charge Code

</th>

<th>

@dtw_tb_rgetn(sqlTable, "10") Balance Due

</th>

<th>

@dtw_tb_rgetn(sqlTable, "11") Credit Due

</th>

</tr>

<!---------------------------------------------------

Table rows

---------------------------------------------------->

@dtw_tb_rows(sqlTable, numRows)

@dtw_assign(rowIndex, "1")

@dtw_assign(color, "lightcyan")

%while (rowIndex <= numRows) {

@dtw_tb_rgetv(sqlTable, rowIndex, "1")

</td>

<td>

@dtw_tb_rgetv(sqlTable, rowIndex, "2")

</td>

<td>

@dtw_tb_rgetv(sqlTable, rowIndex, "3")

</td>

<td>

@dtw_tb_rgetv(sqlTable, rowIndex, "4")

</td>

<td>

@dtw_tb_rgetv(sqlTable, rowIndex, "5")

</td>

<td>

@dtw_tb_rgetv(sqlTable, rowIndex, "6")

</td>

<td>

@dtw_tb_rgetv(sqlTable, rowIndex, "7")

</td>

@dtw_tb_rgetv(sqlTable, rowIndex, "8")

</td>

@dtw_tb_rgetv(sqlTable, rowIndex, "9")

</td>

@dtw_tb_rgetv(sqlTable, rowIndex, "10")

</td>

@dtw_tb_rgetv(sqlTable, rowIndex, "11")

</td>

</tr>

%if (color == "lightcyan")

@dtw_assign(color, "silver")

%else

@dtw_assign(color, "lightcyan")

%endif

@dtw_add(rowIndex, "1", rowIndex)

</table>

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

%{ INPUT - initial section called, calls RUNSQL macro function %}

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

%html(INPUT) {

<html>

<head>

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

</head>

<body>

<h1>Net.Data macro Table3.ndm</h1>

@RUNSQL(sqlTable)

@OUTPUT_TABLE(sqlTable)

</center>

</body>

</html>

Figure 7: Net.Data macro Table3.ndm.

In the %define section, I added the DTW_DEFAULT_REPORT statement to indicate to Net.Data that it is not supposed to generate an HTML report. I need that statement because the RUNSQL section now contains just an SQL statement, as in the Table1.ndm macro. If I didn’t have the DTW_DEFAULT_REPORT option set to “no”, Net.Data would assume that it should generate a report after executing the SQL statement.

I also defined the variable sqlTable in the %define section as a Net.Data %table type variable. This is a special variable type that contains the result set of an SQL SELECT statement. I need this variable so that I can pass the result set from the RUNSQL function to my macro function OUTPUT_TABLE. (In RPG terms, the %table type variable is conceptually similar to a multiple occurrence data structure. Unlike the data structure, I don’t need to declare subfields or an upper limit; Net.Data automatically fills the sqlTable variable with records retrieved from the database file.)

Look now at the %html(INPUT) section. The RUNSQL function call includes the sqlTable parameter, which is defined in the RUNSQL function as an output parameter. When Net.Data executes the RUNSQL function, it runs the SELECT statement, then returns the sqlTable variable. The OUTPUT_TABLE function invocation in the INPUT section uses sqlTable as an input parameter, so I am able to pass the results of the SQL statement to another function.

Why is it necessary to pass the sqlTable variable from RUNSQL to OUTPUT_TABLE, rather than just code the statements for OUTPUT_TABLE in a %report block following the SQL statement? The reason is because some of the Net.Data functions that I want to use are not supported in a %report block. The functions that are not supported are those that let me have access to individual column values in a row. Because I want access to individual column values (cells in the table), I have to create the OUTPUT_TABLE function, pass it the sqlTable variable, and perform my own processing of the table in that function.

The OUTPUT_TABLE function that is shown in Figure 7 is an abbreviated version of the actual function; much of the repetitive code for different column headings and values has been omitted. However, you can see the specific Net.Data functions that are used to work with individual column values.

Starting in the section labeled “Table headers”, I set up the table and the column headings. This time, instead of using the $(Nx) variables for column names, I need to use the Net.Data function @dtw_tb_rgetn (Table Get Name). The parameters for the function are the name of the table (sqlTable) and the column number whose name I want to retrieve.

Heads up: there is also a function @dtw_tb_getn to return the name of a column. The difference is that @dtw_tb_getn uses a third parameter to return the name of the column in a variable. Use that version of the function if you want to retrieve the column name and use it later. Since I am using the column name directly “in place”, I use the “return” form of the function, to return the value directly where it is used in the macro. Most Net.Data functions have both forms of a function (an “r” version and a non-“r” version). The documentation for all of the “r” versions is found in the non-“r” version in the Net.Data references.

After setting the table column headers, I work with the table rows. In this section, I need to perform my own loop over the sqlTable result set. I cannot use the Net.Data %row block to provide automatic looping. The first few statements in the “Table rows” section retrieve the number of rows in the sqlTable, assign a starting row index value, and set the background color for the first row. I then set up a while condition, to loop while the row index is less than or equal to the number of rows in the result set.

Inside the loop, I format an HTML table row for each row in the result set. To access the column values in the result set, I use the @dtw_tb_rgetv (Table Get Value) function. This function uses three parameters: the name of the table variable, the row within the table variable, which is incremented by one each time through the loop, and the column number within the row. On each pass through the while loop, all of the columns in a row are retrieved and formatted.

At the bottom of the loop (after the closing </tr> tag), I use an %if condition to flip the background color on alternating rows. I then increment the rowIndex variable and close the while loop.

About That Link

Figure 6 shows the Table4.ndm macro output, with an <a href> link applied to the customer number column. When you click on a customer number, the macro displays a “detail” page, similar to Figure 8. The example shows how to have the Net.Data equivalent of a subfile going to a detail display.

Figure 8: The Customer Detail page.

Figure 9 shows the modification to the code from Table3.ndm that puts the link onto the customer number. Figure 9 also includes part of the %html(CUSTOMER_DETAIL) section of the Table4.ndm macro, which creates the customer detail page shown in Figure 8.

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

%{ Table4.ndm -- Net.Data table processing, example 4 %}

%{ Create link from customer number to display detail form. %}

%{ %}

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

%{ Define section for the macro %}

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

%define {

DATABASE = "*LOCAL"

DTW_DEFAULT_REPORT = "NO"

sqlTable = %TABLE

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

%{ RUNSQL - run the SQL statement, return %TABLE variable %}

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

%function(DTW_SQL) RUNSQL(IN sqlString,

OUT sqlTable) {

$(sqlString)

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

%{ OUTPUT_TABLE - macro function to work with %TABLE variable %}

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

%macro_function OUTPUT_TABLE (IN sqlTable) {

<!---------------------------------------------------

Table headers

---------------------------------------------------->

<table border="0"

cellpadding="1"

bgcolor="lightcyan">

<th>

@dtw_tb_rgetn(sqlTable, "1") Customer Number

</th>

<th>

@dtw_tb_rgetn(sqlTable, "2") Last Name

</th>

<th>

@dtw_tb_rgetn(sqlTable, "3") Initials

</th>

<th>

@dtw_tb_rgetn(sqlTable, "4") Street

</th>

<th>

@dtw_tb_rgetn(sqlTable, "5") City

</th>

<th>

@dtw_tb_rgetn(sqlTable, "6") State

</th>

<th>

@dtw_tb_rgetn(sqlTable, "7") Zip Code

</th>

<th>

@dtw_tb_rgetn(sqlTable, "8") Credit Limit

</th>

<th>

@dtw_tb_rgetn(sqlTable, "9") Charge Code

</th>

<th>

@dtw_tb_rgetn(sqlTable, "10") Balance Due

</th>

<th>

@dtw_tb_rgetn(sqlTable, "11") Credit Due

</th>

</tr>

<!---------------------------------------------------

Table rows

---------------------------------------------------->

@dtw_tb_rows(sqlTable, numRows)

@dtw_assign(rowIndex, "1")

@dtw_assign(color, "lightcyan")

%while (rowIndex <= numRows) {

@dtw_tb_getv(sqlTable, rowIndex, "1", cusnum)

<a href="CUSTOMER_DETAIL?cusnum=$(cusnum)">$(cusnum)</a>

</td>

<td>

@dtw_tb_rgetv(sqlTable, rowIndex, "2")

</td>

<td>

@dtw_tb_rgetv(sqlTable, rowIndex, "3")

</td>

<td>