Forms and AOLserver

Most database packages include a forms interface. Forms are a natural way to interact with databases because both forms and databases have the same basic data structure. A form is defined as a set of fields, and a database table is also defined as a set of fields. AOLserver provides an HTML forms interface to database tables that lets users search tables, enter data, update data, and remove data.

Forms are used on the World Wide Web to collect data. Right now, forms are the primary method for sending data from users to World Wide Web servers. A typical World Wide Web site includes regular pages along with forms for ordering products, requesting information, responding to surveys, etc.

This screen shows an example search form:

To use forms on the World Wide Web, you must have a process on the server that knows how to handle the data in the form. When you press a button on a form, the data that the user entered into the fields is sent to a URL that is specified as the action of the form. The action can be a CGI program, a user-written Tcl or C extension, or, in the case of AOLserver, a presupplied operation that reads the values of the fields in the form and does something with the data.

AOLserver has operations for handling forms to write, select, update, and delete form data. The URLs for managing form data with AOLserver are listed below. These URLs are the destination for data filled into one of the automatically-generated forms described in the next section.

/NS/Db/InsertRow/poolname/tablename Adds a new row to tablename.
/NS/Db/UpdateRow/poolname/tablename Updates a row in tablename.
/NS/Db/DeleteRow/poolname/tablename Deletes a row in tablename.
/NS/Db/SearchQBF/poolname/tablename Builds and executes a SQL statement from data in the form.

Automatic Forms Generation

AOLserver dynamically creates entry forms, update forms, and search forms for any table in the database using the operations listed below. You can access any of the automatic forms by typing in the appropriate URL with the tablename you want. Alternatively, you can access these forms through the Database Services link on the AOLserver administration page. See the "Database Services in AOLserver" section for more information.

/NS/Db/GetEntryForm/poolname/tablename	Gets a dynamically generated form for inserting new data into tablename. The action of an entry form is /NS/Db/InsertRow/poolname/tablename.
/NS/Db/GetUpdateForm/poolname/tablename $RowID.column=value (The exact syntax of the row argument depends on the database driver being used.)	Gets a dynamically generated form for updating or deleting a record in tablename. The action of an update form is /NS/Db/UpdateRow/poolname/tablename
/NS/Db/GetSearchForm/poolname/tablename	Gets a dynamically generated form for searching a table. The action for a search form is /NS/Db/SearchQBF/poolname/tablename.

AOLserver creates a field in an HTML form corresponding to each column in the table. The forms are displayed as HTML3 tables. The title of the form contains the table name (or table description, if one is specified), and the titles of the fields on the form are the column names (or column descriptions, if specified). The mapping between fields in the form and columns in the database is specified by a special prefix, "ColValue.", in the INPUT NAME of the field in the HTML form.

The INPUT NAME starts with ColValue and is followed by the name of the column in the database. Because the INPUT NAMEs must map correctly to the database, AOLserver automatically creates this mapping for you, which you can see if you highlight the field and choose Element Get Attribute. If you edit other attributes of the field (e.g., change the size or type of field), you must keep the INPUT NAME the same as the one generated by the server.

Entry and Update Forms

Entry forms and Update forms let the user add, modify, or delete data in the database. Note that update forms are only generated for tables that have primary indexes.

This screen shows an example entry form:

General directions for entry and update forms:

On an entry form, type in (or select) the values you want and click Enter Record. A results page is displayed stating that your record was entered. It contains links to:
- the update form for the record just entered so you can modify it. Note that you cannot go backwards to the Entry form and change the data you entered, because AOLserver will then attempt to save the information as a new record rather than updating the record previously entered. (The link to the update form may or may not be provided, depending on the database driver being used.)
- the entry form for the table so you can enter more records.
- the search form for the table so you can search for records.
The Clear Fields button resets the form to the original values.
On an update form, change the values you want and click Update Record. A results page is displayed stating that your record was updated.

The Reset Form button resets the form to the original values.

Type Checking

Each column in a table has a type that restricts the values that can be inserted. For example, columns of type integer can only store integers and date columns can store only dates in a particular format. Or, columns defined as "not null" cannot be left blank. Before inserting any data into the database, the AOLserver checks to make sure that any values conform to these restrictions. If any do not, the AOLserver returns a helpful error message, e.g.:

    Value old in column age is not of type integer.

Boolean Fields

Columns of type boolean only allow values true, false, or NULL (no value, different than false). Search, Entry, and Update forms show these columns as radio buttons with labels True, False, and Unknown (NULL).

