How to Update Files using Net.Data

By Craig Pelkie

Summary: it is quite easy to use Net.Data for simple database retrievals and displays. However, the Net.Data code to update data in a table is more involved. In this article, you will see an example of retrieving, displaying, and updating a record in a file.

To create complete Web-based applications, you need to know how to get data from an HTML form into your database, either to create new records or update existing records. Inserting records is easier than updating, since for an insert you simply start with a blank HTML form (no values displayed in the input fields), then pass any values that are entered to an SQL INSERT statement. For update, you need to retrieve the existing record using an SQL SELECT statement, display the current values, then use the updated values in an SQL UPDATE statement.

The example shown in this article lets you retrieve a single record from the QCUSTCDT file, starting with the input form shown in Figure 1. After entering a valid customer number, you click the button. The Net.Data macro retrieves the customer data and displays it using the input form shown in Figure 2. On Figure 2, you can select the “Update” or “Get another” button. If you select “Update”, the confirmation page shown in Figure 3 is displayed. If you select “Get another” from either Figure 2 or Figure 3, you are returned to the customer prompt form (Figure 1).

Figure 1: This is the prompt form for the customer number.

Figure 2: The customer data is displayed in input-capable fields.

Figure 3: The confirmation page is shown when the customer record is updated.

The code for the Net.Data macro is explained in the following sections. I assume at this point that you have Net.Data successfully configured on your AS/400 system, and that you have some basic familiarity with Net.Data macros. You can review earlier articles in this series for the prerequisites, if necessary.

How the macro is processed

Before reviewing the sections of the macro, you should understand the flow of the macro. The macro is invoked when the URL is entered, specifying the macro name and the section to start at. In this case, the macro is named ndupdate.ndm, and the start-at section is named “input”, so the URL should look like this:

http://systemname/path/ndupdate.ndm/input

where “systemname” is the name or IP address of your AS/400 system (and port number, if necessary), “path” is the virtual path to your Net.Data macros directory (as specified on a PASS directive in your HTTP Server configuration file), and “ndupdate.ndm/input” is the name of the macro and the start-at section.

In the INPUT section, Net.Data displays the HTML for the prompt page. When you click the button, the <form> statement within that section is used to identify the next section of the macro to display, which is GETCUST. GETCUST calls the getCusrec function, which runs the SQL SELECT statement to get the selected customer data and display it on the form. (Note: there is no error handling shown in this macro to handle a “customer not found” condition. In a “real” macro, you should add error recovery code for conditions like that.)

On the Customer Record form, you can change any of the input fields. The customer number is not input capable. If you click the Update button, the UPDATE section of the macro is invoked. That section of the macro calls the updateCusrec function to execute the SQL UPDATE statement, then displays the confirmation page.

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

%{ NDUPDATE - Retrieve/update single record %}

%{ %}

%{ Craig Pelkie %}

%{ Bits & Bytes Programming, Inc. %}

%{ craig@web400.com %}

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

%{ Section A: Defines for macro %}

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

