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
Using Images and Other Large Objects
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
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
Figure 3. Runtime Flow of DB2 WWW Connection
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.
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.
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:
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:
You must bind each database that connects to DB2 WWW Connection. These steps complete the bind process:
Each macro can be bound to only one database. See your DB2 documentation for details about the CONNECT and BIND commands.
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".
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:
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 (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".
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.
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 |
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> %}
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 %}
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.
<A HREF="http://{web-server}/cgi-bin/db2www/{macro-file}/ {cmd}[?name=val&...]">any text</A>
FORM METHOD="{method}" ACTION="http://{web-server}/cgi-bin/ db2www/{macro-file}/{cmd}[?name=val&...]">any text
A typical URL with optional parameters looks like this:
http://www.ibm.com/cgi-bin/db2www/stdqry1.d2w/input?field=custno&field=custname
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.
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.
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.
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:
The default separator is a space, but you can specify another separator in the DEFINE section this way:
%DEFINE %LIST "|" NLISTA 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.
There are two ways to define variables: with a DEFINE declaration and with input from an HTML form.
%DEFINE varname="value-string"
For long strings that you cannot fit on one line of text, use this format:
%DEFINE{ varname={value-string on multiple lines of text %} %}
Where varname is the name you give the variable and value-string is a numeric value or character string.
<INPUT NAME="varname">or
<SELECT NAME="varname">
Where varname is the name you give the variable. These are overly simplified versions of this syntax. The value is determined from your customer's input. You can limit the input to a few values, a range of values, or allow any value, depending on your customers' needs.
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".
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.
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 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:
Replace the quotes with brackets {...%} if the values cover more than one line.
- varA= varB ? "value_1" : "value_2"
- varname= ? "value"
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 = IBMIf both values are null or undefined, the variable where_clause is null and no WHERE clause appears in SQL queries containing $(where_clause).
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.
%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.
Large objects (LOBs) are supported beginning with DB2 Version 2.1. The three types of LOBs DB2 supports are:
Attention:
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.
The voice sample is played when the application user clicks on Voice sample. Not all Web browsers support graphics and sound. Special software and hardware, such as a sound card, may be required to support the features described here.
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.
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.
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:
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:
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.
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.
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.
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:
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.
CONVERT_CODE_PAGE in DB2WWW.INI has no effect. You cannot convert code pages.
The DB2_INSTANCE variable is not used in the DB2WWW.INI file.
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.
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.
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,