# CHAPTER 6: ehsql:update

## 6.1 Introduction

`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:

1. Set `METHOD=POST`.
2. The `ACTION` clause of the form should contain a script alias name, followed by `ehsql.cgi`, and then a path to a template file. The form should also include the `eh-mode` variable set to 'update'. The template file should include a suitable message about the update that was performed. The syntax for the form declaration is

   `<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.

## 6.3 Record Editing Form

To make a record-editing-form with the desired functionality, choose from the list below and follow the steps:

- Set the following required eh-xxx form variables:

  1. `eh-dbase` to the database name
  2. `eh-mode` to update
  3. `eh-table` to the table to be updated
  4. if the Empress internal index is not being used to index the table set `eh-index-attribute` to the unique attribute you are using to index the table

- To update a record:

  1. Set `eh-edit` '`update`'
  2. if `eh-index-attribute` is set add a form field with a name which is the same as the index attribute name, and a value for the record to be updated.
     else set `eh-internal-index` to the value of `eh-internal-index` for the record to be updated
  3. Create form variables with the names of the attributes desired to be updated. The values given by the user will alter the specified record in the database.
     Note that you must not let the user change the value of the index-attribute itself.

- To insert a record without generation of a new unique integer key value:

  1. Set `eh-edit` to '`insert`'
  2. Create form variables with the names of the attributes desired to be inserted. Any attributes left out will be entered as `NULL`. All attributes specified `NOT NULL` in the table design must have values supplied.

- To Insert a record with the generation of a new unique integer key value:

  1. Set `eh-edit` to '`insert`'
  2. Set `eh-auto-indexing` to `on`.
  3. Set the form variable `eh-index-attribute` to a unique integer attribute.
     Note that this must be an integer for auto-indexing to work.
  4. Create form variables with the names of the attributes desired to be inserted.
     But do not provide a value for the index attribute; `ehsql:update` will insert an integer one larger than the largest existing value.
     If attributes other than the index attribute are left out of the form they will be entered as `NULL`. All attributes (other than the index attribute) specified `NOT NULL` in the table design must have values supplied.

- To Delete a record:

  1. Set `eh-edit` to '`delete`'
  2. if `eh-index-attribute` is set add a form field with a name which is the same as the index attribute name, and a value for the record to be updated.
     else set `eh-internal-index` to the value of `eh-internal-index` for the record to be updated

  ## 6.4 The Update Form Variables

  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: insert update delete
  `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.

  ## 6.5 Examples

  ### 6.5.1 Inserting with Automatic Key Updating

  The following is a listing of an example update form.

         <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.

  ### 6.5.2 An Update Application

  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.