Application Developer's Guide

Version 1 for AIX, HP-UX**, OS/2, OS/400, Solaris**, Windows NT**, MVS, and VM

Before using this information and the product it supports, be sure to read the general information. Not all features and syntax described in this document applies to all platforms. See Platform Considerations for platform-specific information.


Introduction to DB2 World Wide Web Connection

Keeping Your Assets Secure

DB2 WWW Connection Macros

  • Define Section
  • HTML Input Section
  • SQL Section
  • HTML Report Section
  • Comments
  • Invoking DB2 WWW

  • Anchor References
  • HTML Forms
  • DB2 WWW Connection Variables

  • Special Variables
  • Defining Variables
  • Variable Types
  • Using Images and Other Large Objects

    Using External Programs

    Macro Examples

  • Example 1: Report Formatting
  • Example 2: Embedding Links to Other Macro Files
  • Example 3: Conditional, List, and Hidden Variables
  • Platform Considerations

  • Windows NT**
  • OS/2
  • MVS
  • OS/400
  • VM

  • Introduction to DB2 World Wide Web Connection

    DB2 World Wide Web Connection gives you the full power of HTML and the versatility of SQL to access data on DB2 databases (and other databases with DataJoiner) from anywhere on the Internet as shown in Figure 1.


    Figure 1. DB2 WWW Connection Overview


    This document explains how to use DB2 World Wide Web Connection to make your database information available through the Internet. Examples show different ways of implementing the features of DB2 WWW Connection. To understand the concepts discussed here, you should understand the basics of how your Web server works, how to use HTML forms to get data, and how to create a basic SQL query. You can get more information on DB2 World Wide Web Connection, including a list of frequently asked questions, a demo, and the most recent copy of this document at:

    http://www.software.ibm.com/data/db2/db2wgafs.html

    The following URLs are external to IBM and are subject to change by their respective owners. If you are unfamiliar with HTML, consider looking here first:

    http://www.ncsa.uiuc.edu/General/Internet/WWW/HTMLPrimer.html

    An index of general HTML information is at:

    http://www.yahoo.com/Computers_and_Internet/Software/Data_Formats/HTML/

    and

    http://www.ncsa.uiuc.edu/SDG/Software/Mosaic/Docs/fill-out-forms/overview.html

    DB2 World Wide Web Connection is a Web server gateway to access DB2 databases and other databases using DataJoiner. An application programmer writes macros, which are stored on the Web server, letting customers query databases using HTML forms. The results of the query are displayed on the Web browser. You can use an HTML editor and a SQL query tool to help write macros, but they are not required. The completed macros reside on the Web server. The development and runtime environments are illustrated in Figure 2.


    Figure 2. DB2 WWW Connection Environment


    The process is transparent to people using the application because the DB2 WWW macros are invoked like any CGI program. To run the application, they just need to submit the completed form. Figure 3 shows how the runtime flow is controlled.

    Figure 3. Runtime Flow of DB2 WWW Connection


    A DB2 WWW macro is a file containing HTML and SQL and usually contains four or more sections:

    The SQL query is dynamically created by using variables specified in the HTML form in the SQL section. How much information people can access through DB2 WWW depends on the security features you enabled on your Web server and DB2, plus the content of the HTML form and the SQL query. "Keeping Your Assets Secure" has information about securing your data.

    To write a macro you only need to know HTML, SQL, and the few specifics of macro design discussed in this document. You are on your way to getting your information out on the Web.


    Keeping Your Assets Secure

    You can keep your assets secure with existing measures you probably already use to protect your system and data. You must decide what level of security is appropriate for your assets.

    For more information on protecting your assets, see the Internet security list of frequently asked questions (FAQ) at this Web site:

    http://www-genome.wi.mit.edu/WWW/faqs/www-security-faq.


    DB2 WWW Connection Macros

    Each DB2 WWW Connection macro contains four sections:

    Queries that do not require input, or canned queries, do not require an HTML input form. Each section in a macro begins with the prefix symbol % (for example, %SQL{) and ends with %}.

    See "Invoking DB2 WWW" for information about invoking DB2 World Wide Web macros. This section describes the syntax and use of macro declarations.

    You must have the initialization file in the HTML root directory of your Web server. The file is DB2WWW.INI on most platforms and contains information about your system setup. Because the content of the initialization file varies on different platforms, see Platform Considerations for specifics. The file may include these lines:

    Modify the initialization file if you change data stored in the file, like the macro file path.

    Many Web servers let you run on multiple ports. Each port can have its own initialization file, but each must be associated with the service port of the Web server.

    You can have multiple subdirectories below MACRO_PATH to organize the macros or control who can access them. For example, you can have MACRO_PATH C:\DB2WWW\MACRO containing these subdirectories:

  • C:\DB2WWW\MACRO\CATALOG
  • C:\DB2WWW\MACRO\ORDERS
  • C:\DB2WWW\MACRO\SERVICE

    You must bind each database that connects to DB2 WWW Connection. These steps complete the bind process:

    1. Change the directory to cgi-bin, which contains DB2WWW.EXE and DB2SQL.BND.
    2. Connect to the database
    3. Bind the database
    4. Repeat the connect and bind steps for each database.

    Each macro can be bound to only one database. See your DB2 documentation for details about the CONNECT and BIND commands.

    Define Section

    This section is for defining the database name and other variables used in the macro. You must always define the variable DATABASE for each macro that accesses a database. You can define all the different types of variables supported by DB2 WWW. The different types are described in "Variable Types". There is a one-line and multiple-line syntax:

    
       %DEFINE varname="value"
    
     
    
    and
    
     
    
       %DEFINE{
    
       varname1="value1"
    
       varname2="value2"
    
       %}
    
    

    The value specified can be a numeric value or a character string. The simplest variable statement is like this:

    
       %DEFINE DATABASE="pricelist"
    
    
    Longer strings or values can be placed on multiple lines this way:
    
       %DEFINE{
    
               LngTitle={DB2 World Wide Web Connection:
    
                         Application Developer's Guide %}
    
       %}
    
    

    More information is in "Defining Variables".

    HTML Input Section

    The HTML input section contains the HTML form where customers can specify information they want from the database by entering values in the form using their Web browsers. Input is dynamically placed in the SQL query. DB2 WWW Connection macros do not require an HTML input section for simple queries.

    
       %HTML_INPUT{
    
       any valid html text
    
       on multiple lines
    
       %}
    
    

    In this example, a customer can see what new CDs are available from a music retailer since a period of time specified in the HTML form. The form calls the report section of this macro.

    
       %HTML_INPUT{
    
       <FORM METHOD="post"
    
             ACTION="/cgi-bin/db2www/example1.d2w/report">
    
       See new CD titles for the last
    
       <SELECT NAME="newcd">
    
       <OPTION> 1
    
       <OPTION selected> 2
    
       <OPTION> 3
    
       <OPTION> 4
    
       <OPTION> 5
    
       <OPTION> 6
    
       </SELECT>
    
       months.
    
       </FORM>
    
       %}
    
    
    Here is how the form appears on the Web browser:

    Figure 4. The CD Lookup Form


    See new CD titles for the last months.

    Clicking on the arrows scrolls through the options. The form input is passed to the report section of the macro, where it is incorporated into a SQL query.

    SQL Section

    You can have multiple SQL sections, but each section can contain only one SQL command with optional report and message declarations. Optionally, you can name one or more sections to call it from anywhere in the HTML report using the section name. SQL_REPORT and SQL_MESSAGE are optional sub-sections that can contain any HTML.
    
       %SQL (sql-section-name) any SQL on one line
    
     
    
    or
    
     
    
       %SQL (sql-section-name){
    
       Any SQL
    
       on multiple lines.
    
     
    
       %SQL_REPORT{
    
       Any valid header HTML or column variable names
    
       returned from the query.
    
       %ROW{
    
       Any valid HTML with special variables
    
       to display once for each row returned.
    
       %}
    
       Any valid HTML footer HTML.
    
       %}
    
     
    
       %SQL_MESSAGE{
    
       SQLCODE: "warning message" : exit or continue
    
       SQLCODE: "warning message" : exit or continue
    
       -SQLCODE: "error message"
    
       -SQLCODE: "error message"
    
       default: "default message"
    
       %}
    
       %}
    
    

    The SQL in a section is executed when it is called by %EXEC_SQL in the HTML report section. See "HTML Report Section" for information on making these calls.

    If an error or warning occurs in a SQL command, the execution terminates and a return code is given. You must include the negative sign (-) for negative return codes. The warning or error message can be any HTML text, including links to other URLs. You must decide if you want the application to continue after receiving a warning message from a SQL command. Information dealing with these issues is in "SQL Message Subsection". This example returns a list of all products in PRODTABLE and orders them using a variable specified through an HTML form in the HTML input section:

    
       %SQL(prodList){
    
       SELECT MODNO, MANUF, COST FROM DB2USER.PRODTABLE
    
       ORDER BY $(ordby)
    
       %}
    
    
    Reminder: Some versions of DB2 do not support SQL statements containing tabs or carriage returns (for example, DB2 for OS/2 Version 1.2 and DB2 for AIX Version 1.2).

    You can set a transaction scope for the SQL query in the DEFINE section using the special variable, TRANSACTION_SCOPE as described in "Special Variables".

    SQL Report Subsection

    This subsection gives you the ability to customize the query output using HTML formatting. If you have no SQL report subsection, a default table is displayed with column names at the top.

    All text and graphics before the %ROW declaration is header information and is displayed before any information from the SQL query. Following the SQL query processing, the column names are placed in special variables Ni, N_column-name, and NLIST.

    The ROW subsection contains information displayed once for each row returned by the SQL query.

    Information, including text and graphics, following the ROW subsection is footer information and is displayed once after all rows are displayed. See "Special Variables" for information on variables named here and other variables useful in report formatting.

    SQL Message Subsection

    This subsection allows you to customize error and warning messages from SQL commands. If you place this declaration inside a SQL section, it is local only to the SQL command in that section. If it is outside of all SQL sections, it is global to the entire macro.

    You create a table of SQL codes and specify the information to display following each SQL code. The default error message is shown when a SQL code not in the declaration is returned by the special variable SQL_CODE. For positive SQL codes, you have the option of exiting or continuing. This table shows how different conditions are handled:

    Table 1. Results following a SQL warning or error
    SQL Return Code Local or Global SQL Messages Declaration Exists No Local or Global SQL Messages Declaration Exists
    Positive Warning displayed, processing ends or continues 1 DB2 message displayed, processing ends
    Negative Error displayed for return code if defined, otherwise the default error message is displayed. Processing ends. 2 DB2 message displayed, processing ends
    Notes:

    1. Specify in the SQL message declaration whether to end or continue.
    2. When a negative return code is received, DB2 WWW Connection looks for a locally-defined error message, then for a globally-defined error message. If no error message is found for the return code, DB2 WWW Connection looks for a local default message, then a global default message.

    HTML Report Section

    This section is where you call the SQL query. The section is executed when DB2 World Wide Web is started in the report mode, often from the HTML input section of the macro.

    
       %HTML_REPORT{
    
       any valid HTML text
    
       %EXEC_SQL(SQL section name)
    
       any valid HTML text
    
       %EXEC_SQL(SQL section name)
    
       any valid HTML text
    
       .
    
       .
    
       .
    
       %EXEC_SQL(SQL section name)
    
       any valid HTML text
    
       %}
    
    
    You can specify any HTML and include any variables from the DEFINE section in the HTML code. Use input from the HTML form to override variables in the %DEFINE section. When an %EXEC_SQL line is encountered, the SQL section matching the name or defined variable is called. Using a variable for the SQL section name is an easy way to allow customers to select a query to perform.

    If you do not specify a section name, all unnamed SQL sections are executed in the order they appear in the macro.

    Here is a simple example of what an HTML report section might look like. You can define the variable query in the DEFINE section, or have the application user specify a value in the input section.

    
       %HTML_REPORT{
    
       <HEADER>
    
       <TITLE>Database query results</TITLE>
    
       </HEADER>
    
       <IMG SRC="gifs/logo.gif" ALIGN=MIDDLE>
    
       <BR>
    
       %EXEC_SQL ($(query))
    
       <HR>
    
       <A HREF="/cgi-bin/db2www/query.d2w/input">Submit another query</A>
    
       <br>
    
       <A HREF="www.celdial.com">Home page</A>
    
       %}
    
    

    Comments

    You can place comments in your macros easily. Comments must be kept separate from other sections in the macro.

    
       %{ single or
    
       multiple lines of text %}
    
    

    Comments are usually about the macro or its contents. The terminating symbols %} are required. This example contains information about updates to the macro:

    
       %{
    
       Date     Change                                    Name
    
       ---------------------------------------------------------------
    
       17Jul95  Added new security features               Rachel G
    
        5Sep95  Added a link to company homepage. Added
    
                an option to search by model number on
    
                everything we sell                        Dave O
    
        2Oct95  Updated logo on page headings             Mary M
    
       %}
    
    

    Invoking DB2 WWW

    DB2 WWW Connection is a common gateway interface (CGI) program that you can invoke from an HTML anchor reference or form.

    If you plan to run queries that take a long time to complete, consider changing your default timeout for CGI programs. Usually the default timeout is 5 minutes, which is enough for most queries. See your Web server's documentation for specific instructions.

    {web-server}
    This is the name of the web server as defined by the system administrator. For example, www.ibm.com. If the macro exists locally, you only need the relative URL, omitting http://www.ibm.com/.
    {macro-file}
    This is the name of the macro file as defined by the DB2 WWW Connection application developer. For example, salesfrm.d2w.
    {cmd}
    You can specify either input or report. If input, the HTML in the HTML_INPUT section of the macro file is displayed. People accessing the form through a Web browser can fill in values or select options. If report, the HTML in the report section is displayed and specified SQL queries are called from %EXEC_SQL statements.
    {method}
    You can specify either get or post, which are specified in HTML 2.0. Using the get method is discouraged because of its limitations.
    ?name=val&...
    These are optional parameters that may be passed in your applications. For example, you can pass the user ID so it doesn't have to be entered multiple times or you can pass the name of a macro that calls another macro so you can back out. This can also be used to call other DB2 WWW macros based on the results of a query.

    A typical URL with optional parameters looks like this:

    
       http://www.ibm.com/cgi-bin/db2www/stdqry1.d2w/input?field=custno&field=custname
    
    

    Anchor References

    The anchor references are commonly called hyperlinks or simply links. Using links, you specify the macro name and the cmd to invoke a SQL query. You can use this method to make queries available that require no HTML form input, sometimes referred to as canned queries.

    You can place text or graphics in the link. When people click on the linked text or graphic, they invoke the macro. For example:

    This is an example of a canned query that you can invoke from a Web page using a link like this:

    
       <A href="http://www.ibm.com/cgi-bin/db2www/monlstA.d2w/report">
    
       List all monitors</A>
    
    
    The link calls this macro:
    
       %DEFINE DATABASE="MNS95"
    
     
    
       %SQL{
    
       SELECT MODNO, COST, DESCRIP FROM DB2USER.EQPTABLE
    
       WHERE TYPE='MONITOR'
    
       %}
    
     
    
       %HTML_REPORT{
    
       %EXEC_SQL
    
       %}
    
    

    This query returns a table of all the monitors sold, including the model number, cost, and description for each one. You can use HTML to format the output as you like in a SQL report subsection. This macro does not need an HTML input section because no variables are used in the query.

    HTML Forms

    The form method exploits the power of HTML forms so your customers can get exactly the information they want. You create the form with any plain-text editor, although there are several good HTML editors available commercially. A form can be as simple as a short list of items or as complex as you need, including boolean operators and beginning and ending dates. You can also use your Web browser to look at HTML code in forms you find on the WWW with your Web browser's view feature to see how others use forms.

    "Introduction to DB2 World Wide Web Connection" lists some places on the WWW where you can learn more about HTML and writing HTML forms.

    For most transactions you will find it useful to call an input section before making the query. This example is similar to the monitor list example above, but allows the application user to select which product to view. The macro can be called from a URL like this:

    
       <A href="http://www.ibm.com/cgi-bin/db2www/equiplst.d2w/input">
    
       List of hardware</A>
    
    

    Here is the macro the link calls:

    
       %DEFINE DATABASE="MNS95"
    
     
    
       %HTML_INPUT{
    
       <H1>Hardware Query Form</H1>
    
       <FORM METHOD="POST"
    
             ACTION="/cgi-bin/db2www/equiplst.d2w/report">
    
       <dt>What hardware do you want to list?
    
       <dd><input type="radio" name="hdware" value="MON" checked>Monitors
    
       <dd><input type="radio" name="hdware" value="PNT">Pointing devices
    
       <dd><input type="radio" name="hdware" value="PRT">Printers
    
       <dd><input type="radio" name="hdware" value="SCN">Scanners
    
       </dl>
    
       <input type=submit value=Submit>
    
       </FORM>
    
       %}
    
     
    
       %SQL{
    
       SELECT MODNO, COST, DESCRIP FROM DB2USER.EQPTABLE
    
       WHERE TYPE=$(hdware)
    
       %SQL_REPORT{
    
       <B>Here is the list you requested:</B>
    
       <TABLE>
    
       <TR>
    
       <TD>$(N1)</TD>
    
       <TD>$(N2)</TD>
    
       <TD>$(N3)</TD>
    
       %ROW{
    
       <TR>
    
       <TD>$(V1)</TD>
    
       <TD>$(V2)</TD>
    
       <TD>$(V3)</TD>
    
       %}
    
       </TABLE>
    
       %}
    
       %}
    
     
    
       %HTML_REPORT{
    
       %EXEC_SQL
    
       %}
    
    

    The form appears on the Web browser as shown in Figure 5.

    Figure 5. Hardware Query Form


    Hardware Query Form

    What hardware do you want to list?
    Monitors
    Pointing devices
    Printers
    Scanners


    After a selection is made and the Submit button pressed, the HTML report section is called. The HTML report section calls the SQL section and the query is processed, substituting the value specified in the form for $(hdware). The query results are displayed according to the HTML in the SQL report subsection.

    DB2 WWW Connection Variables

    DB2 WWW Connection macros pass variables from HTML documents to SQL and back. You define most variables, but some have special meaning in DB2 WWW Connection macros.

    Special Variables

    The power of DB2 WWW Connection is its ability to pass variables from HTML to SQL and back. Variables may be specified by you in the macro or by a customer through your HTML form. Define special variables the same as any other variable. Some variables are system-defined and cannot be changed. These variables have special meaning in DB2 WWW Connection macros:

    Variables you can change in DB2 WWW:

    DATABASE
    The name of the database to be accessed. You must specify a database in each macro that accesses a database. This variable is not used on all systems.
    LOGIN
    The user ID to access the database. This variable is not used on all systems.
    PASSWORD
    The database password associated with the LOGIN variable. This variable is not used on all systems.
    SHOWSQL
    A flag to show the SQL command on the report form. This defaults to null to not display the SQL query. To display the SQL to the application user, set this variable to yes. Set it to null or no to hide the SQL.
    DB_CASE
    The case conversion for input to SQL commands. The default is null. Specifying UPPER converts all text to upper case. Specifying LOWER converts all text to lower case.
    RPT_MAX_ROWS
    The maximum number of rows to display on the Web browser. This variable is undefined by default, giving no limit to the number of rows a SQL query can return. The values 0, ALL, or all also give no limit to the number of rows returned.
    TRANSACTION_SCOPE
    The action for SQL to take when errors are encountered. The default, multiple, means an unsuccessful SQL statement causes all SQL statements already completed in the macro to roll back. Specifying single means a commit is issued after each successfully completed SQL statement. The variable TRANSACTION_SCOPE cannot be changed with input from HTML.
    ALIGN
    If this variable is undefined, the report variables Vi and Ni are returned with no leading or trailing spaces. This is useful in imbedding query results in HTML anchors or form action. If this variable is set to yes, the report variables are padded with spaces so they align properly in the table of query results.

    Variables set by the system:

    N1, ..., Ni
    The name of the columns in the report. These variables are only valid within the SQL report section.
    V1, ..., Vi
    The values for each field of a row returned by a SQL query. They are only valid inside the ROW section. The values change as each row is retrieved.
    N_column-name
    The name of the specified column name. If the column name does not exist, this variable is not defined. For example, the value of $(N_ZIP) is ZIP.
    V_column-name
    The value for the specified column name for the current row. This variable is not defined if the column name does not exist. For example, the value of $(V_ZIP) might be 98109. A query containing two columns with the same name might give unpredictable results. Consider using an AS clause in your SQL to rename duplicate column names.
    NLIST
    This is a special list variable that contains all the column names from the result table.

    The default separator is a space, but you can specify another separator in the DEFINE section this way:

    
       %DEFINE %LIST "|" NLIST
    
    
    A query returning names and phone numbers might have $(NLIST) with this string: LAST FIRST AREA NUMBER. This variable is most helpful creating tables in HTML 3.0.
    VLIST
    The field values for each row of the result table. The default separator for the names is a space, but you can specify another separator in the DEFINE section. For example:
    
    
    A query returning names and phone numbers might have
    
    $(VLIST) for the first row with this value:
    
    ANH   TERESA   408  555 9876.
    
    This is most useful creating tables in HTML 3.0.
    
    
    ROW_NUM
    The current number of rows retrieved from the query. When the last row is returned, this variable contains the total number of rows returned.
    NUM_COLUMNS
    The number of columns returned by the SQL query.
    SQL_CODE
    Contains the SQL warning or error from the SQL query. Successful SQL queries result in 0.

    Defining Variables

    There are two ways to define variables: with a DEFINE declaration and with input from an HTML form.

    An INPUT or SELECT overrides variables set by DEFINE in the macro. In DB2 WWW Connection macros, refer to variables with the variable name inside $( and ), for example, $(prod_name) and $(N1). Circular references (or cycles) are not allowed. For example, the DEFINE declarations below are not allowed and result in an error when the variable is referenced and the final values are evaluated:

    
       %DEFINE a="$(b)"
    
       %DEFINE b="$(a)"
    
    
    Variables must begin with a letter or underscore and can contain any alphanumeric characters or an underscore. All string variables are case sensitive, except N_colname and V_colname. Throughout the macro, refer to variables by placing them in parentheses preceded by a dollar sign, as shown in the next example.
    
       %DEFINE var1="orders"
    
       %DEFINE var2="$(var1).html"
    
    
    Include quotes in a string by using two quotes consecutively. Two consecutive quotes alone is equal to a null string.
    
       %DEFINE HI="say ""hello"""
    
       %DEFINE empty=""
    
    
    When displayed, the variable HI reads say "hello". The variable empty is null. Alternatively, you can use this syntax:
    
       %DEFINE{
    
                 HI={say "hello"%}
    
       %}
    
    

    You can even use variables as part of your HTML. For example, if you have defined the variable homeurl like this:

    
       %DEFINE homeurl="http://www.ibm.com/"
    
    
    You can refer to the homepage as $(homeURL) and create an anchor reference this way:
    
       <A href="$(homeURL)">Home page</A>
    
    

    You can invoke other programs as explained in "Using External Programs".

    Variable Types

    You can use the different variable types to meet your needs. You may choose to use different variable types together, like list and conditional variables.

    List Variables

    One use of list variables help you construct a SQL query with multiple items like those found in some WHERE or HAVING clauses. The syntax for a list variable is:

    
       %LIST " value_separator " variable_name
    
             variable_name="value1"
    
             variable_name="value2"
    
             .
    
             .
    
             .
    
             variable_name="valuen"
    
     
    
    

    Another use of list variables is to let application users to select different columns from a table. Both uses of list variables are in the sample macro file urlquery.d2w, which is installed during DB2 WWW Connection installation. The sample macro has this list variable definition:

    
       %LIST ", " DBFIELDS
    
       DBFIELDS = "id"
    
       DBFIELDS = "url"
    
    

    The application user can add description and title to the list variable through the HTML form. The variable DBFIELDS is evaluated as:

    
       id, url
    
    

    The list variable must be defined in the DEFINE section with the associated values. The default list separator is the comma (,). Blanks are significant. Usually you want to have a space on both sides of the value separator for the tables to look good. Some queries use boolean or mathematical operators (for example, AND, OR, or >) for the value separator. An example of list variables used with conditional variables is in "Conditional Variables".

    Conditional Variables

    Conditional variables determine if a string exists and is not null. If it does exist, it is given the first value; otherwise it is given the second value. Conditional variables have two main forms:

    1. varA= varB ? "value_1" : "value_2"
    2. varname= ? "value"
    Replace the quotes with brackets {...%} if the values cover more than one line.

    In case 1, if varB is defined, varA=value_1 otherwise varA=value_2. In case 2, varname is null if value is null, otherwise varname is set to value. In the example below, if any of the variables are null, x is null. Otherwise x is the value of {$(v1) string1 $(v2) string2 $(v3) string3}

    
       x = ? "$(v1) string1 $(v2) string2 $(v3) string3"
    
    

    List and conditional variables are most effective when used together. This example shows how to set up a WHERE list in the DEFINE section.

    
       %DEFINE{
    
       %list " AND " where_list
    
       where_list   =   ? "custid = $(cust_inp)"
    
       where_list   =   ? "product_name LIKE '$(prod_inp)%'"
    
       where_clause =   ? "WHERE (where_list)"
    
       %}
    
     
    
       %SQL select * from prodtable $(where_clause)
    
    

    The variables cust_inp and prod_inp are HTML input variables passed from CGI, usually from an HTML_INPUT section. The variable where_list is a list variable made of two conditional statements, each statement containing a variable from CGI. If CGI returns values for both variables (cust_inp and prod_inp), for example, IBM and 755C, the where_clause is:

    
       WHERE custid = IBM AND procuct_name LIKE '755C%
    
    
    If either variable (cust_inp or prod_inp), is null or not defined, the WHERE clause changes to omit the null value. For example, if prod_inp is null, the result is:
    
       WHERE custid = IBM
    
    
    If both values are null or undefined, the variable where_clause is null and no WHERE clause appears in SQL queries containing $(where_clause).

    Hidden Variables

    For additional security, consider using hidden variables to conceal your database's internal structure from people who choose to view your HTML source with their Web browser.

    1. Define a variable for each string you want to hide. Put the DEFINE section for these variables after the HTML section where you reference the variables and before the HTML report section where they are used.

    2. In the HTML input section where the variables are referenced, use double dollar signs instead of a single dollar to reference the variables. For example, $$(X) instead of $(X).
    
       %HTML_INPUT{
    
       <FORM ...>
    
       <P>Select fields to view:
    
       <SELECT NAME="Field">
    
       <OPTION VALUE="$$(name)"> Name
    
       <OPTION VALUE="$$(addr)"> Address
    
       .
    
       .
    
       .
    
       </FORM>
    
       %}
    
     
    
       %DEFINE{
    
       name="customer.name"
    
       addr="customer.address"
    
       %}
    
     
    
       %SQL SELECT $(Field) FROM service.customer
    
       .
    
       .
    
       .
    
    

    When the HTML form is displayed on your customer's Web browser, $$(name) and $$(addr) is replaced with $(name) and $(address) respectively, so the actual table and column names never appear on the HTML form and there is no way to tell that the true variable names are hidden. When the customer submits the form, the HTML report section is called. When %EXEC_SQL calls the SQL section, $(Field) is substituted in the SQL section with customer.name or customer.addr in the SQL query.


    Using Images and Other Large Objects

    Large objects (LOBs) are supported beginning with DB2 Version 2.1. The three types of LOBs DB2 supports are:

    When a query returns a LOB, DB2 WWW saves it in a temporary file in a directory called tmplobs. Inserts and updates of large objects are not supported. Consider system limitations when using LOBs because they can quickly consume resources. Some LOBs, like audio files, require special hardware and software. The temporary directory is created in the Web server's HTML root directory.

    Attention: The temporary files should be discarded periodically because DB2 WWW does not do it. This will be fixed for the next release.

    The first example below displays a picture inline. In the second example, the application user must click on the file name to invoke the viewer.

    
       <IMG SCR="/tmplobs/filename">
    
       <A HREF="/tmplobs/filename">filename</A>
    
    

    Here is an example to show how to use LOBs in your applications:

    
       %DEFINE{
    
       docroot="/usr/lpp/internet/server_root/html"
    
       move=%exec "mv $(docroot)$(V3) $(docroot)$(V3).wav"
    
       %}
    
     
    
       %SQL{
    
       SELECT Name, IDPhoto, Voice FROM DB2USER.RepProfile
    
       %SQL_REPORT{
    
       Here are the images you selected:<P>
    
       %ROW{
    
       $(move)
    
       $(V1)<BR> <IMG SRC="$(V2)">
    
       <A HREF="$(V3).wav">Voice sample</A><P>
    
       %}
    
       %}
    
       %}
    
     
    
       %HTML_REPORT{
    
       %EXEC_SQL
    
       %}
    
    

    The query returns this in the HTML_REPORT:

    
       Kinson Yamamoto
    
       <IMG SRC="/tmplobs/p2345n1.gif">
    
       <A HREF="/tmplobs/p2345n2.wav">Voice sample</A><P>
    
       Merilee Lau
    
       <IMG SRC="/tmplobs/p2345n3.gif">
    
       <A HREF="/tmplobs/p2345n4.wav">Voice sample</A><P>
    
    

    This SQL_REPORT uses the system variables V1, V2, and V3.

    Most LOBs contain a file signature in the first few bytes, which indicates what type of information the file contains. If a LOB is recognized by DB2 WWW Connection, the extension is added to the temporary file and to the macro variable representing its name. If there is no SQL report section, CLOBs have the .txt Recognized LOBs include bitmap (.bmp), graphical image format (.gif), and tag image file format (.tif). PostScript (.ps) LOBs are recognized if stored in the database as binary large objects (BLOBs), not character large objects (CLOBs). Other file types are not recognized and no extension is added.


    Using External Programs

    The %exec statement lets you call REXX programs (or any command line executables, like DOS batch programs) with parameters passed from DB2 WWW Connection. Use the REXX ARG command to get the data from DB2 WWW Connection. The results must be standard output and are displayed on the Web browser. You cannot update variables using %exec.

    The %exec statement is allowed in these areas:

    Define the variable associated with the program in the DEFINE section like this:

    
       %DEFINE MYCALL=%EXEC "MYEXEC $(V1) $(V3)"
    
    

    The program MYEXEC is run where $(MYCALL) appears in the ROW section of the macro file using V1 and V3 as input variables. If the program ends with a return code of 0, MYCALL has a null value. Otherwise, it has the value of the error returned.


    Macro Examples

    Example 1: Report Formatting

    This example shows how you can customize report formats using special variables and HTML formatting tags. If you do not format the output, the default report table is displayed with the column names at the top of the report.

    These variables are used to return data from the SQL query:

    Variable
    Meaning
    N1, N2,..., Ni
    The names of each column in the report.
    V1, V2,..., Vi
    The values for the fields as each row is retrieved.
    CUR_ROW_NUM
    The number of rows retrieved from the query.

    This example displays the names, phone numbers, and Fax numbers from the table CustomerTbl.

    
       %SQL (custlist) SELECT Name, Phone, Fax FROM DB2USER.CustomerTbl
    
       %SQL_REPORT{
    
       <I>Phone Query Results:</I>
    
       <BR>
    
       =====================
    
       %ROW{
    
       Name: <B>$(V1)</B><BR>
    
       Phone: $(V2) Fax: $(V3)<BR>
    
       -----------------------------------------------
    
       %}
    
       Total records retrieved: $(CUR_ROW_NUM)
    
       %}
    
    

    The resulting report looks like this:

    
       Phone Query Results:
    
       ====================
    
       Name: Doen, David
    
       Phone: 422-245-1293 Fax: 422-245-7383
    
       -----------------------------------------------
    
       Name: Williamson, Jack
    
       Phone: 955-768-3489 Fax: 955-768-3974
    
       -----------------------------------------------
    
       Total records retrieved: 2
    
    

    DB2 WWW Connection generated the report by:

    1. Printing Address Query Result: once at the beginning of the report.

    2. Giving the variables $(V1), $(V2), and $(V3) the values for Name, Phone, and Fax respectively for each row as it is retrieved.

    3. Drawing a line after each row retrieved to help readability.

    4. Printing the string Total records retrieved: and the value for $(CUR_ROW_NUM) once at the end of the report.

    Alternatively, you can use the column titles returned from the query. The column titles in the CustomerTbl are Name, Phone, and Fax, so this macro has the same output as the macro above.

    
       %SQL SELECT Name, Phone, Fax FROM DB2USER.CustomerTbl
    
       %HTML_REPORT{
    
       Phone Query Results:
    
       <BR>
    
       ==================
    
       %ROW{
    
       $(N1): $(V1)<BR>
    
       $(N2): $(V2) $(N3): $(V3)<BR>
    
       -----------------------------------------------
    
       %}
    
       Total records retrieved: $(CUR_ROW_NUM)
    
       %}
    
    

    You have total control of the formatting with HTML. Here are a couple similar examples.

    
       %SQL SELECT Name, Phone, Fax FROM DB2USER.CustomerTbl
    
       %SQL_REPORT{
    
       <B>Phone Query Results:</B>
    
       <HR>
    
       %ROW{
    
       <B>$(V1)</B><BR>
    
       Phone: $(V2) Fax: $(V3)
    
       <HR>
    
       %}
    
       Total records retrieved: $(CUR_ROW_NUM)
    
       %}
    
    

    The resulting report has the names in bold type and a separator between each record. Another way to use the $(CUR_ROW_NUM) variable is to place it in the %ROW section to keep a running list of the records retrieved.

    
       %SQL SELECT Name, Phone, Fax FROM DB2USER.CustomerTbl
    
       %SQL_REPORT{
    
       <B>Phone Query Results:</B>
    
       <HR>
    
       %ROW{
    
       <B>$(CUR_ROW_NUM)</B> $(V1)<BR>
    
       Phone: $(V2) Fax: $(V3)
    
       <HR>
    
       %}
    
       %}
    
    

    The resulting report is similar to the previous example, but each name is prefaced by a number. The first records retrieved is always record 1.

    Example 2: Embedding Links to Other Macro Files

    Because of the flexible variable substitution of DB2 WWW, you can set items in the report to link to other URLs, including other HTML forms and cgi-bin programs.

    
       %DEFINE URLprefix="http://stl.ibm.com/"
    
       %HTML_INPUT{
    
       <FORM METHOD="post"
    
             ACTION="$(URLprefix)cgi-bin/db2www/example2.d2w/report">
    
       Enter name of customer for search:
    
       <INPUT Name="custname">
    
       <INPUT Type="Submit" Value="Search Now">
    
       </FORM>
    
       %}
    
       %SQL{
    
       SELECT Name, Company
    
              FROM DB2USER.CustomerTbl
    
              WHERE Name LIKE '%$(custname)%'
    
       %SQL_REPORT{
    
       <I>Here are your query results.
    
       Please click on a name for more information.</I>
    
       <P>
    
       %ROW{
    
       <P>
    
       Name: <A HREF="$(URLprefix)cgi-bin/db2www/longrpt.d2w/report?cust=$(V1)">
    
       $(V1)</A><BR>
    
       Company: $(V2)
    
       %}
    
       %}
    
       %}
    
       %HTML_REPORT{
    
       %EXEC_SQL
    
       <A HREF="$(URLprefix)/homepage.html>Go to home page</A>
    
       %}
    
    

    Invoking DB2 WWW with macro example2.d2w returns an HTML form to the application user to fill out the name of the customer to query. The query on the customer table might return a report that looks similar to this:

    
       Here are your query results. Please click on
    
       a name for more information.</I>
    
     
    
       Name: John_Williams
    
       Company: Mikrotuf Corporation
    
     
    
       Name: Jack_Williamson
    
       Company: Orakel Limited
    
       Go to home page
    
    

    The names are referenced in an HTML link. Clicking on any name calls macro longrpt.d2w:

    
       %SQL{
    
       SELECT Name, Title, Phone, Acct FROM DB2USER.CustomerTbl
    
        WHERE Name LIKE "$(cust)"
    
       %}
    
       %SQL_REPORT{
    
       Detailed customer information
    
       -----------------------------
    
       %ROW{
    
       Name=$(V1)<BR>
    
       Title=$(V2)<BR>
    
       Phone=$(V3)<BR>
    
       Acct #=$(V4)<BR>
    
       %}
    
       %}
    
    

    Clicking on the name calls the macro and DB2 WWW Connection returns the detailed information. For example, selecting John_Williams returns this:

    
       Detailed customer information
    
       -----------------------------
    
       Name=John_Williams
    
       Title=VP of Research and Development
    
       Phone=206-123-4567
    
       Acct #=F15048
    
    

    Selecting Go to home page jumps to the HTML page http://stl.ibm.com/homepage.html.

    Example 3: Conditional, List, and Hidden Variables

    This example has conditional, list, and hidden variables:

    
       %{ Macro file example 3 %}
    
       %HTML_INPUT{
    
       <FORM METHOD="post"
    
             ACTION="/cgi-bin/db2www/example2.d2w/report">
    
       Select one or more cities: <BR>
    
       <INPUT TYPE="checkbox" NAME="Conditions" VALUE="$$(cond1)"> San Jose <BR>
    
       <INPUT TYPE="checkbox" NAME="Conditions" VALUE="$$(cond2)"> New York <BR>
    
       <INPUT TYPE="submit" VALUE="Submit Query">
    
       </FORM>
    
       %}
    
       %DEFINE{
    
       DATABASE="CustomerDB"
    
       %LIST " OR " Conditions
    
       cond1="City LIKE "San Jose""
    
       cond2="City LIKE "New York""
    
       WhereClause=Conditions ? "WHERE $(Conditions)" : ""
    
       %}
    
       %SQL SELECT Name, City FROM DB2USER.CustomerTbl $(WhereClause)
    
       %HTML_REPORT{
    
       %EXEC_SQL
    
       %}
    
    

    The macro file above contains the following items:

    The HTML form appears on the Web browser as shown in Figure 6..

    Figure 6. An HTML Form Using Conditional, List, and Hidden Variables.


    Select one or more cities:
    San Jose
    New York

    The value of $(WhereClause) depends on which boxes are selected on the form. There are 4 possible combinations, which result in 4 different WHERE clauses:


    Platform Considerations

    Each platform has unique characteristics that might require special considerations. Before developing your application, please read the considerations for your platform of DB2 WWW Connection.

    Windows NT(**)

    CONVERT_CODE_PAGE in DB2WWW.INI has no effect. You cannot convert code pages.

    OS/2

    The DB2_INSTANCE variable is not used in the DB2WWW.INI file.

    MVS

    MVS has no visual tools to assist you buildng SQL queries.

    These variables are not used in MVS:

    You can ignore the DATABASE variable where it appears in examples.

    MVS does not use LOGIN and PASSWORD authentication. Instead, authorization is based on GRANTs of privileges to SQL IDs. The SQL ID for DB2 WWW is its MVS userid. The MVS user ID is established from parameters in the Internet Connection Server configuration file (for example, /etc/hpptd.conf). The user ID is derived from the Protect directive or the User ID subdirective.

    Generally, DB2 for MVS table names are known across the DB2 subsystem and are qualified (implicitly or explicitly) by a creator name. If the creator name is not provided explicitly as part of the table name in an SQL statement, DB2 uses the SQL ID as the qualifier. Some of the examples do not have these table qualifiers.

    Three part table names have a high level qualifier for the location name. You can use three part table names to reference tables on other DB2 subsystems.

    The only valid lines in the initialization file are:

    There is no equivalent for LOBs in MVS and no support in DB2 WWW for MVS. Instead of storing images directly in DB2, an alternative is to store an image as a GIF file on the Web server, store the GIF file name in DB2, and display the image using the IMG tag with the retrieved GIF file name as SRC. For example:

    
    <IMG SRC="$(picture)">
    
    

    Where $(picture) is the variable DB2 returns that contains the GIF file name.

    OS/400

    These variables are not used in OS/400:

    Ignore the DATABASE variable in examples. OS/400 uses a collection name instead to identify the database. You can qualify table names with the appropriate collection names by hard coding them in the SQL statements or by using a variable placed in front of the table name, as this example shows:

    
    %DEFINE COLLECTION="WWWDATA"
    
    %SQL (custlist) SELECT Name, Phone, Fax FROM $(COLLECTION).CustomerTbl
    
    

    The COLLECTION variable is not a special variable. You can use any variable name you choose, but we recommend not using DATABASE to ensure compatibility for future releases of DB2 WWW Connection.

    DB2 for OS/400 does not use LOGIN and PASSWORD authentication. Instead, authorization is based on granting authority to the OS/400 User Profile that accesses the database collection, which is QTMHHTP1. The Web server uses this User Profile to run CGI programs like DB2 WWW.

    Invoking DB2 WWW Connection as described in this document requires special TCP/IP configuration on your OS/400 system. See AS/400 Advanced Series, TCP/IP Configuration and Reference (SC41-3420) for details.

    These lines are not used in the initialization file:

    There is no equivalent for LOBs in OS/400 and no support in DB2 WWW for OS/400. Instead of storing images directly in DB2, an alternative is to store an image as a GIF file on the Web server, store the GIF file name in DB2, and display the image using the IMG tag with the retrieved GIF file name as SRC. For example:

    
    <IMG SRC="$(picture)">
    
    

    Where $(picture) is the variable DB2 returns that contains the GIF file name.

    VM

    These variables are required in VM:

    DATABASE supplies the VM server name, and LOGIN and PASSWORD are used in the SQL CONNECT which covers the transaction.

    The only valid line in the initialization file is:

    There is no equivalent for LOBs in VM and no support in DB2 WWW for VM. Instead of storing images directly in DB2, an alternative is to store an image as a GIF file on the Web server, store the GIF file name in DB2, and display the image using the IMG tag with the retrieved GIF file name as SRC. For example:

    
    <IMG SRC="$(picture)">
    
    

    Where $(picture) is the variable DB2 returns that contains the GIF file name.

    The %exec facility in VM does not route standard output from the command being executed to the browser as HTML output. If VM:Webserver is being used, the CGI WRITE subcommand can be used from a REXX EXEC executed by %exec, to send output to the browser,


    [ IBM home page | Order | Search | Contact IBM | Help | (C) | (TM) ]