Administration and Programming Guide for OS/400

Appendix B. Net.Data Sample Macro

This sample macro application displays a list of employees names from which the application user can obtain additional information about an individual employee by selecting the employee's name from the list. The macro uses the SQL language environment to query the EMPLOYEE table for both the employee names and the information about a specific employee.

The macro file uses an include file that contains the DEFINE block for the macro.

Figure 9 shows the sample macro. Figure 10 shows the include file.

Figure 9. Sample macro

%{************************ Sample Macro *****************************
*   FileName = sqlsamp1.d2w                                         * 
*   Description:                                                    *
*     This Net.Data macro queries...                           *
*         - The EMPLOYEE table to create a selection list of        *
*          employees for display at a browser                       *
*         - The EMPLOYEE table to obtain additional information     *
*          about an individual employee                             * 
*                                                                   *
********************************************************************%}
%{***************************************************************************
*   Include for global DEFINEs -                                            *
****************************************************************************%}
%INCLUDE "sqlsamp1.hti"
%{****************************************************************************
*  Function:  queryDB              Language Environment: SQL                *
*  Description: Queries the table designated by the variable myTable and    *
*  creates a selection list from the result.  The value of the variable     *
*  myTable is specified in the include file sqlsamp1.hti.                   *
****************************************************************************%}
%FUNCTION(DTW_SQL) queryDB() {
 SELECT FIRSTNME FROM $(myTable)
%MESSAGE {
    -204:  {<p><b>ERROR -204: Table $(myTable) not found. </b>
           <p>Be sure the correct include file is being used.</b>
           %} : exit
    +default: "WARNING $(RETURN_CODE)" : continue
    -default: "Unexpected ERROR $(RETURN_CODE)" : exit
%}
 
%REPORT {
<select name=emp_name>
%ROW{
<option>$(V1)
%}
</select>
%}
%}
 
%{****************************************************************************
*  Function: fname         Language Environment: SQL                         *
*  Description: Queries the table designated by the variable myTable for     *
*               additional information about the employee identified by the  *
*               variable emp_name.                                           *
******************************************************************************%}
%FUNCTION(DTW_SQL) fname(){
SELECT FIRSTNME, PHONENO, JOB FROM $(myTable) WHERE FIRSTNME='$(emp_name)'
%MESSAGE {
  -204: "Error -204: Table not found "
  -104: "Error -104: Syntax error"
   100: "Warning 100: No records" : continue
  +default: "Warning $(RETURN_CODE)" : continue
  -default: "Unexpected SQL error" : exit
%}
%}

%{***************************************************************************
*  HTML block: INPUT            Title: Dynamic Query Selection              *
*                                                                           *
*  Description: Queries the EMPLOYEE table to create a selection list of    *
*               the employees for display at the browser                    *
****************************************************************************%}
%HTML(INPUT) {
<html>
<head>
<title>Generate Employee Selection List</title>
</head>
<body>
<h3>$(exampleTitle)</h3>
<p>This example queries a table and uses the result to create
a selection list using a <em>%REPORT</em> block.
<hr>
<form method="post" action="report">
@queryDB()<input type="submit" value="Select Employee">
</form>
<hr>
</body>
</html>
%}

%{***************************************************************************
*  HTML block:    REPORT                                                    *
*  Description: Queries the EMPLOYEE table to obtain additional information *
*               about an individual employee                                *
****************************************************************************%}
%HTML(REPORT) {
<html>
<head>
<title>Obtain Employee Information</title>
</head>
<body>
<h3>You selected employee name = $(emp_name)</h3>
<p>Here is the information for that employee:
<PRE>
@fname()
</PRE>
<hr><a href="input">Return to previous page</a>
</body>
</html>
%}
 
%{     End of Net.Data macro 1 %}

Figure 10. Include file

===========================================================================
%{**************************** Include File *********************************
*   FileName = sqlsamp1.hti                                                 *
*   Description:                                                            *
*     This include file provides global DEFINEs for the sqlsamp1.d2w        *
*     Net.Data macro.                                                       *
****************************************************************************%}
%define {
    emp_name   =""
    reposition = sign
    exampleTitle = "Sample Macro"
    myTable = "EMPLOYEE"
    DATABASE = "sample"
%}
 
%{     End of include file  %}

[ Top of Page | Previous Page | Next Page | Table of Contents | Index ]