Executing a database command
This topic is divided into the following sections:
The JdbcSource applet executes non-retrieval database operations (such as Insert, Delete, and Update) by connecting to an external database and using the commands HTML parameter. Commands are executed at runtime by setting an incoming command "trigger" data item to true. This trigger item is implicitly created by defining the original command with a PARAM tag. Click here for more information on these "trigger" data items.
The following sample HTML code shows the PARAM tags needed to execute a non-retrieval command operation with the JdbcSource applet.
Two commands are defined, "NewEmp" and "EmpDeptChanged". "NewEmp" inserts a new employee's last name, first name, and employee into a new database record. "EmpDeptChanged" updates the employee department information for a specified employee record.
<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">
<!-- Command tags begin here -->
<PARAM NAME="commands" VALUE="NewEmp, EmpDeptChange">
<PARAM NAME="NewEmp" VALUE="insert into employee(lastname, firstname, id) values(%ln%, %fn%, %id%)">
<PARAM NAME="EmpDeptChange" VALUE="exec sp_emp_change(%id%, %oldDept%, %newDept%">
The following JdbcSource PARAM tags specify the options while executing the database commands used in this example. 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.
Command Tags
- The commands PARAM tag lets you define a list of command names to process. Specific commands are given a name to differentiate between other commands with similar values. In this sample, two commands are defined: "NewEmp" and "EmpDeptChanged"..
- The "NewEmp" PARAM tag in this sample refers to the first command specified in the previous "commands" PARAM tag. This PARAM tag specifies the actual command to be executed. In this case, it is stated as an SQL Insert statement via the tag VALUE attribute. In this example, the NewEmp command adds a new employee's last name, first name, and employee ID into the Employee database.
- The "EmpDeptChange" PARAM tag in this sample refers to the second command specified in the "commands" PARAM tag. The command is stated in the VALUE attribute. In this example, the EmpDeptChange command executes a routine called "sp_emp_change" which looks up an employee database record based on the employee ID and updates the department information.
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 achieve the same result by setting the allowUI PARAM tag to "False."
By defining two commands ("NewEmp" and "EmpDeptChange") in the example, the following data items are also implicitely defined and used during the command operations:
Data Items for the NewEmp command:
Data Item Name
|
Type
|
Description
|
NewEmp.trigger
|
In
|
Causes "NewEmp" command to be executed when set to true
|
ln
|
In
|
Employee last name
|
fn
|
In
|
Employee first name
|
id
|
In
|
Employee ID
|
NewEmp.status
|
Out
|
Contains status of the "NewEmp" command (initially "no data", later "OK" or an error message)
|
NewEmp |
Out
|
Contains the result for the "NewEmp" command. This is the number of data rows affected by the operation. The result is -1 if this is not applicable.
|
Data Items for the EmpDeptChange command:
Data Item Name
|
Type
|
Description
|
EmpDeptChange.trigger
|
In
|
Causes the "EmpDeptChange" command to be executed when set to true
|
id
|
In
|
Employee ID
|
oldDept
|
In
|
Employee's old department
|
newDept
|
In
|
Employee's new department
|
EmpDeptChange.status
|
Out
|
Contains the status of the "EmpDeptChange" command (initially "no data", later "OK" or an error message)
|
EmpDeptChange
|
Out
|
Contains the result for the "EmpDeptChange" command. This is the number of data rows affected by the operation. The result is -1 if this is not applicable.
|