Doing Subfiles in Net.Data

By Craig Pelkie

Abstract: When you need to display an HTML table of data in Net.Data you have two options: display the entire table at once and let the browser handle the scrolling, or split the table into smaller sections. When you use smaller sections, you need to provide some form of navigation for forward and backward scrolling. This article shows an example of a navigation system for Net.Data that you can use as a basis for your own macros.

One of the most frequent questions I hear about Net.Data is, how do you make it work like a subfile? Practically all AS/400 programmers are familiar with DDS subfiles, which are commonly used to present data in manageable chunks called “pages”. The reason why this question comes up is because Net.Data by default chooses to present all rows of a table at one time. Creating such Net.Data macros is trivial, after you get past the initial configuration, but that default behavior is not desirable if it results in excessive vertical scrolling in the browser.

Net.Data provides a technique that you can use to set the total number of rows displayed in a table and the starting row number to use in your resultset. What Net.Data does not provide is an easy technique to navigate from one section of the resultset to another; you need to provide navigation through Net.Data code. In this article, you’ll learn how to create a Net.Data macro that uses the starting row technique, and you’ll also see an example of a navigation system that you might want to implement.

Sample Pages

Figures 1, 2 and 3 are samples of the Web pages that are output from the Net.Data macro. Figure 1 shows the first page that appears when the macro runs. Notice that in the navigation system (the topmost row of the table), the number 1 is in bold and is not “clickable”; that indicates that page 1 is the currently displayed page. Other pages are clickable; if you click their numbers, you go directly to the page with the associated records in the resultset. Finally, there is a “Next” link that takes you to the next page in sequence. This is provided as a convenience, so that rather than try to click the relatively smaller page number target, you can simply click Next.

Figure 1: A view of a Net.Data "subfile", showing the first of six pages.

Figure 2 shows the same macro displaying the second page of the resultset. In this case, the navigation system now includes a “Prev” link to take you to the previous page.

Figure 2: Page 2 of the "subfile". Note that the "Prev" link is now available.

Finally, Figure 3 shows the last page of the resultset. This is a partial page, listing only two items. Because this is the last page in the sequence, the “Next” link is no longer available.

Figure 3: The last page of the "subfile". There is no "next" link.

Now, a disclaimer: I did not invent this navigation system, I happily and knowingly copied it from www.cdw.com, a computer equipment seller that I have ordered from (I am not associated with them). Although in the past copying ideas from other Web sites was tolerated and accepted, such behavior now usually leads to litigation. I simply copied this system (but not their code, which I don’t have access to) because I think it is one of the best multiple page navigation systems I have seen on the Web.

Navigation Concepts

Before getting into the code, there are a few concepts you should be clear about to understand how the navigation system works. When you run a query against a database file, you usually don’t know in advance how many rows will be returned in the resultset. Because of that, you can’t hard-code the navigation links; all of the links must be dynamically generated.

Figure 1 shows some of the metadata that you need to work with. The total number of rows for the resultset is 52, and the number of pages is six. You can vary the number of pages by adjusting the number of rows to be displayed on a Web page. In this example, I chose to display ten rows per page, meaning that six pages are required (five “whole” pages and one partial page, as shown in Figure 3).

Once you know the number of rows and the size of each page, you can start generating links for the pages. For example, the link for page “1” should take you to row 1 in the resultset. The link for page “2” should start at row 11 (the starting value of the previous page plus the number of rows per page).

The “Next” and “Prev” links are in relation to the current page. In the figures, the current page is displayed as bold text, and is not clickable. The “Next” link points to the row at the position immediately after the last row on the page. The “Prev” link points to the row that is a page size less than the first row on the page.

When you first display the page, the resultset is displayed starting from row 1. As you click links, you call back to the same page, passing it the value of the next starting row. In this example, the next starting row is passed from the clickable links by using a query string value. You can see examples of the query string in Figures 2 and 3 in the URL Address, and also at the bottom of the figures, where the value of the clickable link is displayed. In this sample, the query string is the value after the ? mark in the URL, for example s=21 (the “s” stands for “start at row number”). In the Net.Data macro, you need to generate the value for the query string, which is part of the HTML sent to the browser. You also need to write code to extract the value of the query string from the incoming URL when you click one of the links.