%define {

DATABASE = "*LOCAL"

DTW_DEFAULT_REPORT = "NO"

SHOWSQL = "YES"

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

%{ Section B: SQL function updateCusrec %}

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

%function(dtw_sql) updateCusrec (IN CUSNUM,

IN INIT,

IN LSTNAM,

IN STREET,

IN CITY,

IN STATE,

IN ZIPCOD) {

update qgpl.qcustcdt

set

INIT = '@dtw_raddquote($(INIT))',

LSTNAM = '@dtw_raddquote($(LSTNAM))',

STREET = '@dtw_raddquote($(STREET))',

CITY = '@dtw_raddquote($(CITY))',

STATE = '@dtw_raddquote($(STATE))',

ZIPCOD = $(ZIPCOD)

where

CUSNUM = $(CUSNUM)

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

%{ Section C: SQL function getCusrec %}

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

%function (DTW_SQL) getCusrec (IN pmCusnum) {

select * from qgpl.qcustcdt where cusnum = $(pmCusnum)

%report{

%row{

<html>

<head>

<title>NDUPDATE - Retrieve/update single record</title>

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

%{ Section D - JavaScript %}

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

function SubmitForm(requestedAction) {

document.custForm.action = requestedAction;

document.custForm.submit();

}

</script>

</head>

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

%{ Section E - HTML Form %}

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

<body>

<form method="post"

name="custForm">

<h1>Customer Record</h1>

<tr>

<td>Customer</td>

<td>

$(V_CUSNUM)

<input type="hidden"

name="CUSNUM"

value="$(V_CUSNUM)">

</td>

</tr>

<tr>

<td>

<input type="text"

name="INIT"

value="$(V_INIT)"

size="3"

maxlength="3">

<input type="text"

name="LSTNAM"

value="$(V_LSTNAM)"

size="8"

maxlength="8">

</td>

</tr>

<tr>

<td>Street</td>

<td>

<input type="text"

name="STREET"

value="$(V_STREET)"

size="13"

maxlength="13">

</td>

</tr>

<tr>

<td>

<input type="text"

name="CITY"

value="$(V_CITY)"

size="6"

maxlength="6">

<input type="text"

name="STATE"

value="$(V_STATE)"

size="2"

maxlength="2">

<input type="text"

name="ZIPCOD"

value="$(V_ZIPCOD)"

size="5"

maxlength="5">

</td>

</tr>

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

%{ Section F - Buttons %}

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

<tr>

<td>

<input type=button

value="Update"

onClick="SubmitForm('UPDATE')">

<input type=button

value="Get another"

onClick="SubmitForm('INPUT')">

</td>

</tr>

</table>

</form>

</body>

</html>

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

%{ Section G: HTML section GETCUST %}

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

%html (GETCUST) {

@getCusrec(pmCusnum)

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

%{ Section H: HTML section UPDATE %}

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

%html (UPDATE) {

<html>

<head>

<title>NDUPDATE - Retrieve/update single record</title>

</head>

<body>

<form method="post"

action="INPUT">

@updateCusrec($(CUSNUM),

$(INIT),

$(LSTNAM),

$(STREET),

$(CITY),

$(STATE),

$(ZIPCOD))

<h1>Customer $(CUSNUM) was updated.</h1>

<input type="submit"

value="Get another">

</form>

</body>

</html>

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

%{ Section I: HTML section INPUT %}

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

%html (INPUT) {

<html>

<head>

<title>NDUPDATE - Retrieve/update single record</title>

</head>

<body>

<form method="post"

action="getcust">

<h1>Retrieve/update single record</h1>

Enter the customer number to retrieve:

<input type="text"

name="pmCusnum"

size="6"

maxlength="6">

<br>

<input type="submit"

name="btnSubmit"

value="Get customer data">

</form>

</body>

</html>

Section A – Defines

The %define section is conventional. It specifies that the *LOCAL database is in use (the database that is on the AS/400 system that the macro is invoked on). The DTW_DEFAULT_REPORT value is set to “NO” so that the SQL SELECT statement does not attempt to generate a report (HTML table displaying the retrieved data values). The SHOWSQL value is used so that any SQL statements that are executed in the macro will be displayed in the browser. This is a debugging aid that is useful when you alter the SQL statement in a macro, as is being done in this sample. After the macro is tested and working, you will probably want to set SHOWSQL to “NO” before moving the macro into production.

Section B – SQL function updateCusrec

This function is called from Section H (UPDATE section) and is used to run the SQL UPDATE statement. The input parameter values passed to this function represent the field names defined on the HTML form.

In the UPDATE statement, all of the character string values are enclosed in single quotation characters, and each character string value is also passed to the @dtw_raddquote function. That function examines the field value, and if there are any embedded single quotation characters in the field, it doubles them up. The function works for all occurrences of single quotation characters in a field; you only need to invoke the function once per field. Although you might wonder if it is truly necessary to check for single quotation characters in all of the fields, you should consider the consequences of not doubling up the characters: the SQL statement will blow up. You might also consider what would happen if the ZIPCOD field value is not numeric. I assume that you will include JavaScript code in a production version of your applications to verify that numeric fields contain only numeric data; that edit is not shown in this code sample.

Section C – SQL function getCusrec

This section is used to retrieve the customer record and format the input form, as shown in Figure 2. The customer number from the prompt form (Figure 1) is passed to this function and used in the SQL SELECT statement, which is conventional.

Immediately following the SELECT statement is a %report block, which indicates that I am providing code to display the results of the SELECT, rather than using Net.Data’s default report processing. Inside the %report block, I start a %row block to handle the row of data retrieved from the SELECT. In this case, I plan to only work with one row from the table; if in fact the SELECT had retrieved multiple rows (duplicate customer numbers), the macro will attempt to write multiple HTML forms to the browser. You need to “know your data” to know if you will only get one row for the SELECT. If there is a possibility of retrieving multiple rows, you need to code for that.

Section D – JavaScript to handle the buttons

This section is a JavaScript function named SubmitForm that is called when either of the buttons on the Customer Record form is clicked (see Section F). When a button is clicked, it calls the function and passes the requestedAction parameter value, used to indicate the next section of the macro to go to.

The document.custForm syntax is used to identify the HTML form that the script will act on; the name custForm is defined in the HTML <form> statement (see Section E).

Note that JavaScript functions end with a single } character, as opposed to Net.Data blocks ending with the %} characters.

Section E – HTML form

This section defines the HTML form used to display the customer data shown in Figure 2. The <form> statement defines the POST method to transmit data from the browser to the server, and the name of the form.

Data for the customer is displayed in an HTML table, which is conventional.

The first row of the table is the customer number, which is treated differently from the other data values since you don’t want to allow updates to that value. In the <td> cell used to display the value, the $(V_CUSNUM) variable is used to display the customer number value retrieved from the SQL SELECT statement. That value is simply displayed in the browser, not in an input capable field. However, you also need to pass the value of the customer number from this form to the SQL UPDATE statement. One of the ways to pass a value is to use a hidden field, which is also defined in the same <td> cell as the customer number. Note that the “value” parameter for the input field specifies the $(V_CUSNUM) variable, which substitutes in place the value of the customer number.

If you try this macro, you should use the browser View Source option to look at the generated HTML. You will see that the hidden field is not really hidden, since it is plainly visible in the generated HTML; it is simply not shown on the browser display. However, since I now have a named field with a value, I can pass that to the next section of the macro. Another technique that could have been used would be to append the value as a query string on the URL of the next section, but that would mean extra coding to retrieve the value, in addition to the other form fields. Also, if you are using Microsoft Internet Explorer, you can define an input field with a READONLY attribute, which means you don’t have to use a hidden field. Netscape Navigator does not support the READONLY attribute, so you might want to stick with hidden fields if you need to support different browsers.

Each of the other fields retrieved and displayed uses an input field of type “text”, which lets you view and change the field value. For each field, the value from the database is displayed using the $(V_name) format. Also, note the name of each field is defined the same as the database field name. Although you can define your HTML form fields with any names, I used the same names as in the database file to make it easy to match up the form fields with the database field names.

Section F – Buttons

This section is used to define the two buttons shown in Figure 2. The onClick parameter for each button invokes the JavaScript function SubmitForm (see Section D), passing it the name of the next section of the Net.Data macro to go to.

Section G – GETCUST

This section of the macro is called from the INPUT section (see Section I), when you enter a customer number on the prompt form (Figure 1) and click the button. This section passes the customer number value from the form to the getCusrec SQL function (see Section C).

Section H – UPDATE

This section of the macro is called when you click the Update button on the Customer Record form (Figure 2, also see Section F and Section D). The UPDATE calls the updateCusrec SQL function (see Section B), passing it the values of the HTML form fields from the Customer Record form (compare the names of the HTML form fields in Section E with the field names on the @updateCusrec function call in this section of the macro).

After calling updateCusrec, the confirmation page is displayed (Figure 3).

Section I – INPUT

This is the first section of the macro called. It is used to display the prompt form shown in Figure 1. When you click the button, the getCust section is called to retrieve and display the customer data (see Section G).

And even more code

As pointed out in the article, the macro does not include any editing for field values or tests for the results of the SQL statements. In a production macro, you will need to include JavaScript code to edit input fields for validity, and Net.Data error handling code for the SQL statements.

As you can imagine, the macros can become quite lengthy. I recommend that when you develop such a macro that you iteratively code and test each section, just a bit at a time. For example, there are 10 fields in the customer record that are available to update, but I only coded for the name and address to get my test working. Now that I see it working and understand the “code patterns” to use, it is easy to go back and extend the macro. Also, I can now work on making the form “pretty”, so that it looks somewhat more appealing than browser-based DFU.

Craig Pelkie provides Web programming and consulting services for the AS/400 using Net.Data, WebSphere and CGI, in addition to Microsoft Active Server Pages. He is based in Southern California and can be contacted at craig@web400.com. You can also view other articles by Craig at http://www.web400.com.