Date and Time Fields

All Search, Entry, and Update forms have special widgets for entering dates and times.

The default date and time values are the current date and time. The timestamp type is a composite of both date and time.
To enter a different date or time, select the radio button next to the date and modify the value in the widget.
To select NULL, choose the Unknown (NULL) value. AOLserver inserts a NULL (in the case of an entry or update form) or selects all rows (in the case of a search form).

Search Forms

The top portion of the search form for a table contains one line for each column in the table. The bottom portion of the search form contains a set of widgets for customizing the order and length of the search results.

This screen shows an example search form:

To the left of the column name is a check box. If this box is checked, this column will be included in the table of search results.
Directly to the right of the column name is a select box containing operators with which you can define your search criteria. The operators in this select box vary according to the type of the column:
- Numeric types, such as integer and float, have the following operator choices:
```
    =, >, <, >=, <=, <>, "is null", and "is not null"
```
- Text types (char, text, and varchar) allow the following operator choices:
```
    =, <>, contains, like, not like, is null, and is not null
```
  The text operators are case-sensitive. The like operator lets you put a % character into the string that matches any substring. For example, `Jo% Smith' matches `John Smith', `Joe Smith', and `Joseph Smith', but not `Frank Smith'. The contains operator is like the like operator, but puts a % character on either side of the value for you. For example, choosing the contains operator and typing lemur into the search value field would match the field `stories about lemurs in Des Moines'.
- Boolean types allow these operator choices:
```
    =, <>, "is null", "is not null"
```
- Date and time types have the following operator choices:
```
    =, >, <, >=, <=, <>, "is null", and "is not null"
```
The last element for each column lets the user fill in or select a value for comparison in the database according to the operator selected.
The Order by field allows you to select one or more columns on which to order the search results.
If the Eliminate duplicate rows box is checked, duplicate rows will be left out of the search results.
If you want to control the maximum number of rows that the search results will return, fill in the appropriate number in the Maximum number of records to return field.
To bring the form back to its original state, click Reset Form.
After entering the search criteria you want, click Search to start the search. If you do not change anything on the form, the server returns all rows and columns (up to the maximum to return) in the table.

Search Results

When the server processes a Search Form, it translates the form data into an SQL query and displays the results in a table.

The first column of the table contains values that are links to the Update Form for that row. The remaining columns of the table show the values of the columns that were selected on the Search Form.

The search results page contains links to the entry form and the search form for the table. If you follow a link for one of the rows in the table, the update form for that record is displayed.

Customize Entry, Update, and Search Forms

The advantage to using AOLserver's automatically-generated Entry, Update, and Search forms is that they are dynamic. A date field, for example, will always have a default value of the current date in a dynamic form, and a selection list for a field can reflect the current values in the database. Also, you do not have to store these forms on your server; they are generated each time they are requested, and they are always up-to-date with the current version of the table in the database.

If you want to customize one of the automatic forms, you can take one of the following approaches:

If you want to customize the form's appearance, use the form editing functions of AOLpress, and then save the form to a location on the server. For example, to change the size of a field, highlight the field and select Element Get Attribute. A dialog appears with the specification for the field. You can change the size or even the type of field as long as you preserve the INPUT NAME tag generated by AOLserver.

With this approach, the new form you save to the server is "frozen", and is not automatically updated with the current date or the current version of the table in the database each time you request the form.
You can customize certain aspects of the fields in the dynamically-generated forms by modifying the information about the table and its columns using the Customize Table link on the Database Services page. This approach relies on the existence of the ns2_tables and ns2_columns tables in the database pool. The ExtendedTableInfo entry in the configuration file must be set to enable the creation and use of these tables.
If you want to make more extensive customizations to the dynamically-generated forms, you can either modify the Tcl script used to generate the automatic forms, or you can write your own Tcl script (or C program) and register it to the appropriate URL.

To edit the Tcl script used to generate the automatic entry, update, and search forms, follow the Tcl Script Maintenance link on the AOLserver administration page. On the Tcl Script Maintenance page, follow the link to the nsdb directory. The scripts that generate the automatic forms are in forms.tcl.

Top of Page
Copyright © 1996 America Online, Inc.

/NS/Db/InsertRow/poolname/tablename	Adds a new row to tablename.
/NS/Db/UpdateRow/poolname/tablename	Updates a row in tablename.
/NS/Db/DeleteRow/poolname/tablename	Deletes a row in tablename.
/NS/Db/SearchQBF/poolname/tablename	Builds and executes a SQL statement from data in the form.