Creating query forms
Parsing user queries

Queries filter the current database using a logical xBase language expression. These expressions filter the database so that only those records that match the expression are available. baserunner has two methods of generating query statements.

• Expressions may be passed directly to baserunner using the _fil, _tempfil, and _deffilter variables

• baserunner can generate a query statement based on a user's input from an HTML form.

Using expressions directly

Passing expressions directly gives you the most control over how the database will be presented. And, since these expressions may be generated on the fly in the application, they represent one of the most powerful features of baserunner. The 3 variables you may use to pass expressions differ in the way they apply the expression to the database:

The _deffilter variable is normally used only in the application configuration file. If you use a _deffilter variable in your application configuration file, the expression you use as a value will always be in effect. If a user queries the database, the value of _deffilter will be appended to her query. _deffilter variables have the disadvantage of always having your application in a filtered state.

The _tempfil variable is used to apply a temporary filter to the database. Passing an expression with the _tempfil variable has the same effect as use _fil, but the query statement and record positioning, and target are discarded immediately after the call. This is most useful when querying a second database with data from the first.

The _fil variable is the useful. It may be passed on the command line, through any form, or as a part of a query form. If it is used as part of a query form, multiple _fil variables can be processed.

Using the _fil variable from the command line (or a form that does not use the query action) to pass an expression to baserunner sets the query statement to the value of _fil (and _deffilter, if it exists). Only one _fil variable will be processed in these cases. When used this way, _fil bypasses the query generation routines: a _fil variable is always accepted by baserunner as is--it is not processed or changed in any way. Because of that, you need to take care that it is properly formatted.

Note: When you need to pass an expression on the command line, for instance, with a _fil variable, you will need to URL encode any characters that would normally be encoded if the expression were sent through a form. 

baserunner will URL encode any text contained within the delimiters <%. . .%> (these delimiter characters may be changed by sending alternate characters through the _uencodestart and _uencodeend variables). So, for instance, if you send a _fil variable on the command line, you could encode that value like this: _fil=<%expression%>. URL encoding takes place after all tags have been processed, so your expression may contain tags referring to field values.

You do not need to encode expressions passed through forms or those contained in the configuration files.

The _fil variable may also be used to clear the current query by passing it with 0 (zero) as a value from the command line.

Query forms

When querying the database through a form that uses one of the query actions, baserunner parses the user input to generate a valid query statement. The three query actions are: query, refine, and revert. Within a query form you may include variables that contain expressions that will be added to the query statement without processing. To accomplish this, you name the input field _fil. A query form will process multiple _fil variables.

If you send expressions to the query engine through _fil or _deffilter that include numeric or date values, these must be included in the expression as character values. Numeric field expressions need to be in the format: (num_field=val("123")). Date field expressions need to be used in the format (date_field = ctod("01/15/1998")). baserunner's query engine expects all elements in a query to use the same format. Mixing different types in a query statement will cause unpredictable results. User entered values for dates and numbers are all converted to character values by the query engine.

A query form can take several actions:

The query action causes a new query statement to be generated and applied to the database.

The refine action causes a query statement to have additional information appended to it and the new statement applied to the database..

The revert action causes the immediately previous query statement to be restored and applied to the database. Revert does not call the query engine. It simply replaces the current query statement with the previous statement:

<form action="/cgi-bin/baserun.exe?_ses={session}&_act=revert&_tar=target.htm" method="GET"> <input type="submit" value="Use Previous Query">

Submitting a query action that includes no field variables clears the current query (which may also be accomplished by sending a _fil variable with a value of 0 (zero)), e.g:

Creating query forms

A query form consists of input fields  that have the name of the field(s) that should be queried with the value sent by the input field. In other words, a input field named field_name will construct a query statement for the database field named field_name. A query form input may be named with multiple database field names as well.   baserunner will also process input variable named _fil, these inputs, like the _fil variable passed from the command line, will contain xBase expressions that will be added without change to the query string constructed by baserunner. In addition to these inputs, baserunner will process four additional variables in a query form:

_matchall is a logical variable. If it has any value, the various parts of the query will be joined with a logical and. By default different part of the query are joined with a logical or. The effect of this variable is to only return a match if all the parts of the query statement are true; otherwise a match is made if any of the parts of the query statement are true. If you wish to make _matchall the default you may include it in the application configuration file, or you may place it in the form as a hidden variable. If you wish to give the user the option, you may add the variable to the form with a checkbox:

_case is a logical variable that makes the query case sensitive. By default all queries (of character and memo fields) are not case sensitive. This variable is handled in the same ways _matchall is.

_exact is a logical variable that has the same effect as using both _case and _matchall. This variable also changes the default operator for character and memo field queries from "contains" to "equals".

_sortorder takes an expression as a value. The expression may not return a logical value. The expression determines the order in which the queried data will be returned to the user. The sort order variable may also be used with a form using the sort action. Most often this input would be a menu:

