— Model
204 Users —
Register TODAY for Insight 204!
Got Model 204? Then You NEED
Insight 204!
- CCA’s Insight 204 Symposium
is the most authoritative conference on Model 204, and the one conference
you can’t afford to miss this year. It offers:
- Maximum exposure to CCA developers
and staff members.
- Early previews of the latest product
developments and enhancements.
- Easy access to CCA partners.
- Educational information to help you
get the most out of your Model 204 investment.
- Visit the Insight 204 Web site for
details about all scheduled presentations.
- Mark Your Calendar NOW - April 9-12
in the Boston Area. Plan to join us at the Westin Hotel in Waltham, MA
on April 9-12 for the grand premier of CCA’s Insight 204 Symposium.
We look forward to seeing
you in Boston in April!
- Model 204
— WebGate Programming — Part III
Using
WebGate Tags to Access Model 204 Data
by Mark LaRocca
The first two articles in this
series, in November 1999 and January 2000 issues of CCAprint, demonstrated
the use of WebGate variables within a Web application. Now, we can begin
to look at Web applications that retrieve database data. As before, our
options for coding include using WebGate tags and/or server-side VBScript
or JavaScript. In this article, we present a simple two-page application
that retrieves data from the Model 204 DEMO database using SQL.
Customer Inquiry Sample
Web Application
The Customer Inquiry Web application
consists of two Web pages:
- CCACI.htmx — Customer
Inquiry Input page (Figure 1)
- This page accepts a Policy
number that is used by the next page to look up information in the demo.policies
table. WebGate tags are not used on this page. However, two WebGate system
variables, the date (wdbdate) and time (wdbtime), are displayed.
- CCACD.htmx — Customer
Inquiry Detail page (Figure 2)
- This page contains the code
to create and execute an SQL request and display the output in an HTML
table format.
As in the previous articles,
we examine the HTML and WebGate code involved in our sample application
pages. It is important to understand how data is accepted from the input
page and then received and processed by the detail (output) page, which
displays the results to the user at the browser. Therefore, the code for
each page is given and the resulting output is shown. Figure 1 shows the
code and output for the Customer Inquiry Input page, and Figure 2 shows
the code and output for the Customer Inquiry Detail page. In the final section,
the WebGate tags used to display the detail page are explained in greater
depth.
Customer Inquiry
Input page — Figure 1
Here is the code:
<html><head><title>Customer
Inquiry Input</title></head>
<body bgcolor="white">
<form method="POST"
action="/cgi-bin/wecgi.exe/Customer_Inquiry/CCACD.htx">
<hr size="4"><div
align="left"><em><font color="#FF0000">
<big>Please enter an account number.
</font></big></em></div><hr size="4">
<! textarea box below
is for input of policy number to be used
in creating the request on the next page>
<p> Policy
Number:
<input type="text" name="input_policy_number"
size="20" tabindex="1">
</p>
<p>
<input type="submit" value="Search" name="CCAUISubmit"
style="font-size: small">
<input type="reset" value="Reset" name="CCAUIReset"
style="font-size: small"></p>
<p>
<small>
(Current Date: ##wdbdate##
Time: ##wdbtime##)
</small></p>
<hr size="4"></form></body></html>
Here is the output:
Customer Inquiry Detail
page — Figure 2
Here is the code:
<html><head><title>Customer
Inquiry Detail</title></head>
<body bgcolor="white"><form method="POST">
<WDBREQ_NAME=DetailOut>
<WDBDSN=demo_database>
<WDBSTATEMENT="SELECT POLICY_NO,FIRST_NAME,MIDDLE_NAME,
LAST_NAME,CITY,STATE,ZIP,AGENT FROM
CCACAT.DEMO.POLICIES WHERE POLICY_NO=?">
<WDBPARAMLIST="input_policy_number CHAR">
<WDBACTION=EXECUTE>
<WDBOUTPUT=FORMATTED>
<TABLE BORDER="1"
width="684"><caption><font face=’Verdana,
Arial’ size=’4’><i>Customer Inquiry Detail</i></font></caption>
<TR bgcolor="FFFF99">
<TH><font face=’Verdana, Arial’ size=’-1’>Policy
No</font></TH>
<TH><font face=’Verdana, Arial’ size=’-1’>First
Name</font></TH>
<TH><font face=’Verdana, Arial’ size=’-1’>Middle
Name</font></TH>
<TH><font face=’Verdana, Arial’ size=’-1’>Last
Name</font></TH>
<TH><font face=’Verdana, Arial’ size=’-1’>City</font></TH>
<TH><font face=’Verdana, Arial’ size=’-1’>State</font></TH>
<TH><font face=’Verdana,
Arial’ size=’-1’>Zip</font></TH>
<TH><font face=’Verdana, Arial’ size=’-1’>Agent</font></TH>
</TR>
<WDBFORMAT_AREA=DetailOut>
<TR><TD align=’center’>##detailout.POLICY_NO##</TD>
<TD align=’center’> <font face=’Arial, Verdana’
size=’-1’>##detailout.FIRST_NAME##</font></TD>
<TD align=’center’> <font face=’Arial, Verdana’
size=’-1’>##detailout.MIDDLE_NAME##</font></TD>
<TD align=’center’> <font face=’Arial, Verdana’
size=’-1’>##detailout.LAST_NAME##</font></TD>
<TD align=’center’> <font face=’Arial, Verdana’
size=’-1’>##detailout.CITY##</font></TD>
<TD align=’center’> <font face=’Arial, Verdana’
size=’-1’>##detailout.STATE##</font></TD>
<TD align=’center’> <font face=’Arial, Verdana’
size=’-1’>##detailout.ZIP##</font></TD>
<TD align=’center’> <font face=’Arial, Verdana’
size=’-1’>##detailout.AGENT##</font></TD>
</TR>
</WDBFORMAT_AREA>
<WDBACTION=DISPLAY>
</table>
<p><small>
(Current Date: ##wdbdate##
Time: ##wdbtime##)</small></p>
</form></body></htm
Here is the output:
WebGate Tags
Let’s look at the tags
used to create the Customer Inquiry Detail page:
<WDBREQ_NAME=DetailOut>
-- Name the request "DetailOut".
<WDBDSN=demo_database>
-- The WebGate Broker will use the Data Service "demo_database"
to
connectto Model 204.
<WDBSTATEMENT="SELECT POLICY_NO,FIRST_NAME,MIDDLE_NAME, LAST_NAME,CITY,STATE,ZIP,AGENT
FROM
CCACAT.DEMO.POLICIES WHERE POLICY_NO=?">
WDBSTATEMENT contains the SQL
request that is passed to Model 204 by the WebGate Broker. The following
statements set up and execute the initial request:
<WDBPARAMLIST="input_policy_number
CHAR">-- Input_policy_number is passed from the Input page.
Since it is the first and only parameter, it will be inserted in the SELECT
statement at the first "?".
<WDBACTION=EXECUTE>-- This statement causes the WebGate Broker to
send WDBSTATEMENT to Model 204.
<WDBOUTPUT=FORMATTED>-- Data will be returned to a Formatted area.
Rows of data are returned from
Model 204 and placed within the format area defined by the following format
area tags:
<WDBFORMAT_AREA=DetailOut>--
Begin the format area.
<TR><TD align=’center’>##detailout.POLICY_NO##</TD>
Each column name is printed
using the format ##requestName.columnName##. In the previous example, the
column POLICY_NO is printed in the first cell, and so on (see Figure 2 code).
This process is automatically repeated for each row of data until all data
from the request has been displayed in the HTML table on the Customer Inquiry
Detail page:
...
</WDBFORMAT_AREA>
-- End the format area.
<WDBACTION=DISPLAY>
-- Display the data in the format area.
Conclusion
We have
followed the WebGate tag code from the creation of the request through the
display of the data on the Customer Inquiry Detail page. At this point,
the WebGate Communicator returns the formatted page to the IIS Web Server,
which, in turn, sends the page to the browser that displays the final results
to the user. This is a fairly simple application, but it demonstrates the
basic concepts of creating and executing a request and formatting the output
for display. Of course, there are other ways to accomplish this task, but
the concepts remain the same.
In the
next article, we will perform this same request and display using WebGate
server-side VBScript extensions instead of WebGate tags.
System 1032
— Part II
Optimizing
Joins for ODBC
by Tym Stegner
Part I of this article in the
December 1999 issue of CCAprint described how to optimize joins for
ODBC; Part II provides an example using the MOVIES database in the S1032_DEMO
area.
Important Note
In the December article, the
last three sections describe how to set up a view dataset for inclusion
in the ODBC Dictionary. In the intervening time, the ODBC Dictionary’s
ADD TABLE command has changed. The following sections describe the appropriate
method to create and import a view dataset into the ODBC Dictionary.
Creating an ODBC view data
source from System 1032 datasets
In this scenario, you want to
compare a studio’s profits with their award-winning films of the same
year. The data is currently found in the following datasets:
|
Dataset
|
Data
|
| Films |
FILM_TITLE,
COUNTRY, RELEASE_YEAR, SCRIPTWRITER, TOTAL_RENTALS, SYNOPSIS |
| Directors |
LAST_NAME |
| Studios_Header |
FULL_NAME |
| Studios_Detail |
STUDIO, TOTAL_FILMS,
ANNUAL_PROFITS, CALENDAR_YEAR |
To make this data accessible
to ODBC, you must supply:
- Database file that contains
the dataset view, the underlying datasets, and their join information
- Collect information
- Predefined selection set
ODBC Driver extracts both the
join information and the fields of the view from within this database.
Defining a view dataset
The view dataset combines the
films, full names of the directors, financial information for the studio,
and the studio’s full name to provide access to all the required data.
Define the view dataset as follows:
1032> OPEN MOVIES
IN S1032_DEMO READONLY
4 datasets opened in MOVIES
Current dataset is now FILMS, current database is MOVIES
1032> CREATE DS FINVIEW
VIEW
DDL_VIEW> COPY ATTRIBUTE FILMS.FILM_TITLE
DDL_VIEW> COPY ATTRIBUTE FILMS.COUNTRY
...
DDL_VIEW> COPY DATASET STUDIOS_DETAIL
...
DDL_VIEW> END_DATASET
1032>
And so on, to select all the
attributes listed in the previous table.
Storing join information
in a database
The underlying datasets are
already joined in the MOVIES database, as displayed by the following SHOW
JOIN command:
1032> SHOW DB MOVIES
JOIN
Database MOVIES
Join Outer
Join Many FILMS To One DIRECTORS Via DIRECTOR_ID To DIRECTOR_ID
Join Many FILMS To One STUDIOS_HEADER Via STUDIO To STUDIO
Join One STUDIOS_HEADER to Many STUDIOS_DETAIL Via STUDIO To STUDIO
However, since the ODBC Driver
must obtain this data from within a database containing the view dataset,
we must create a new database incorporating these four datasets, the join
information, and the view dataset. First, extract the join information to
a file, then edit the FINVIEW.JOIN file to remove the first two lines, leaving
only the join commands. You can also enter the join commands manually:
1032> SHOW ON FINVIEW.JOIN
DB MOVIES JOIN
1032> CALL EDT("FINVIEW.JOIN")
Next, create the FINVIEWDB database
to contain the underlying datasets, the dataset view, and the join information:
1032> CREATE DATABASE
FINVIEWDB
DDL> INCLUDE FILMS
DDL> INCLUDE DIRECTORS
DDL> INCLUDE STUDIOS_HEADER
DDL> INCLUDE STUDIOS_DETAIL
DDL> INCLUDE FINVIEW
DDL> @FINVIEW.JOIN
DDL> END_DATABASE
1032>
At this point, exit System 1032
to clear your context, then restart System 1032 and reopen the FINVIEWDB
database.
Storing collect information
in the file
Associate the attributes in
the source datasets with the view dataset using the COLLECT command. Next,
immediately extract the command itself out to a file, as shown in the following
example:
1032> COLLECT JOIN
FINVIEW AS DS FILMS, DS DIRECTORS, -
1032_ DS STUDIOS_HEADER, DS STUDIOS_DETAIL
1032> WRITE ON FINVIEW.COLLECT $LAST_COMMAND
ODBC Driver needs the COLLECT
command to correctly define the view, so this COLLECT command becomes part
of the ADD TABLE command.
Forming a selection set
to be saved
Form the initial view selection
by first selecting one of the source datasets, and create the selection
you want. This is usually a FIND ALL command. To form the joined records
based on that selection set, populate (put virtual records in) the view
by issuing the MAP command:
Database was also changed
to MOVIES database
1032> FIND ALL
106 FILMS records found
1032> SET DS FILMS
1032> MAP TO FINVIEW
Database was also changed to $TEMP database
3076 related records located in FINVIEW, current dataset is FINVIEW
1032>
Each of the 106 FILMS records
now has attached to it the corresponding director and studio information,
and each of these augmented records is then related to all the studio calendar-year
data, resulting in a multi- fold expansion of 3076 related records.
Preserving a view selection
set
Processing the MAP command to
populate the view selection set is often the most resource-intensive portion
of establishing the view. To minimize this overhead, preserve the initial
view selection set by saving it to a file. The ODBC Driver uses this file
as part of the ADD TABLE command:
1032> SAVE SELECTION
FILE FINVIEW.DMV
1032>
It is very important to create
the view selection set from within the same database as contains the view
dataset. If the earlier database close and open had not been done, the view
selection set would have been against the MOVIES database instead of the
FINVIEWDB database. Any subsequent attempt to restore that selection set
would have resulted in an error.
At this time, you have the files
FINVIEW.COLLECT, FINVIEW.DMS, FINVIEW.DMV, and FINVIEWDB.DMB.
Before you import the view into
the ODBC Dictionary, edit FINVIEW.COLLECT to remove all keywords up to the
dataset list, as shown in the following example:
- Before editing the FINVIEW.COLLECT
file:
COLLECT JOIN FINVIEW AS DS
FILMS, DS DIRECTORS, DS STUDIOS_HEADER, DS STUDIOS_DETAIL
- After editing the FINVIEW.COLLECT
file:
DS FILMS, DS DIRECTORS, DS
STUDIOS_HEADER, DS STUDIOS_DETAIL
Creating the ODBC data source
Start ODBC_DICTIONARY, and choose
the destination dictionary:
$ RUN S1032ODBC_IMAGE:ODBC_DICTIONARY
DICT> SET DICTIONARY TYMS
DICT>
To create the FINVIEW table,
issue an ADD TABLE command, specifying the qualifier to include the necessary
components:
DICT> ADD TABLE FINVIEW
-
DICT_ /DATABASE=FINVIEWDB.DMB -
DICT_ /JOIN="@FINVIEW.COLLECT" -
DICT_ /SELECTION=FINVIEW.DMV
DICT>
Congratulations! The FINVIEW
view dataset is now an available data source in the ODBC Dictionary. Queries
against the FINVIEW table are converted into search commands against the
automatically restored view selection set.
Note: The V3.0-0 ODBC
Driver server cannot access the view table from ODBC. Please contact
System 1032 Customer Support to obtain a refresh that enables this ability.
