Executing a database query
This topic is divided into the following sections:
The JdbcSource applet executes retrieval operations by connecting to an external database and using the retrievals HTML parameter to define and execute one or more retrieval queries.
The value for a retrieval query can be an SQL String, a parameterized SQL string, or a saved query file. A parameterized SQL string contains the '%' character as the delimiter. A saved query file is one that is read which specifies a query text file by naming '@' as the first character followed by the query text file name (for example, "@querylist.txt").
The actual execution of the query is triggered at runtime by setting an incoming "trigger" data item that has been set to a "true" condition. This trigger item has been implicitly created by defining the original query. Click here for more information on these "trigger" data items.
The following sample HTML code shows the PARAM tags needed to execute a retrieval operation with the JdbcSource applet. Three retrieval queries are defined, "EmpInfo" and "SalesForRegionYear" and "EmpInfoSaved".
<APPLET NAME="dataBase1" CODEBASE="..\..\.." CODE="lotus.jdbc.JdbcSource" WIDTH=1 HEIGHT=1>
<!-- Configuration setup tags begin here -->
<PARAM NAME="driver" VALUE="JDBC driver classname">
<PARAM NAME="autoload" VALUE="true">
<PARAM NAME="database" VALUE="databaseURL">
<PARAM NAME="user" VALUE="databaseID">
<PARAM NAME="password" VALUE="databasePassword">
<PARAM NAME="autoconnect" VALUE="true">
<!-- Retrieval operation tags begin here -->
<PARAM NAME="retrievals" VALUE="EmpInfo, SalesForRegionYear, EmpInfoSaved">
<PARAM NAME="EmpInfo" VALUE="select * from employee">
<PARAM NAME="SalesForRegionYear" VALUE="select sum(sales) from salesinfo where region='%region%' and year=%year%">
<PARAM NAME="EmpInfoSaved" VALUE="@x.qry">
<PARAM NAME="EmpInfo_resultStyle" VALUE="data">
<PARAM NAME="SalesForRegionYear_resultStyle" VALUE="data">
<PARAM NAME="EmpInfoSaved_resultStyle" VALUE="data">
</APPLET>
The following JdbcSource PARAM tags specify the options for executing a database query. Tag options are specified by the NAME attribute and option values are specified by the VALUE attribute of the tag.
Configuration Setup Tags
- The driver PARAM tag specifies the driver that the applet uses for JDBC operations.
- The autoload PARAM tag is set to "true" so that the driver is automatically loaded each time the applet is launched.
- The database PARAM tag specifies the connecting database via a URL-like string
- The user PARAM tag specifies the database ID
- The password PARAM tag specifies the database password
- The autoconnect PARAM tag is set to "true" so that the driver is automatically started each time the applet is launched.
Retrieval Operation Tags
- The retrievals PARAM tag lets you define a list of query names to process. Specific queries are given a name to differentiate between other queries with similar values. In this sample, three retrieval queries are defined: "EmpInfo", "SalesForRegionYear", and "EmpInfoSaved".
- The "EmpInfo" PARAM tag in this sample refers to the first query specified in the previous "retrievals" PARAM tag. This PARAM tag specifies the actual query to execute. The query is stated in the VALUE attribute.
- The "SalesForRegionYear" PARAM tag in this sample refers to the second query specified in the previous "retrievals" PARAM tag. The query is stated in the VALUE attribute.
- The "EmpInfoSaved" PARAM tag in this sample refers to the last query specified in the previous "retrievals" PARAM tag. The query is stated in the VALUE attribute.
- The "_resultStyle" PARAM tags in this application lets you specify the format of each resulting data set by specifying a parameter whose name is the name of the specific query with "_resultStyle" appended. In this application, the "_resultStyle" PARAM tags specify that only the data for each query is to be returned in the data set.
Within the APPLET tag, the various attributes do the following:
- NAME assigns a reference name to the applet so that you can refer to it in your code.
- CODEBASE specifies the base URL of the applet. That is, the directory or folder that contains the applet's code. If this attribute is not specified, then the document's URL is used.
- CODE gives the name of the file that contains the applet's compiled Applet subclass. This file is relative to the base URL of the applet. It cannot be absolute.
- WIDTH and HEIGHT define the size of the applet's display region in the HTML document. This is measured in pixels. In the case of this applet, minimal dimensions are given to the applet so that it remains invisible. You can also achieve the same result by setting the allowUI PARAM tag to "False."
By defining the three retrieval queries ("EmpInfo", "SalesForRegionYear", and "EmpInfoSaved") in the example, the following data items required for the retrieval process are also implicitly defined:
Data Items for the EmpInfo query:
Data Item Name
|
Type
|
Description
|
EmpInfo.trigger
|
In
|
Causes "EmpInfo" query to be executed when set to true
|
EmpInfo.status
|
Out
|
Contains status of the "EmpInfo" query (initially "no data", later "OK" or an error message)
|
EmpInfo
|
Out
|
Contains the result data for the "EmpInfo" retrieval query
|
Data Items for the SalesForRegionYear query:
Data Item Name
|
Type
|
Description
|
SalesForRegionYear.trigger
|
In
|
Causes the "SalesForRegionYear" query to be executed when set to true
|
region
|
In
|
Incoming data item needed to complete the "SalesForRegionYear" query
|
year
|
In
|
Incoming data item needed to complete the "SalesForRegionYear" query
|
SalesForRegionYear.status
|
Out
|
Contains the status of the "SalesForRegionYear" query (initially "no data", later "OK" or an error message)
|
SalesForRegionYear
|
Out
|
Contains the result data for the"SalesForRegionYear" query.
|
Finally, the data items for the last retrieval, EmpInfoSaved, are the same as those for EmpInfo.