Setting a sort order has a severe effect on the performance of a baserunner application. Sorting should be avoided unless the database is filtered down to a fairly small number of records.

Under Windows NT with the Microsoft IIS server, baserunner uses different record positioning code when sorting is in effect, since the standard code used to set record positioning does not work. baserunner automatically determines whether IIS is the server. Other Windows servers work with the normal record position code. However, since there are many servers out there and baserunner has not tested all of them, you may need to use the IIS code for some other Windows servers. You can test this by setting a _sortorder variable on your database and attempting to use the "back" navigation action. If this action hangs the application, you need the IIS code. You may force baserunner to use the IIS code by setting the logical variable _use_iis_sort.When this variable is present, baserunner will default to the IIS code when navigating a sorted database. If you are running baserunner under IIS, using this variable may provide a slight performance benefit since it will bypass the code that checks the server software.

Input variables for queries will normally be either text boxes or menus. In the case of a logical field it is often easier to use a check box or radio buttons. Each input field will refer to one or more fields in the database.

Logic field queries need to return a value of "T" or "F". This can be accomplished through a checkbox, radio buttons, or a single selection list.

<input type="checkbox" name="log_field" value="T"> <input type="radio" value="T" name="log_field"> Yes <input type="radio" value="F" name="log_field"> No <select name="log_field" size="1"> <option> </option> <option value="T">True</option> <option value="F">False</option> </select>

Memo, character, date, and numeric fields may all be queried using edit boxes. The name of the input should be the same as the field. Date fields can be queried using edit boxes, but the input from the user needs be in the format specified by the _dateformat variable. This should be noted on the query form.

These fields can also be queried through a combo box that gives the user a menu of selections. Such a menu may get the values from variables in the configuration file--This is useful if the value contains < or > characters that can mess up the HTML. Note that options that contain white space must be enclosed in quotes. baserunner's query engine will handle both single and multiple selections. If you use a single selection list, if you don’t want the user to be forced to select a value, you can add a blank entry at the top, though there are probably many situations where you might want to force a selection. With a combo box you can also use _fil as the name, which will send the value through without parsing by the query engine:

More than one field may be queried with a single form field. To query multiple fields use a list of field names as the name of the input separated by blanks. Generally, these multiple fields should be of the same type.

Parsing user queries

Users do not need to enter xBase style expressions. The baserunner query parsing engine will convert their input into xBase expressions.

Logical field queries require an input that sends a value of true or false (actually, T, t, Y, y for true, anything else for false).

Date field queries require that the input(s) are formatted in same format specified in the _dateformat variable. Date inputs may include multiple entries and relational operators.

Numeric field queries require an input value using digits. Numeric inputs may include multiple entries and relational operators.

Character and memo field queries require text input. The user may enter words or phrases. A phrase (any value that includes white space) must be enclosed in double quotes: "some phrase". Any input, other than Logical fields, may contain multiple numbers, dates, words, or phrases. Multiple entries are simply separated by spaces.

The baserunner query parsing engine also recognizes several relational operators that the user may enter along with the values he wishes to query for:

For text fields, the default operator is "$" (contains). This means that if the user enters a word or phrase, if the field contains that word or phrase in any form, it will be returned as a match. The user may override this by explicitly using other operators.

 

For numeric and date fields the default operator is "=". The user may override this by explicitly using other operators.

By default, multiple values are separated by a logical "or". This means that a field that matches any of the user’s entries will be returned as a match. The user may override this by explicitly using "and" between to entries.

 

By default, queries are not case sensitive. You may allow the user to change by providing a checkbox named _case, or if you want queries to be case sensitive by default, you may add the _case variable to the configuration file.

 

By default, query statements for different fields are joined by a logical "or". This means that if any field the user queries returns a match the whole record will be returned as a match. You may allow the user change this by providing a checkbox named _matchall, which will match only records where all queried fields return a match. You may make this the default by adding the _matchall variable to the configuration file.

 

The _exact variable has the same effect as using both _case and _matchall as well as using the "=" operator for character and memo field queries.

For example, a user could enter >=100 and <=250 to find a range of numbers when querying a numeric field, or >=01/15/1960 and <=01/06/1966 to find a range of dates. Using the operators with character or memo fields has the effect of specifying the alphabetical place of the value, for instance, = H will query for all entries that contain a capital H, or >B and <D will find entries that begin with a capital C. Words, phrases, and values are separated by spaces. Any operator acts on the next value in the query. baserunner does not care whether there is a space between the operator and the value or not (except for "and", "or", and "not").

A query form that uses refine as its action will take the user's input and append it to the current query statement. This has the effect of querying within the current query. In many situations, for both queries using a query action and queries using a refine action, you can use unique lists to generate menus from the actual data: