How to use AS/400 Stored Procedures with Net.Data

By Craig Pelkie

Summary: although it is easy to execute any valid SQL statement within a Net.Data macro, there may be times when it is advantageous to execute SQL in a stored procedure and return the results to Net.Data. This article describes a sample stored procedure written in RPG that is called from Net.Data and returns two result sets to the Net.Data macro.

Net.Data is a great AS/400 Web development tool because it lets you get a result quickly. Once you understand how to configure Net.Data and the basics of the language, you can quickly create Net.Data macros to access database files on your AS/400. You can use Net.Data’s default report processing to automatically generate a resulting HTML display, or you can use Net.Data functions to completely control the appearance of the Web page.

When you move beyond the basics, there are some questions you will probably want to resolve. For example:

How can you improve the performance of Net.Data macros that use SQL statements?
How can you access database files that do not have *PUBLIC authority and that user profile QTMHHTP1 is not authorized to?
How can you partition the workload of developing a major Web-based application?

One technique that you can use to help with those requirements is AS/400 stored procedures. In this article, I’ll show you how some relatively simple stored procedure code can be used to supplement your Net.Data techniques.

What is a stored procedure?

To use a stored procedure, you first have to understand what it is. On most platforms, a stored procedure is simply a series of SQL statements and directives that are run as a unit. The stored procedure can support input and output parameters, and may return no or several result sets (a result set is the rows of data returned as the result of running a SELECT statement). Stored procedures are cataloged in the SQL system catalog using the CREATE PROCEDURE statement.

On the AS/400, the rules for stored procedures are somewhat relaxed, compared with other platforms. For example, an AS/400 stored procedure can be written in any one of several AS/400 languages, including RPG, COBOL, FORTRAN, PL/I, REXX, CL and C. Stored procedures written in one of those languages are called external stored procedures, as opposed to SQL stored procedures which are written entirely using SQL statements.

External stored procedures do not necessarily have to include embedded SQL statements, and they do not need to be cataloged.

To create external stored procedures that include embedded SQL statements, you need the DB2 Query Manager and SQL Developer Kit, 5769-ST1. To create SQL stored procedures on the AS/400 system, you need the SQL Developer Kit and the C compiler, 5769-CX2.

Stored procedure or program call?

Net.Data for the AS/400 system supports both calls to stored procedures and calls to other AS/400 programs. You can pass parameters to and from stored procedures or other programs. The primary differences between the two types of calls are:

Stored procedures are called from a @dtwl_sql type function in Net.Data. Other programs are called using a @dtw_directcall type function.
Stored procedures can return one or more result sets to the Net.Data macro. Other programs can only return values directly to Net.Data through the parameter list.

Because you will frequently need to work with a set of records in your Net.Data macro, it is important to know how to work with stored procedures so that you can easily get the required data from a result set.

A stored procedure sample

Figure 1 shows the sample Web page from a Net.Data macro that prompts for parameters to retrieve a customer master record and a list of orders for the customer. When the button is clicked, the Net.Data macro invokes an external stored procedure written in RPG that returns two result sets to the macro.

Figure 1: The prompting form used to get parameters to pass to the stored procedure.

Figure 2 shows the resulting HTML tables that are automatically built by Net.Data. The tables are based on two different result sets that are returned to the macro from the stored procedure:

A result set containing the customer data. This result set has only one row (record) in it.
A result set containing the list of orders for the customer. This result set can contain from 0…n rows.

Admittedly, the automatic HTML tables do not look very good, but the macro is intended as a “proof of concept”. Once the result sets are returned to Net.Data, you can use additional Net.Data table formatting functions to create better looking HTML tables (for an explanation and samples of using Net.Data table formatting functions, see Net.Data Table Functions, also available at http://www.web400.com).

Figure 2: Sample Web page returned from Net.Data, showing the two HTML tables that are automatically generated.

The Net.Data ResultSets macro

Shown below is the Net.Data macro used to produce Figures 1 and 2, and to invoke the RPG stored procedure. The macro is invoked at the Input block, as shown in this sample URL:

http://server_name/nd01/resultsets.ndm/input

%{***********************************************************%}

%{ Net.Data macro ResultSets.ndm %}

%{ %}

%{ Demonstrate calling stored procedure that returns %}

%{ multiple result sets. %}

%{***********************************************************%}

%{ Define Net.Data table variables %}

%{***********************************************************%}

%define{

DTW_DEFAULT_REPORT = "NO"

DTW_HTML_TABLE = "YES"

TABLE1 = %table

TABLE2 = %table

%{***********************************************************%}

%{ SQL function to invoke multiple result set stored proc. %}

%{***********************************************************%}

%function(dtw_sql) myProc(in char(4) custoomer_ID,

in decimal(3,0) district_id,

in char(4) warehouse_id,

out t1,

out t2) {

call resultsets

%{***********************************************************%}

%{ Macro function - uses default report processing for table %}

%{***********************************************************%}

%macro_function showTable(INOUT table) {

%{***********************************************************%}

%{ HTML section - invoke stored procedure, display tables %}

%{***********************************************************%}

%html(GETTABLES) {

<html>

<head>

<title>Net.Data macro using SQL Stored Procedure</title>

</head>

<body>

<h1>Net.Data macro using SQL Stored Procedure</h1>

@myProc(FORM_CUSTOMER_ID,

FORM_DISTRICT_ID,

FORM_WAREHOUSE_ID,

TABLE1,

TABLE2)

@dtw_assign(DTW_DEFAULT_REPORT, "YES")

First result set table:

<br>

@showTable(TABLE1)

<hr>

Second result set table:

<br>

@showTable(TABLE2)

</body>

</html>

%{***********************************************************%}

%{ HTML section - display page, get library name %}

%{***********************************************************%}

%html(Input) {

<html>

<head>

<title>

Net.Data macro using

Multiple Result Set Stored Procedure

</title>

</head>

<body>

<form action="GETTABLES"

method="POST">

<h1>

Net.Data macro using

Multiple Result Set Stored Procedure

</h1>

<br>

This macro uses multiple result sets and

the parameter passing technique.

</font>

<br>

<hr>

Enter the Customer ID (example: 0001):

<input type="text"

name="FORM_CUSTOMER_ID"

size="4"

maxlength="4">

<br>

Enter the District ID (example: 001):

<input type="text"

name="FORM_DISTRICT_ID"

size="3"

maxlength="3">

<br>

Enter the Warehouse ID (example: 0001):

<input type="text"

name="FORM_WAREHOUSE_ID"

size="4"

maxlength="4">

<br>

<input type="Submit"

value="Run Stored Procedure">

</form>

</body>

</html>

The %define block

To understand how the macro works, start at the top in the %define block. I start by setting the DTW_DEFAULT_REPORT option to “no” to prevent Net.Data from automatically generating an HTML table when it retrieves the result set. Even though I do in fact use the automatic HTML table generation feature later in the macro, this technique shows what you need to do if you intend to intercept the table and use your own set of Net.Data table handling functions to generate the HTML.

The DTW_HTML_TABLE option indicates that Net.Data is to use <table> type constructs rather than the <pre> tag for table formatting when it generates HTML tables. You should always include this option in your Net.Data define block.

The two %table variables are used to receive the result sets back from the stored procedure.

The %html(input) block

When the macro is invoked from the URL, it starts at the %html(input) block, which is the last block in the source code. The entire block is simply HTML code that displays the Web form shown in Figure 1. There are only a few items of interest in this block:

The <form> statement, indicating that the next block to be invoked is the GETTABLES block.
The <input> tags, used to return form values FORM_CUSTOMER_ID, FORM_DISTRICT_ID and FORM_WAREHOUSE_ID to the macro.

When you enter values into the three input fields and click the Run Stored Procedure button, the input field values are returned to the server and the GETTABLES block is invoked. At that point, the values of the input fields are available to that block.

The %html(GETTABLES) block

Most of the action in the macro happens in the GETTABLES block. This block is an %html block, since it is used to generate the HTML that produces the Web page shown in Figure 2. Reading down through this block, you can see that there are four Net.Data function invocations:

@myProc invokes the myProc function, defined as a dtw_sql type function.
@dtw_assign sets the value of DTW_DEFAULT_REPORT to “YES”.
Two invocations to the @showTable function are used to format the two result sets returned to the macro.

The myProc dtw_sql function

The myProc function is defined near the beginning of the macro. There are several points to note about this function definition:

The function type is dtw_sql. By defining the function as this type, I am able to use Net.Data’s ability to return result sets to the macro.

There are five parameters defined for the function. The first three are IN (input) type parameters, meaning that the values are passed from the Net.Data macro to the stored procedure. The last two are OUT (output) type parameters, meaning that the values are passed from the stored procedure back to the Net.Data macro.
The IN parameters include both a data type definition and a placeholder name. As with any call to another program, you must match up the parameter data types and lengths, or you will get errors or unexpected results. The parameter name does not need to match either the input field name in the Net.Data macro or the actual name in the stored procedure. Some examples you will see of Net.Data parameter names are as simple as p1, p2 and p3. I chose to use more descriptive placeholder names so that I can easily tell what the parameter is used for just by looking at the parameter list.
The OUT parameters reference the previously defined Net.Data %table variables. These parameter names are also not important.

The body of the function contains the call statement that invokes the stored procedure, program RESULTSETS in library ND01. The procedure name apparently must be fully qualified, as shown in the function. Net.Data does not seem to use the value in the EXEC_PATH statement in the INI file to resolve the unqualified procedure name. Also, cataloging the procedure using the SQL statement CREATE PROCEDURE command did not help Net.Data resolve the unqualified procedure name.

Calling the stored procedure and getting result sets back

Now that you understand the dtw_sql function used to define the parameters for the stored procedure call, you can look back at the GETTABLES block to see how the myProc function is invoked. The invocation of the myProc function includes the three variables from the <input> tags, followed by the two table variables defined in the %define block. Upon returning from the function call, the two result sets are available to the Net.Data macro in the TABLE1 and TABLE2 table variables.

Note that the variables from the <input> tags do not need to be specified with the Net.Data variable syntax. For example, I specified FORM_CUSTOMER_ID rather than $(FORM_CUSTOMER_ID). The rule in Net.Data is that if a variable reference is not ambiguous, you do not need to use the variable syntax. If a reference is ambiguous, then you need the variable syntax to indicate that Net.Data is to replace the reference with the value of the variable. For example, the following code shows an example of an ambiguous variable reference:

If your intention is to show the actual value contained in the FORM_CUSTOMER_ID variable, then you need to specify the last part of that input tag as follows:

value=”$(FORM_CUSTOMER_ID)”

Processing the result sets

After the invocation of myProc in the GETTABLES block, I enable default report processing with the @dtw_assign statement. That is followed by HTML to identify the first result set, then an invocation of the @showTable function, passing it the TABLE1 table variable.

The @showTable function precedes the GETTABLES block in the code listing. The function does nothing but receive the value of the table variable. Although the function does nothing, Net.Data default report processing is now in effect, so it generates a default HTML table to display the contents of the result set. As previously mentioned, the resulting table is nothing special to look at (see Figure 2). However, when incrementally developing a macro, I use this technique to view the result set, just to verify that I did get back the data that I was expecting. At this point, I can go ahead and continue development of the @showTable function to include Net.Data table handling functions that let me have more control over the resulting HTML.

The second result set (the list of orders for the customer) is processed in the second invocation of the @showTable function.

The RPG stored procedure

The complete source code for the RPG stored procedure is shown below. Most of the code consists of the embedded SQL statements used to define the cursors that will be used to return the result sets and the actual SELECT statements.

Because this is an external stored procedure, as opposed to an SQL stored procedure, you can include any additional processing required. For example, you can work with data areas, data queues, AS/400 system APIs, and other types of objects, just as in other batch-type RPG programs. In this example, I simply use two SQL SELECT statements to get the requested data from the CSTMR and ORDERS tables.

The program starts with the *ENTRY PLIST, used to accept the incoming parameters from the stored procedure call in the Net.Data macro. Note that there are only three parameters in the parameter list (compare the list with the myProc dtw_sql block in the Net.Data macro). When you code an external stored procedure that returns result sets, you do not use parameter list entries for the returned result sets. However, in the Net.Data macro, you need to specify the returned result sets as parameter items following all of the other “real” parameters that are passed to or received from the external stored procedure.

The first series of EXEC SQL statements declare a cursor named C1 that will contain the result set returned from the SELECT statement against the CSTMR table. A cursor is simply a construct that points to a result set. Operations in the stored procedure take place in terms of the cursor. For example, the second EXEC SQL block is used to open the C1 cursor, which actually runs the SELECT statement. After executing the open C1 statement, the result set is available in the RPG program in terms of the C1 cursor.

The second set of EXEC SQL statements declare the C2 cursor for the ORDERS table, then open that cursor.

The two result sets are actually returned to the Net.Data macro in the third EXEC SQL block, where the result sets are set to the two cursors. You can return up to 20 result sets to a Net.Data macro from each invocation of a stored procedure.

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

* Program RESULTSETS

* Stored procedure, return multiple result sets

* For use with Net.Data macro to demonstrate multiple

* result set capability of Net.Data.

* Use the following command to compile this program:

* CRTSQLRPGI OBJ(lib/RESULTSETS) SRCFILE(lib/QRPGLESRC)

* In the SQL environment, use the following command to

* catalog the stored procedure:

* create procedure resultsets

* (input p1 char(4),

* input p2 decimal(3,0),

* input p3 char(4))

* external name lib_name/resultsets

* language rpgle general

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

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

C* Parameters passed to stored procedure:

C* pmcust - customer ID

C* pmdistrict - district ID

C* pmwarehs - warehouse ID

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

C *entry plist

C parm pmcust 4

C parm pmdistrict 3 0

C parm pmwarehs 4

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

C* Execute SELECT statement for customer file (cursor C1)

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

C/exec sql

C+

C+ declare C1 cursor for

C+

C+ select * from csdb/cstmr

C+

C+ where CID = :pmcust and

C+ CDID = :pmdistrict and

C+ CWID = :pmwarehs

C+

C+ for fetch only

C+

C/end-exec

C/exec sql

C+

C+ open C1

C+

C/end-exec

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

C* Execute SELECT statement for item file (cursor C2)

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

C/exec sql

C+

C+ declare C2 cursor for

C+

C+ select * from csdb/orders

C+

C+ where OCID = :pmcust and

C+ ODID = :pmdistrict and

C+ OWID = :pmwarehs

C+

C+ order by oid

C+

C+ for fetch only

C+

C/end-exec

C/exec sql

C+

C+ open C2

C+

C/end-exec

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

C* Set result sets to return cursor C1 and C2

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

C/exec sql

C+

C+ set result sets cursor C1, cursor C2

C+

C/end-exec

C eval *inlr = *on

C return

Creating and cataloging the stored procedure

The commands used to create and catalog the external stored procedure are included in the comments at the top of the source code. The only required command is CRTSQLRPGI, to create the SQL RPG program. Although there is no particular reason in this example to catalog the stored procedure with the CREATE PROCEDURE command (an SQL command, not a CL command), you might want to catalog all of your stored procedures so that you have an entry for them in the SYSPROCS system table. If you catalog your stored procedures, you can run queries against SYSPROCS to determine what stored procedures are available on your system.

Benefits of stored procedures

So why go through all of this work, when it looks like you could simply enter the SQL statements directly into the Net.Data macro?

In previous releases of OS/400, stored procedures were one of the primary methods available to improve SQL performance, since the access plan for the query could be computed and saved with the stored procedure. Upon invoking the stored procedure, a number of steps in the query optimization phase could be bypassed, since the system already “knew” about the SQL statement. With recent releases of OS/400 (V4R3 and above), there is now a system-wide cache or SQL package of previously executed SQL statements. After the first execution of an SQL statement from any environment (Net.Data, ODBC, etc.), the AS/400 system caches the statement and its access plan information. The result is that the AS/400 system retains the “knowledge” of the SQL statement and uses that retained information when it encounters the SQL statement again.

So that leaves other reasons for stored procedures. One of the primary reasons you should consider is that you can better secure your database if you insist that access to the database is in terms of stored procedures, rather than “raw” SQL statements from Net.Data macros (or other sources). As it stands, an SQL statement from the Net.Data macro runs under user profile QTMHHTP1, so that user profile or *PUBLIC needs to be authorized to the database file. If you don’t want to allow that level of access to those user profiles, you can create a stored procedure using a user profile that does have access to the database file, then change the stored procedure program so that it runs under the owner’s authority, not the user’s authority. That way, QTMHHTP1 invokes the stored procedure, but because the stored procedure runs with the owner’s authority, it can access the database file.

Another reason to use stored procedures applies if you have a “division of labor” in your environment. In many AS/400 shops, programmers do everything, including user interface, program logic and coding, and database access. However, some shops have distinct roles, including Database Administrator (DBA). One of the functions of a DBA is usually the creation of stored procedures. The DBA then makes the stored procedure available to other programmers, who work with the database in terms of the well-defined interface of the stored procedure. Even if you are a “small shop” and create all of the code for an application, it is worth considering separating functions like this, so that all access to the database is through a few controlled gateways (stored procedures), rather than the free-for-all of numerous SQL statements from any number of Net.Data macros. As an added benefit, the stored procedures can be used from other environments, including ODBC, JDBC or even calls from other platforms.

A step at a time

It is important to understand that using stored procedures in your Net.Data macros is not an all-or-nothing proposition. You can start by creating a stored procedure that runs the SELECT statement that you already have in one of your macros, then change that macro to use the stored procedure. As you develop more experience with the technique, you may find that you prefer developing an application from many smaller pieces that can be reused elsewhere.

Craig Pelkie works for a dot.com startup in the San Diego area. He is the developer and primary instructor of the IBM Learning Services course S6178 Using Net.Data with the AS/400. He is available for programming consulting for interesting projects and may be contacted at craig@web400.com.