ehsql:update is laid out very much like ehsql:select. Instead of a form specifying a query and returning results, a form specifying changes to a particular record is made. New records can be inserted, existing records can be changed or deleted.
There are fewer form variables to set compared to ehsql:select. Inserting a new record doesn't require a WHERE clause of any sort. When deleting or updating, ehsql:update does not choose its records from a complicated list of conditions (it is not like the WHERE clause in an SQL UPDATE), but rather must be provided with the value of a unique attribute. This isn't to say the user of a Toolkit application must know obscure key values to edit a record. To obtain the unique key attribute, the developer may use ehsql:select or ehsql:writer to phrase a user-built query form which produces the required attribute, which can then be passed to ehsql:update. In most applications, an ehsql:update form for updating or deleting will be preceded by some other form for selecting the record to update.
Like ehsql:select, variables which do not start with eh- are assumed to be attribute names of the table specified with eh-dbase and eh-table. Unlike ehsql:select, these attributes do not form part of the query conditions, but rather specify the values of the new or updated record. Thus the HTML form input widgets will allow the user to type in or select the desired values. If attributes which exist in the table to be edited do not appear in the form, or the user does not fill them out, they will be set to NULL for an insert, or left as-is for a record update.
It is possible to emulate the all the functionality of the ehsql:update
tool using ehsql:writer. Application developers who are familiar with
SQL may prefer to use writer for these tasks; but more coding will
generally required.
6.2 Invoking ehsql:update from a HTML Form
To invoke update tool from a HTML form, you have to do the following:
<FORM METHOD="POST"
ACTION="script_alias/ehsql.cgi/path_to_template_file>
A typical example might be:
<form method="post"
action="/My-bin/ehsql.cgi/docs/u1template.html">
<input name="eh-mode" value="update">
In this case the paths are from the DocumentRoot of the HTTP server. The first path is DocumentRoot/My-bin/ehsql.cgi. This will pass the form variables to the ehsql.cgi program. The second path is DocumentRoot/docs/u1template.html. After the question-mark comes the word update which tells the ehsql.cgi program to process the form information as an update. Finally, the second parameter of the action is a value.
To make a record-editing-form with the desired functionality, choose from the list below and follow the steps:
To update a record:
To insert a record without generation of a new unique integer key value:
To Insert a record with the generation of a new unique integer key value:
To Delete a record:
The following is a list of all allowable form variable names, and their meanings.
Table 6-1: The Update Form Variables
| Name | Description |
|---|---|
| eh-dbase | The value specifies the data source. This is required. |
| eh-table | The value specifies the tables to update. |
| eh-mode | This must be set to update. |
| eh-edit | This is the flag which tells ehsql:update what kind of record-editing operation to perform. The allowable values are:
|
| eh-index-attribute | This should be set to a UNIQUE, NOT NULL attribute of the table to be edited. If it is desired that the index attribute be updated automatically with each new insert, it must also be and integer type. |
| eh-index-table | This must be set if a view is specified in the eh-table variable. It must give the name of the base table of the view which contains the index attribute. |
| eh-auto-indexing | This can be set to on or off. Only during an insert operation in which the index attribute is to be automatically updated should the value be on. Every time the insert form is submitted, the new record will have a value equal to the maximum value already in the column plus 1. The index attribute must be of integral type. |
| eh-required | ehsql:update does not require that the user fill out the form completely before submission. Any unfilled attributes will be set to NULL in the database table when the edit is made. However, attributes in a relational database table can be declared as NOT NULL, meaning there must be a record for each value. If the Toolkit tries insert NULL into such an attribute, it will fail and an SQL Server error will be issued. To avoid a low-level error like this being displayed on your net application, set eh-required to a list of attributes which must be filled out. If the attribute is not filled out, an HTML prompt will appear stating that the particular field must be filled out. |
| eh-binary | Any binary attributes should be indicated using this field. If there
is more than one binary attribute, each one can be entered in a separate
form field. Or they can all be added as a comma separate list in
a single field.
eg if you have three binary attributes 'a1', 'a2' and 'a3'. Then <INPUT TYPE="HIDDEN" NAME="eh-binary" VALUE="a1"> <INPUT TYPE="HIDDEN" NAME="eh-binary" VALUE="a2"> <INPUT TYPE="HIDDEN" NAME="eh-binary" VALUE="a3">and <INPUT TYPE="HIDDEN" NAME="eh-binary" VALUE="a1, a2, a3"> are equivalent.
|
<form method=POST action=
"/cgi-bin/ehsql.cgi/usermessage.html">
<input type=hidden name=eh-dbase value="sales">
<input type=hidden name=eh-mode value="update">
<input type=hidden name=eh-edit value="insert">
<input type=hidden name=eh-table value="NetOrders">
<input type=hidden name=eh-index-attribute
value="OrderKey">
Please enter your name:<input name="name"><p>
Company name:<input name="company"><p>
Shipping Address:<input name="address"><p>
Quantity:<input name="quantity"><p>
CreditCard Number:<input name="CCnum"><p>
<input type=submit value="submit order">
</form>
The following is a listing of the HTML body of the target file for
the above form. This would be contained in usermessage.html:
<h1>The following order has been placed.</h1> %%name%%<p> %%company%%<p> %%address%%<p> %%quantity%%<p> %%CCnum%%<p> Your Order Number is %%OrderKey%%
In this example, an order-form is created, which inserts new orders into the NetOrders table in the Sales database. Note that the URL of the form action is composite. First ehsql.cgi is called, and it is passed the path (from DocumentRoot) of a file called usermessage.html, which tells the customer the record has been updated.
The following is a listing of an example of a form template that can use ehsql:writer to generate a pulldown list of customers.
<form method=POST
action="/bin/ehsql.cgi/updateform.ehtml">
Select the name of the customer you wish to update
<P>
<QUERY DBASE="sales"
SQL="SELECT name, OrderKey FROM NetOrders"
START="<SELECT NAME='OrderKey'>"
END="<input type=submit value='Go to update form'>"
>
<OPTION VALUE="%%OrderKey%%">%%name%%</OPTION>
<NUMRECORDS = 0>
No Customer Information Available
</QUERY>
</form>
The above form can then be submitted to an update form like:
<html>
<QUERY DBASE="sales"
SQL="Select OrderKey, name, company, address, quantity, CCnum
FROM NetOrders WHERE OrderKey=&&OrderKey&&">
<form method=post
action="/bin/ehsql.cgi/message.html?%%OrderKey%%">
<input type=hidden name=eh-dbase value="sales">
<input type=hidden name=eh-mode value="update">
<input type=hidden name=eh-edit value="update">
<input type=hidden name=eh-table value="NetOrders">
<input type=hidden name=eh-index-attribute value="OrderKey">
Customer Name:
<input name="name" value="%%name%%"><p>
Company:
<input name="company" value="%%company%%"><p>
Address
<input name="address" value="%%address%%"><p>
Quantity Ordered:
<input name="quantity" value="%%quantity%%"><p>
Credit Card Number:
<input name="CCnum" value="%%CCnum%%"><p>
<input type=submit value="submit update">
</form>
<numrecords=0>
Error: No records found
<numrecords>1>
Error: More than one record returned
</QUERY>
</html>
The user could then submit this form to message.html
<html> <h1>Record Updated</h1> </html>
In this example, the user is given a list of customer names to select from: the name is displayed as a pulldown, the OrderKey is hidden as the VALUE part of the pulldown menu. This OrderKey is passed into the ehsql:writer <QUERY> in updateform.ehtml as the query condition. The example includes error messages if more than one customer with that name is found, or if no customers with that name are found - this is unlikely to occur, since the pulldown was generated from the database. In the normal case of one record being returned by the <QUERY>, an ehsql:update form is placed within the <query> tag and the first of the <numrecords> tag. The form allows the user to change any information about that particular order, but also writes the current values into the fields of the form as defaults. This is why each of the <input> tags that the user can see is given a value as well as a name. The value is set to be the results of the <QUERY>.
After the update is performed, a very simple message is issued, which does no checking of the database.