Database Query Forms

With a query form, an End User can query the database even when (s)he has no knowledge of SQL. The simplest query form consists of a single input field where a keyword can be entered. Many search engines use this type of query form. More complex query forms can impose specific constraints on multiple fields.

The program included with the heitml package will give you an idea of how query forms work.

The Query Form Library has basically two functions:

1. To show a query form on the screen.
2. To produce an SQL query from data that was entered into the form.

A Simple dbq Form

To use the Query Form Library, insert the following statement in your page:

< include name="dbq.hei" />

A Query Form Application normally consists of a form and the statements performing the search. The form part may look like the following:

<dbqForm se.qf> <dbqAddRelation se.qf "guestbook"> Please enter Part of the Name <dbqField se.qf "Guest_Name" 10 rel="contains"> and press <dbqSubmit se.qf>. </dbqForm>

Query Forms are enclosed in the <dbqForm> Environment Tag. se.qf is the name of the query form, or, technically speaking, the Session Variable the form data is stored in. This data must be passed as the first parameter to each dbq Tag.

<dbqAddRelation> specifies the database relation to be used (in this case, "guestbook"). The <dbqField> creates a search field for the database field "Guest_Name". The field has a size of 10 characters and the relational operator "contains" is used. Finally <dbqSubmit> creates a submit button.

When the User enters something into the search field and presses the submit button, a query is produced that searches the "guestbook" relation for all entries where "Guest_Name" contains the search field content as a substring.

For the relation parameter "rel", all the usual relational operators such as =,<,> etc. are allowed. "contains" is a special operator that uses the "like" operator of SQL. You can add flexibility to a Query Form by showing the user a select box and allowing him to select whichever relation he wishes to use.

A Query Form can contain more than one search field. In this case all constraints given by the search fields must match. If a search field is left blank, it has no effect on the search.

When the submit button is pressed, an SQL query is assembled and the content of the <dbqPerform> Tag is executed.

Here is an example:

<dbqPerform se.qf q selectall=true> <dbsQuery se.Scroller> <? q> </dbsQuery> </dbqPerform>

The q parameter is an output parameter. It contains the SQL-Query. In this example it is directly passed to the Scroller Library.

The Query Form Library works in Session Mode. This means that the form fields retain their values, even if the User surfs to some other pages and comes back again. On the other hand, it means that the form needs to be initialized prior to use as in the following example:

<if isempty(se.qf)><dbqInit se.qf></if>

This code initializes the form, and keeps the form data until the end of the User's session.

dbq Tags

< dbqInit name />

Initializes a query form. The name parameter must be the name of a Session Variable. This variable must be passed to all dbq Tags that work with this particular form. A form must be initialized prior to use. It can be initialized several times, if necessary, to reset the fields to empty.

< dbqForm name href=SrvLocalUrl > ... </dbqForm>

The Query Form must be enclosed within the <dbqForm> Tag. The name parameter must be the name of the Session Variable which contains the form data. The same name must be used for all dbq Tags that belong to the same form. There can be more than one dbq Form, even on the same page.

The href parameter specifies the name of the page that processes the form. This page must contain the <dbqPerform> Tag that belongs to the form. href defaults to the current page, so if <dbqPerform> and <dbqForm> are on the same page, the href parameter is not necessary.

< dbqPerform name *query selectall=false > ... </dbqPerform>

The inner part of the environment is processed if the submit button on the form was pressed. query is an output parameter and contains the SQL Query generated.

The query can be used in various ways, e.g. inside a dbquery Tag, or inside a <dbsQuery> Tag (see Scroller Library).

If the User has not filled out any of the fields on the query form (i.e. the SQL Query is given no constraints), the theoretically all entries in a database table qualify. In many cases, however, this is not the desired behavior, because the User might simply have forgotten to fill out a field. Therefore, an error message should normally appear in this case. If selectall=true is specified, then all rows qualify.

The <dbqPerform> Tag must be used in front of the <dbqForm> Tag. It must not be used inside the <dbqForm>, nor can it be used afterwards. If the query needed to be preserved for later use, it should be stored in a temporary variable.

< dbqField dbqname fname size name=fname maxlength=size rel="=" type="t" />

This Tag is used inside a <dbqForm> in order to create a search field. The dbqname parameter must be the name of the dbq form. The fname parameter is the name of the database field, and size is the size of the text field on the screen.

The name parameter is a field name of the search field, which must be unique within the form. If not specified, the parameter defaults to fname. It is required only if there is more than one search field for one database field. The maxlength parameter can be used to specify a larger data length for the field and works the same as in HTML.

rel is a relational operator. It is used to compare the search field with the corresponding field in each row. Possible relations are "=", "<>", "contains", "not cont", "like", "not like", "matches", "not matches", "<", ">", "<=", ">=".

