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:
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:
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:
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:
This code initializes the form, and keeps the form data until the end of the User's session.
< 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.
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:
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.
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:
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.
© 1996-2022 H.E.I. All Rights Reserved.