The Parameterized Query Application
This document describes the Parameterized Query application available for the Lotus eSuite DevPack. The document is divided into the following sections:

Introduction

The Parameterized Query application demonstrates how Lotus applets perform a database query and display the results in both a text and graphical format. This application is also a good example of how to connect various Lotus applets together using HTML, the InfoBus, and applet template files.

This sample application is used on Web page of a fictitious shoe company. The application lets you select a sales region from a drop-down list and then click on a button to query a database for unit sales and prices of various shoe types for that region.

The results of the query are retrieved and placed into a range on a spreadsheet applet in the same Web page. The spreadsheet uses pre-loaded a template file. The spreadsheet template then calculates the sale amounts for each transaction and graphs these values in a chart applet located on the Web page.

Application Design Topics Covered

The following application design topics are covered by creating this application:

The Application Pieces

The following applets and additional functional pieces make up the Parameterized Query application.

Coding the Parameterized Query Application

Use the following procedure to create the Parameterized Query application:
  1. Use the necessary tags to create a basic HTML page that will contain all of application pieces (applets, helper applets, graphics, buttons, and so on).

  2. Create a table within the BODY section of the HTML page to act as a container for the instances of the Sheet and Chart applets that use and display the information obtained from the query and place it on the InfoBus via the ScriptHelper.

    Within the table you will also need to create a form that provides a drop down list box (with a <Select> tag and its options) to select information about sales in a particular state. Finally, you need to create a Submit Query button (with an <input> tag and its attributes) in the form that when clicked runs the run_query function (see Step 3) using the selected state sales area information chosen from the drop-down box.

  3. Write and insert a JavaScript function (run_query) into your HTML Header section (between the <HEAD> tags) to handle the initiation of a database query when a button on the Web-page is clicked.

    There are two data items that must be placed on the InfoBus in order to execute the query: the parameter to the query and the query trigger. The query is executed by the JdbcSource applet (defined later) only when the query trigger goes from false to true. So the first step of the script is to reset the query trigger before placing both data items on the InfoBus.

  4. Insert an instance of the ScriptHelper applet into the beginning of the BODY section of your HTML page. This gives the run_query script function the ability to put data items on the application InfoBus. This applet has no user interface so it must be made invisible on the Web page by using minimal WIDTH and HEIGHT settings in the ScriptHelper APPLET tag.

  5. Create an instance of the Sheet applet in the top left cell of the two row table created earlier. The applet is programmed to do the following:

    An important point to remember about this step is to be sure to include the Sheet applet template file (PQueryTemplate.html) in your application directory. The spreadsheet uses this pre-loaded spreadsheet template to calculate and display sales amounts for each transaction. These results are then graphed by the Chart applet.

  6. Create an instance of the Chart applet in the top row right-hand cell of the two-row table. The Chart applet graphs a range of data from the Sheet applet (data calculated from the results of the database query). The name of the Chart applet (shoeChart) determines the name of the data item that is charted so in this example the Sheet applet must have a range defined with this name.

  7. Create an instance of the JdbcSource applet. This data access applet will perform the database query via the ScriptHelper applet and the InfoBus.

  8. Make sure that you have correctly set up the proper infrastucture for database queries. There are several system and network requirements that must be met for this application to run successfully. Refer to the "Installing and Running the Parameterized Query Application" section of this document for more details.

Installing and Running the Parameterized Query Application

Make sure that you do the following to correctly install and run this application:

Only after all the above are setup is it possible to exercise JdbcSource.

Running the Parameterized Query Application with a Notes Database

A Notes database can be used as an alternative to the SQLAnywhere database provided with the Parameterized Query sample. Actual Notes data for testing this configuration has been included in the sample application directory. The following information will assist you in running the Parameterized Query Application with a Notes Database: To access this database, modify the line in the pquery.html file:

<PARAM NAME="database" VALUE="jdbc:dbaw://SERVER:8889/Sybase_SQLANY/PQueryData/PQueryData">

This change lets you refer to the Notes data source that you created. If the data source is named 'PqueryNotesData', then change 'PQueryData' in the file to 'PqueryNotesData' The 'SERVER' parameter must be changed to the name of the server where the data resides.

The data for this sample is also provided in an ASCII text file, pquerydata.txt. You can modify the sample to use the FileReader applet to read this data and publish it to the Infobus instead of using the JdbcSource applet, or you can convert this data to another database format to try the sample with an alternative database server.

Note that this configuration has not been strongly tested and may not work with some JDBC middleware versions.

Finally, remember that the Notes SQL driver must be installed on the server and registered as an ODBC data source for this change to run successfully.

See also
Jdbcsource applet
ScriptHelper applet
Sheet applet
Chart applet
FileReader applet