Executing a database query
This topic is divided into the following sections:

Overview of the Database Query Process

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.

Sample HTML for executing a database query

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.

Within the APPLET tag, the various attributes do the following:

Data items defined during the execution of a database query

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.