Like is the SQL like operator. Contains means the search field content is a substring of the field content (it is simulated by the like operator, pre-pending and appending a '%'). Matches is a special function of some database systems. Type is the field type and has the same meaning as in the Session Field Library.

< dbqFieldName dbqname fname size name=fname maxlength=size rel="=" type="t" />

This Tag is similar to <dbqField>. It merely prints the field name followed by the relation in front of the form field.

< dbqFieldRel dbqname fname size name=fname maxlength=size type="t" any=true equality=true contains=false match=false order=false nulls=false relsize=1 />

This Tag creates a search field and a select box where the User can select a relation. The parameters dbqname, fname, size, name, maxlength, and type have the same meaning as with the <dbqField> Tag. The additional parameters specify which relations appear in the select box:

• all=true The relation all means that all rows qualify, no comparison is done. With all=true, all will be shown in the select box. An empty search field has the same effect as all.
• equality=true Puts "=" and "<>" into the select box.
• contains=true Puts "contains" into the select box.
• match=true Puts "not cont", "like", "not like", "matches", and "not matches" into the select box.
• nulls=true Puts "is null" and "is not null" into the select box.
• order=true Puts "<", ">", "<=", and ">=" into the select box.

The relsize parameter gives the size of the select box.

< dbqCheckBox dbqname name sel=null relation=null order=null field=null invert=false />

This Tag shows a checkbox on the screen. The name parameter must be a search field name that is unique within the form. If the box is not checked it has no influence on the query generated. If the box is checked the query is modified.

Applications are:

• add an additional constraint to the query (e.g. a check box named "Guest comes from Germany"). If checked, only guests from Germany qualify; otherwise all do.
• show a certain field in the output, the field is shown, only if the box is checked
• request a certain sort order, grouping etc.

Technically the parameters sel, relation, order and field have to be strings. If the box is checked then these strings are included into the query. In detail this means:

• sel the sel string is added to the where clause. If necessary it is connected by an "AND" with other parts of the where clause. Therefore, sel can be used to specify an additional constraint.
• relation the relation string must contain a valid name of a database relation. It is added to the FROM clause of the query.
• field the relation string must contain valid database field names. These are added to the list of field names to be selected. Initially the field list is empty. With <dbqAddField>, <dbqSelFi>, <dbqCheckBox> and <dbqOption> field names are added. If the list stays empty "*" is put in, which selects all fields (SELECT * FROM ...) otherwise only the given fields are selected.
• order the order string must contain a valid ORDER, HAVING, or GIVING clause (or more of them). They are inserted at the end of the query. If there is more than one dbqCheckBox with a non-empty order string, then the last one succeeds.

The invert parameter can be used to invert the meaning of the checkbox. With invert=true the query is modified if the box is not checked.

< dbqSelect dbqname name > ... </dbqSelect>

This Tag shows a select box on the screen. The name parameter must be a search field name that is unique within the form. Inside the dbqSelect only dbqOption tags are allowed.

< dbqOption dbqname sel=null relation=null order=null field=null invert=false > ... </dbqOption>

If the option is selected, then the query is modified according to the sel, relation, order, and field parameters. (See <dbqCheckBox> for a detailed description of these parameters._ If invert=true is specified, then the query is modified, if the option is not selected.

< dbqSelFi dbqname fname default=false />

This Tag shows a checkbox. If the box is checked, then the database field named by fname is included in the result relation. With default=true, the checkbox is shown initially checked.

< dbqSelFiName dbqname fname default=false />

Works like <dbqSelFi>, but prints the field name in front of the checkbox.

< dbqAddField dbqname fieldname />

Adds a field to the list of fields to be selected. Initially the field list is empty. With <dbqAddField>, <dbqSelFi>, <dbqCheckBox> and <dbqOption> field names are added. If the list stays empty, "*" is put in, which selects all fields (SELECT * FROM ...); otherwise only the given fields are selected.

< dbqAddRelation dbqname relationname />

Adds a database relation to the FROM clause of the query.

< dbqSetOrder dbqname order />

The order parameter must be a string. It is added at the end of the query. An active checkbox or option with a non-empty order string overwrites the order clause given by dbqSetOrder.

< dbqSubmit dbqname value="Query" />

Shows a submit button. When pressed, the database is searched, or more precisely, the body of the <dbqPerform> Tag will be executed. The value parameter specifies the label of the button shown, "Query" is the default.

< dbqApply dbqname value="Apply" />

Shows an apply button. When pressed, the User input is processed, but no query is performed. The value parameter specifies the label of the button shown. "Apply" is the default.

Accessing the Form Fields Directly

Form fields can be accessed directly by specifying dbqname.fields.name, where name is the name of a field. This can be useful in order to initialize the query form and to check consistency of the User input before executing the query.