You can use the following types of variable in your macros.
If you assign strings to variables that are defined a certain way by Net.Data, such as ENVVAR, LIST, condition list variables, the variable no longer behaves in the defined way. In other words, the variable becomes a simple variable, containing a string.
See Net.Data Reference for syntax and examples of each variable.
Conditional variables let you define a conditional value for a variable by using a method similar to an IF, THEN construct. When defining the conditional variable, you can specify two possible variable values. If the first variable you reference exists, the conditional variable gets the first value; otherwise the conditional variable gets the second value. The syntax for a conditional variable is:
varA = varB ? "value_1" : "value_2"
If varB is defined, varA="value_1", otherwise varA="value_2". This is equivalent to using an IF block, as in the following example:
%IF ($(varB))
varA = "value_1"
%ELSE
varA = "value_2"
%ENDIF
See List Variables for an example of using conditional variables with list variables.
You can reference environment variables that the Web server makes available to the process or thread that is processing your Net.Data request. When the ENVVAR variable is referenced, Net.Data returns the current value of the environment variable by the same name.
The syntax for defining environment variables is:
%DEFINE var=%ENVVAR
Where var is the name of the environment variable being defined.
For example, the variable SERVER_NAME can be defined as environment variable:
%DEFINE SERVER_NAME=%ENVVAR
And then referenced:
The server is $(SERVER_NAME)
The output looks like this:
The server is www.software.ibm.com
See Net.Data Reference for more information about the ENVVAR statement.
You can invoke other programs from a variable reference using executable variables.
Define executable variables in a Net.Data macro using the EXEC language construct in the DEFINE block. For more information about the EXEC language element, see the language constructs chapter in the Net.Data Reference. In the following example, the variable runit is defined to execute the executable program testProg:
%DEFINE runit=%EXEC "testProg"runit becomes an executable variable.
Net.Data runs the executable program when it encounters a valid variable reference in a Net.Data macro. For example, the program testProg is executed when a valid variable reference is made to the variable runit in a Net.Data macro.
A simple method is to reference an executable variable from another variable definition. The following example demonstrates this method. The variable date is defined as an executable variable and dateRpt contains a reference to the executable variable.
%DEFINE date=%EXEC "date" %DEFINE dateRpt="Today is $(date)"
Wherever $(dateRpt) appears in the Net.Data macro, Net.Data searches for the executable program date, and when it locates it, returns:
Today is Tue 11-07-1999
When Net.Data encounters an executable variable in a macro, it looks for the referenced executable program using the following method:
Restriction: Do not set an executable variable to the value of the output of the executable program it calls. In the previous example, the value of the variable date is NULL. If you use this variable in a DTW_ASSIGN function call to assign its value to another variable, the value of the new variable after the assignment is NULL also. The only purpose of an executable variable is to invoke the program it defines.
You can also pass parameters to the program to be executed by specifying them with the program name on the variable definition. In this example, the values of distance and time are passed to the program calcMPH.
%DEFINE mph=%EXEC "calcMPH $(distance) $(time)"
This next example returns the system date as part of the report:
%DEFINE database="celdial"
%DEFINE tstamp=%EXEC "date"
%FUNCTION(DTW_SQL) myQuery() {
SELECT CUSTNO, CUSTNAME from dist1.customer
%REPORT{
%ROW{
<A HREF="/cgi-bin/db2www/exmp.d2w/report?value1=$(V1)&value2=$(V2)">
$(V1) $(V2) </A> <BR>
%}
%}
%}
%HTML(report){
<H1>Report made: $(tstamp) </H1>
@myQuery()
%}
Each report displays the date for easy tracking. This example also puts the customer number and name in a link for another Net.Data macro. Clicking on any customer in the report calls the exmp.d2w Net.Data macro, passing the customer number and name to the Net.Data macro.
You can use hidden variables to conceal the actual name of a variable from application users who view your Web page source with their Web browser. To define a hidden variable:
%HTML(INPUT) {
<FORM ...>
<P>Select fields to view:
shanghai<SELECT NAME="Field">
<OPTION VALUE="$$(name)"> Name
<OPTION VALUE="$$(addr)"> Address
...
</FORM>
%}
%DEFINE {
name="customer.name"
addr="customer.address"
%}
%FUNCTION(DTW_SQL) mySelect() {
SELECT $(Field) FROM customer
%}
...
When a Web browser displays the HTML form, $$(name) and $$(addr) are replaced with $(name) and $(addr) respectively, so the actual table and column names never appear on the HTML form. Application users cannot tell that the true variable names are hidden. When the user submits the form, the HTML(REPORT) block is called. When @mySelect() calls the FUNCTION block, $(Field) is substituted in the SQL statement with customer.name or customer.addr in the SQL query.
Use list variables to build a delimited string of values. They are particularly useful in helping you construct an 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
Recommendation: The blanks are significant. Insert a space before and after the value separator for most cases. Most queries use Boolean or mathematical operators (for example, AND, OR, or >) for the value separator. The following example illustrates the use of conditional, hidden, and list variables:
%HTML(INPUT) {
<FORM METHOD="POST" ACTION="/cgi-bin/db2www/example2.d2w/report">
<H2>Select one or more cities:</H2>
<INPUT TYPE="checkbox" NAME="conditions" VALUE="$$(cond1)">Sao Paolo<BR>
<INPUT TYPE="checkbox" NAME="conditions" VALUE="$$(cond2)">Seattle<BR>
<INPUT TYPE="checkbox" NAME="conditions" VALUE="$$(cond3)">Shanghai<BR>
<INPUT TYPE="submit" VALUE="Submit Query">
</FORM>
%}
%DEFINE{
DATABASE="custcity"
%LIST " OR " conditions
cond1="cond1='Sao Paolo'"
cond2="cond2='Seattle'"
cond3="cond3='Shanghai'"
whereClause= ? "WHERE $(conditions)" : ""
%}
%FUNCTION(DTW_SQL) mySelect(){
SELECT name, city FROM citylist
$(whereClause)
%}
%HTML(REPORT){
@mySelect()
%}
In the HTML form, if no boxes are checked, conditions is NULL, so whereClause is also NULL in the query. Otherwise, whereClause has the selected values separated by OR. For example, if all three cities are selected, the SQL query is:
SELECT name, city FROM citylist WHERE cond1='Sao Paolo' OR cond2='Seattle' OR cond3='Shanghai'
This example shows that Seattle is selected, which results in this SQL query:
SELECT name, city FROM citylist WHERE cond1='Seattle'
The table variable defines a collection of related data. It contains a set of rows and columns including a row of column headers. A table is defined in the Net.Data macro as in the following statement:
%DEFINE myTable=%TABLE(30)
The number following %TABLE is the limit on the number of rows that this table variable can contain. To specify a table with no limit on the number of rows, use the default or specify ALL, as shown in these examples:
%DEFINE myTable2=%TABLE %DEFINE myTable3=%TABLE(ALL)
When you define a table, it has zero rows and zero columns. The only way you can populate a table with values is by passing it as an OUT or INOUT parameter to a function or by using the built-in table functions provided by Net.Data. The DTW_SQL language environment automatically puts the results of a SELECT statement into a table.
For non-database language environments, such as DTW_REXX or DTW_PERL, the language environment is also responsible for setting table values. However, the language environment script or program defines the table values cell-by-cell. See Using Language Environments for more information about how language environments use table variables.
You can pass a table between functions by referring to the table variable name. The individual elements of the table can be referred to in a REPORT block of a function or by using the Net.Data table functions. See Table Processing Variables for accessing individual elements in a table within a REPORT block, and see Table Functions for accessing individual elements of a table using a table function. Table variables are usually populated with values in an SQL function, and then used as input to a report, either in the SQL function or in another function after being passed to that function as a parameter. You can pass table variables as IN, OUT, or INOUT parameters to any non-SQL function. Tables can be passed to SQL functions only as OUT parameters.
If you reference a table variable, the contents of the table are displayed and formatted based on the setting of the DTW_HTML_TABLE variable. In the following example, the contents of myTable would be displayed:
%HTML (output) {
$(myTable)
}
The column names and field values in a table are addressed as array elements with an origin of 1.
These variables are Net.Data-defined variables that you can use to:
Miscellaneous variables can either have a predefined value that Net.Data determines or have values that you set. For example, Net.Data determines the DTW_CURRENT_FILENAME variable value based on the current file that it is processing, whereas you can specify whether Net.Data removes extra white space caused by tabulators and new-line characters.
Predefined variables are used as variable references within the macro and provide information about the current status of files, dates, or the status of a function call. For example, to retrieve the name of the current file, you could use:
%REPORT {
<p>This file is <i>$(DTW_CURRENT_FILENAME)</i>.</P>
}
Modifiable variable values are generally set using a DEFINE statement or with the @DTW_ASSIGN() function and let you affect how Net.Data processes the macro. For example, to specify whether white space is removed, you could use the following DEFINE statement:
%DEFINE DTW_REMOVE_WS="YES"
See the variables chapter in Net.Data Reference for a list of valid miscellaneous variables with syntax and examples.
Net.Data defines table processing variables for use in the REPORT and ROW blocks. Use these variables to reference values from SQL queries and function calls.
Table processing variables have a predefined value that Net.Data determines. These variables allow you to reference values from the result sets of SQL queries or function calls by the column, row, or field that is being processed. You can also access information about the number of rows being processed or a list of all the column names.
For example, as Net.Data processes a result set from an SQL query, it assigns the value of the variable Nn for each current column name, such that N1 is assigned to the first column, N2 is assigned to the second column, and so on. You can reference the current column name for your Web page output.
Use table processing variables as variable references within the macro. For example, to retrieve the name of the current column being processed, you could use:
%REPORT {
<p>Column 1 is <i>$(N1)</i>.</P>
}
Table processing variables also provide information about the results of a query. You can reference the variable TOTAL_ROWS in the macro to show how many rows are returned from an SQL query, as in the following example:
Names found: $(TOTAL_ROWS)
Some of the table processing variables are affected by other variables or built-in functions. For example, TOTAL_ROWS requires that the DTW_SET_TOTAL_ROWS SQL language environment variable be activated so that Net.Data assigns the value of TOTAL_ROWS when processing the results from a SQL query or function call as in the following example:
%DEFINE DTW_SET_TOTAL_ROWS="YES" ... Names found: $(TOTAL_ROWS)
See the variables chapter in Net.Data Reference for a list of valid table processing variables with syntax and examples.
Net.Data displays Web page output generated from the macro in a default report format. The default report format displays in a table format using <PRE> </PRE> tags. You can override the default report by defining a REPORT block with instructions for displaying the output or by using one of the report variables to prevent the default report from being generated.
Report variables help you customize how your Web page output is displayed and used with default reports and Net.Data tables. You must define these variables before using them with a DEFINE statement or with the @DTW_ASSIGN() function.
The report variables specify spacing, override default report formats, specify HTML table output versus default table output, and specify other display features. For example, you can use the ALIGN variable to control leading and trailing spaces for table processing variables. The following example uses the ALIGN variable to separate by a space each column name in a list that is returned by a query.
%DEFINE ALIGN="YES" ... <p>Your query was on these columns: $(NLIST)
The START_ROW_NUM report variable lets you determine at which row to begin displaying the results of a query. For example, the following variable value specifies that Net.Data will begin displaying the results of a query at the third row.
%DEFINE START_ROW_NUM = "3"
You can also determine whether Net.Data uses HTML tags for default formatting. With DTW_HTML_TABLE set to YES, an HTML table is generated rather than a text-formatted table.
%DEFINE DTW_HTML_TABLE="YES"
%FUNCTION(DTW_SQL){
SELECT NAME, ADDRESS FROM $(qTable)
%}
See the variables chapter in Net.Data Reference for a list of valid report variables with syntax and examples.
These variables are used with language environments and affect how the language environment processes a request.
With these variables, you can perform tasks such as establishing connections to databases,enabling NLS support, and determining whether the execution of an SQL statement is successful.
For example, you can use the SQL_STATE variable to access or display the SQL state value returned from the database.
%FUNCTION (DTW_SQL) val1() {
select * from customer
%REPORT {
...
%ROW {
...
%}
SQLSTATE=$(SQL_STATE)
%}
See the variables chapter in Net.Data Reference for a list of valid language environment variables with syntax and examples.