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.
The following application design topics are covered by creating this application:
- Embedding, configuring, and connecting the application applets necessary for your application web-page
- Coding and configuring the HTML PARAM (parameter) tags required for initiating a database query with the JdbcSource applet
- Writing and embedding a JavaScript function (run_query) that handles the initiation of a database query when a button on the Web-page is clicked
- Connecting the ScriptHelper helper applet to the Infobus via a script so that data can be published to the InfoBus
- Connecting a Spreadsheet applet to the application so that data published to the InfoBus can be stored
- Using a Spreadsheet template (via PARAM tags) with the Spreadsheet applet so that data published to the InfoBus can be displayed in specific ways
- Connecting a Chart applet to the calculated Spreadsheet data and displaying the data via the Chart applet
- Adding a Button and a drop-down list to your Web page to customize the application.
The following applets and additional functional pieces make up the Parameterized Query application.
Use the following procedure to create the Parameterized Query application:
- 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).
- 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.
- 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.
- 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.
- 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:
- Hold the results of the database query
- Load a template file which has all range names, formats, and formulas defined for presenting the results of the query
- Disable column and row headers, the edit line, and the status line
- Enable the horizontal and vertical scroll bars
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.
- 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.
- Create an instance of the JdbcSource applet. This data access applet will perform the database query via the ScriptHelper applet and the InfoBus.
- 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.
Make sure that you do the following to correctly install and run this application:
- Setup one or more databases (typically a Relational database or Notes database)
- Setup a Web server (Domino, for example)
- Install database client elements on the Web server (e.g. Notes API, SQL Server "dblib" or Oracle SQL*Net)
- Install JDBC elements on the Web server. This includes:
- A vendor's alternate "java.sql" package, e.g. "symjava.sql", "dataramp.sql", "jdbc.sql"
- Classes which implement the JDBC driver, and any classes needed by the driver
- Middleware executeables (for example, dbaw.exe and the .dlls it requires). Depending on the exact middleware and database being used, this may include installing ODBC driver(s) on Web server (for example, the NotesSQL ODBC to Notes-API driver, or an ODBC to SQL Server driver, or an ODBC to Oracle driver);
- Register ODBC data source names
- Make one or more .HTML pages which use lotus.jdbc.JdbcSource available on the Web server
- Start the middleware executable (e.g. Symantec's dbaw.exe)
Only after all the above are setup is it possible to exercise JdbcSource.
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 install the Notes SQL driver, access the Lotus web site at http://www.lotus.com/ and click on the "Products" link followed by "NotesSQL" link. Download the necessary files and install.
- To register the ODBC data source from the Windows Control Panel, click on "ODBC." Next click on the tab "System DSN" followed by the "Add" button. Finally, highlight "Lotus NotesSQL 2.0 ODBC Driver" and click on the "Finish" button.
- Enter a Data Source Name and description. The Data Source Name will be what you refer to when setting up the dbcSource applet on the pquery.html page. The Notes database containing the data for the Parameterized Query sample is named pquerydata.nsf.
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