When you click one of the navigation links, the start at row number value is extracted and the value is assigned to the Net.Data built-in variable START_ROW_NUM. When you set that value before executing an SQL SELECT statement, Net.Data passes the value to the query processor so that it knows where in the resultset it is to start returning values to the macro.

After running the query, you can use Net.Data %report blocks or your own code to work with the subset of the resultset. If you use Net.Data %report blocks, Net.Data will only process as many rows as you specify for the RPT_MAX_ROWS Net.Data built-in variable.

The Net.Data Code

The complete Net.Data macro is shown below. This macro was run against a sample PARTS database file that I obtained from IBM. You can very easily run the macro against any of your own database files by simply changing the SQL SELECT statement.

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

%{ StartRow.ndm -- Net.Data table processing with Starting Row number %}

%{ Create numbered links to show sections of a resultset. %}

%{ %}

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

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

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

%define {

DATABASE = "*LOCAL"

DTW_DEFAULT_REPORT = "NO"

DTW_SET_TOTAL_ROWS = "YES"

RPT_MAX_ROWS = "10"

currentPage = "0"

pageCount = "1"

rowCount = "0"

rowIndex = "1"

rowIndexNext = "1"

totalPages = "0"

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

%{ Section B - RUNSQL - run the SQL statement, generate HTML table %}

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

%function(DTW_SQL) RUNSQL() {

select * from apilib.parts order by partno

%report{

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

%{ Section C - test for empty result set, exit if empty %}

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

%if (TOTAL_ROWS == "0")

No rows retrieved for query.

@dtw_exit()

%endif

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

Table headers

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

<table border="1"

cellpadding="2"

cellspacing="0"

bgcolor="white"

width="500">

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

Navigation data and links

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

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

%{ Section D - calculate/display total number of pages to %}

%{ generate (whole and fractional) %}

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

@dtw_assign(totalPages, @dtw_rintdiv(TOTAL_ROWS, RPT_MAX_ROWS))

%if (@dtw_rdivrem(TOTAL_ROWS, RPT_MAX_ROWS) > "0")

@dtw_add(totalPages, "1", totalPages)

%endif

Total rows: $(TOTAL_ROWS)

Total pages: $(totalPages)

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

%{ Section E - Put "<< Prev"" option if on page 2..n %}

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

%if (START_ROW_NUM > RPT_MAX_ROWS)

<a href="input?s=@dtw_rsubtract(START_ROW_NUM,

RPT_MAX_ROWS)">

<< Prev

</a>

%endif

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

%{ Section F - Put links for each page %}

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

%while(pageCount <= totalPages) {

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

%{ Section G - link for "current page" %}

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

%if (rowIndex == START_ROW_NUM)

$(pageCount)

@dtw_assign(rowIndexNext, @dtw_radd(rowIndex,

RPT_MAX_ROWS))

@dtw_assign(currentPage, pageCount)

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

%{ Section H - link for other pages %}

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

%else

<a href="input?s=$(rowIndex)">$(pageCount)</a>

%endif

@dtw_add(rowIndex, RPT_MAX_ROWS, rowIndex)

@dtw_add(pageCount, "1", pageCount)

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

%{ Section I - Put "Next >>" option if on page 1..n-1 %}

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

%if (currentPage < totalPages)

Next >>

</a>

%endif

</td>

</tr>

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

Section J - Column names

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

</tr>

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

Section K - Table rows

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

%row{

<tr>

</tr>

</table>

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

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

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

%html(INPUT) {

<html>

<head>

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

</head>

<body>

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

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

%{ Section L - Extract the starting row number from the Query String %}

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

@dtw_assign(queryString, @dtw_rgetenv("QUERY_STRING"))

@dtw_assign(eqPos, @dtw_rpos("=", queryString))

%if (eqPos >= "1")

@dtw_assign(START_ROW_NUM, @dtw_rsubstr(queryString,

@dtw_radd(eqPos, "1")))

%else

@dtw_assign(START_ROW_NUM, "1")

%endif

@RUNSQL()

</center>

</body>

</html>

The code is described in sections as follows.

Section A – Define section for the macro

This is a conventional Net.Data %define section, and is used to set values for Net.Data built-in variables and user-defined variables. There are two significant items defined that you need to be aware of:

DTW_SET_TOTAL_ROWS – setting this value to “YES” lets you work with the Net.Data built-in variable TOTAL_ROWS, which is the number of rows returned from your SELECT statement. The documentation for this value indicates that you also need to set it in the Language Environment in your Net.Data INI file, which for the AS/400 system is DTW_SQL. When I looked into my INI file, I found that I wouldn’t have enough room in the source statement for DTW_SQL to include this value (DTW_SET_TOTAL_ROWS), so I took the advice of IBM’s AS/400 Net.Data Web site and simply removed the DTW_SQL statement from my INI file. (Apparently, as of V4R3 and higher, you no longer need any Language Environment statements in your INI file. You still might need the INI file for other settings, though.)

RPT_MAX_ROWS – this value is used to set the number of rows that you want displayed on each Web page. You can hard-code this value, as I did in this example, or calculate it at run time, a technique that I will describe later.

Section B – RUNSQL

This section starts the RUNSQL function, which includes the SELECT statement and the %report block.

Section C – Test for empty resultset

If there are no records in the resultset, the macro writes a message to the browser and exits. Because DTW_SET_TOTAL_ROWS was set to “Yes”, you can test the value of the Net.Data TOTAL_ROWS built-in variable.

Section D – Calculate number of pages

This section calculates the number of pages that will be generated from the result set. This is based on two Net.Data division operations. The first, using @dtw_rintdiv, calculates the “whole” number of pages by dividing the TOTAL_ROWS value by the RPT_MAX_ROWS value. For example, for a resultset of 52 rows with a page size of ten, the calculated value is five.

However, to display 52 rows, you actually need six Web pages, so the second division operation using @dtw_rdivrem is used. This performs a division using the same factors. If the remainder is greater than zero, then an additional page is required, so the page count is incremented.

The TOTAL_ROWS and totalPages values are written to the browser.

Section E – Put “<< Prev” link

This section writes the “<< Prev” (previous) link to the browser, if the current START_ROW_NUM value is greater than the number of rows per page. When the macro starts, the START_ROW_NUM value is one and the RPT_MAX_ROWS value is ten. The rows displayed from the resultset are rows one through ten, so there is no “previous” page. Once you go past the first Web page, the START_ROW_NUM value will be greater than the RPT_MAX_ROWS value, so the “prev” link is generated.

Note that the link is generated as an <a href> pointing back to the INPUT section of this macro, with a query string value of the START_ROW_NUM minus the RPT_MAX_ROWS. For example, if you are currently on page three, the START_ROW_NUM is 21, so the link for the previous page will be 11.

Section F – Put links for each page

This section starts a %while loop for the total number of pages.

Section G – Link for “current page”

The “current page” is the one being displayed in the browser. This is determined by comparing a variable rowIndex with the START_ROW_NUM. If the two values are equal, then a “link” is generated that is bold, but not clickable (it doesn’t make sense to have a clickable link for the page you are viewing).

I also store the value of rowIndexNext, which gives me the value for the “Next” link, defined as the current rowIndex plus the RPT_MAX_ROWS. For example, if I am currently displaying rows 11 – 20 in the browser, my current rowIndex value is 11. The rowIndexNext value will be set to 21.

Another variable set in this section is currentPage, which I use in Section I to determine if the current page I am viewing is the last page in the sequence.

Section H – Links for other pages

In this section, I generate clickable links for other pages. The link value that is displayed is the page number (see the figures), and the value for each link is the starting rowIndex for that page. When I start the macro, the rowIndex value is set to one. After completing Section G or the <a href> in Section H, I increment the rowIndex to the value of the next page. The rowIndex is incremented by the RPT_MAX_ROWS value, so it “steps up” ten at a time: 1, 11, 21, and so on.

I also increment the pageCount value in this section, which is used in the %while loop.

Section I – Put “Next >>” link

In this section, I compare the value of the currentPage (which was calculated back in Section G) to the totalPages (from Section D). If the currentPage is less than the totalPages, then I have additional pages to be displayed, so I can generate the “Next” link. If the currentPage is not less than totalPages, then I am on the last page, and it does not make sense to generate a “Next” link.

Sections J, K – output column names, table rows

These sections use conventional Net.Data column name / column value fields ($(Nx) and $(Vx)) to write the column name and column values to the browser. If you are uncertain how these features work, you can review past columns in this series or the Net.Data documentation on IBM’s web site at www.as400.ibm.com/netdata.

Section L – Extract Query String value

This section is in the INPUT section of the macro, which is invoked from the URL. When the Net.Data macro starts, it goes to the INPUT section and starts emitting HTML and processing Net.Data macro functions.

In this sample, the @dtw_rgetenv function is used to retrieve the QUERY_STRING environment variable, which is maintained by IBM HTTP Server for AS/400. The query string contains the values in the URL following the ? character.

To extract the value that I am interested in (the “s=11” for example), I look for the position of the equals sign in the query string. If the equals sign is found, I use the @dtw_rsubstr function to extract the substring of the query string starting at one position beyond the equals sign. For example, with a query string “s=11”, the value returned will be “11”, which gives me my next START_ROW_NUM value.

If there is no equal sign in the query string, I assign the value “1” to START_ROW_NUM. For example, the first time you invoke the macro, you will not provide a query string (see Figure 1 URL Address), so the START_ROW_NUM points to the first row in the resultset.

Note that this example of query string extraction is not “robust”, since it assumes that only one value is present in the query string and that it is a valid starting row number. Rather than use the query string to convey the next starting row number between invocations of the macro, you might consider using an HTML hidden input field.

After extracting the query string value (if any) and assigning the value to START_ROW_NUM, the RUNSQL function is invoked, starting at Section B.

Other Issues

Apart from some of the configuration issues mentioned above (in particular, the issue with the DTW_SQL Language Environment statement in regard to the DTW_SET_TOTAL_ROWS option), the macro is conventional. If you have a working Net.Data configuration on your AS/400 system, you should be able to load the macro, set your SQL statement to your selected database file, and run it. (Note, you may want to adjust the $(Nx) and $(Vx) options to display more than five columns of data).

There are some other issues you need to be aware of with this type of macro. The primary issue is, you are running the SELECT statement every time you click a link, so you can potentially generate a different result set. That means that a macro using this technique is not well suited to highly volatile data, but is perhaps better suited to relatively static data, such as listings of item catalogs. You will have to determine what is “volatile” in relation to your data. For example, files that are updated by batch processes overnight might be ideal candidates for a Net.Data macro like this. (Yes, I know the Web is “7x24”, but you can do what everybody else does, and put up a “we’re updating” page so that your customers 12 time zones away will not be confused.)

Another issue that always comes up is the “we’ve got a million records” syndrome. This is usually framed as, “how do we display our million record (you pick) master file”.

The answer is, you don’t. You don’t do it now with green-screen interactives. The only people remotely interested in perusing that much data are accountants and auditors, and they should only be given green-bar reports anyway.

Get on the side of your users! First of all, put a nice front-end on the SELECT statement, so that they cannot possibly select more data than they can realistically deal with. I know, some of you wish the subfile limitation of 9999 records would be lifted, but does anybody really think a human being should have to page through hundreds of screens or Web pages to find the data they need?

If your wilily users circumvent your SELECT processing and still generate an enormous resultset, then let them know. Now that you have seen a technique to retrieve the total number of records in the result set, tell them how many records they’ve retrieved, and refuse to display them. To punish them, make them reenter all of their query parameters again.

More likely, you will have reasonable users who know how to intelligently use your carefully designed queries, but you still end up with several hundred rows. In that case, you might want to dynamically adjust the size of RPT_MAX_ROWS to display more rows per Web page (assuming that expanding the size of the page does not knock the all-important “contact” information at the bottom of your page too far below the browser horizon). For example, you can recode the sample macro to calculate a RPT_MAX_ROWS after getting TOTAL_ROWS. You might decide to display up to 50 rows per page, so that the user does not have to click through a long series of links. As an aside, if you do generate pages that vertically scroll, you might want to replicate the navigation links at the bottom of the pages as well as at the top (see cdw.com).

Some of what I’ve suggested may have been facetious, but you can bring your common sense to bear on Web page data presentation. I know it’s more work, but write more code and favor the user.

Craig Pelkie works with a dot.com startup in the San Diego area, in addition to other programming and consulting activities. He created and is the instructor for the IBM Learning Services course S6178, “Using Net.Data with the AS/400”. You can view other articles and get code samples at his Web site, http://www.web400.com.