dbq/dbs Tutorial by Keith Oustalet
The dbq or "Database Query" Library provides tools to create Search Engines of incredible power and flexibility, enabling visitors to your Web Site to specify an exact set of conditions merely by selecting options from, or typing data into a FORM. Many search engines use this type of Query, because it is simple to understand, and does not require the User to know how to create an SQL (Structured Query Language) command.
Better still, however, is that the dbq Library doesn't even require the Web Page Designer himself to have any special knowledge of SQL. In short, just as previous heitml Libraries freed you of the necessity of learning a Scripting Language, the dbq Library saves you the effort of venturing into areas better reserved for Computer Scientists.
The second Library we will be discussing is the dbs or Scroller Library. It contains Tags that manage the results returned from a Query, no matter how large or small. Using dbs, an application can be designed that stores the result of a Query on the Server, and delivers only part of it at a time to the downstream User. If the User finds what he's looking for in the first page of results, much time will be saved than if he had to wait for a huge quantity of data to finish loading into his Browser. And if the first page isn't enough, the User can proceed in an orderly fashion to subsequent pages, or skip forward over many pages, jumping directly to a page of results farther down the line.
Both of these libraries use Session Mode variables. We'll deal with the Database Query Library here, and save the Scroller Library for the next section.
Building Search Forms
The dbq Library has basically two functions:
We'll start out by showing a very simple dbq Form, then adding a bit of sophistication to it, and finally putting everything together as it would appear in a sample code file.
First, here is the usual preliminary stuff, which by now should look fairly familiar, even though it includes calls to a couple of new libraries:
|A simple dbq Form: Preliminary Elements|
<include sesfield.hei> <include NewLayout.hei> <include dbs.hei> <include dbq.hei> <TITLE>Guestbook Search</TITLE> <session mode="create"> <BODY>
We'll be using Session Variables to maintain our data, so we included a call to the Session Field Library and initiated the session using the mode="create" parameter of the <session> Tag.
The <BODY> Tag definition could incorporate <olPage> if your application includes the Outline Library and a menu.
Next comes a couple of conditional if statements:
|A simple dbq Form: Conditional Statements|
<if !isdecl(se.Scroller)> <let se.Scroller=null> </if> <if !isdecl(se.qf)> <dbqInit se.qf> </if>
The first statement checks to see whether the se.Scroller variable has been declared and, if not, creates it and assigns an initial null value. The Scroller variable will be used to control the output of a Search Query and will be explained further in the next section of the Language Guide.
Also, a dbq Form must be initialized before it can be used. This is accomplished with a call to the <dbqInit> Tag. The name parameter must be the name of a Session Variable in the form se.qf and this variable must be passed to all dbq Tags that work with this particular Form. The purpose of this variable is to create a tuple which will keep track of and control any data entered into the Form.
We enclosed the <dbqInit> Tag within a conditional if statement because it is only necessary to initialize a Form the first time it is used, or when you purposely want to clear the data fields of any previous entries by the User. As you can see, the isdecl() function tests to see whether a Session Variable se.qf has been declared and, if not, it knows that the <dbqInit> Tag has not yet been called. A test such as this should always be included at the beginning of any page containing a dbq Form. As you will see later, tests like this can be modified in several ways to control the appearance of the on-screen Form, as well as the number of options and data fields that are allowed.
Now we turn our attention to the main body of code, the part that displays the actual input Form:
|A simple dbq Form: Main Body|
<dbqForm se.qf> <dbqAddRelation se.qf "guestbook"> Please enter Part of the Name: <dbqField se.qf "Guest_Name" 10 rel="contains"> <br> and press <dbqSubmit se.qf>. </dbqForm>
The <dbqForm> Environment Tag encloses three additional Tags. The <dbqAddRelation> Tag contains two parameters: se.qf, which, as stated above, is the master tuple which keeps track of all data related to this Form, and the second parameter, which identifies the name of the SQL Table (technically referred to as a relation) to be searched. This relation is appended to the se.qf tuple and becomes part of its structure.
The next line contains a prompt that will appear on the screen in front of an empty data field. We ask the User to enter part of a name to search for. In this case, it is not necessary to enter an entire name, but only a name fragment. This can be useful when the exact spelling of a name is uncertain. Entering "Sm" will not only return any appearance of the name "Smith", but also "Smythe", "Smithers", or any other name that contains the characters "sm". In fact, the name doesn't even have to begin with "sm", but only contain them as part of its spelling.
The <dbqField> Tag can have as many as seven parameters, but we have only used four in this example. In addition to the mandatory se.qf parameter, we have also declared the data field name upon which the code will operate (in this case: "Guest_Name"), the size of the on-screen data window (10 characters), and the comparison or relation operator to use when searching through the data (e.g. rel="contains").
Specifying a relation that "contains" the characters "Sm" ensures that any appearance of those characters in the "Guest_Name" field will result in a match. A more restrictive comparison, such as rel="=" (equals) would put a higher burden on the User. Unless the User entered a full name that resulted in an exact match, the search would not return any data.
We should mention here that the maxsize parameter, which we did not use, is useful in situations where the length of a data field may exceed the useful screen area. In such a situation, a declaration such as
<dbqField se.qf "Guest_Name" 10 35 rel="contains">
would produce an on-screen data field 10 characters wide that would scroll left to accept as many as 35 characters. Note that it is unnecessary to specify the parameter names size= and maxsize= when they are used in the expected order, but only when they are declared out of sequence, as in the following example:
<dbqField se.qf maxsize=35 fname="Guest_Name" rel="contains" size=10>
|Note: For further information regarding parameters for this and other Library Tags, consult the documentation in the heitml Libraries section.|
After the <dbqField> Tag, the source code in our simple dbq Form example contains a normal HTML <BR> Tag to act as a line break, and instructions for the User to press the Form's "submit" button after having typed something into the data field. The purpose of the <dbqSubmit> Tag should therefore be self-evident, as the User is now ready to execute his Query.
|A simple dbq Form: Query Execution & Display|
<dbqPerform se.qf q selectall=true> <dbsQuery se.Scroller> <? q> </dbsQuery> </dbqPerform> <HR> <dbsScroller se.Scroller> <H2> Here is the search Result </H2> <TABLE BORDER> <dbsTableHead se.Scroller> <dbsTableBody se.Scroller> </TABLE> <P> <dbsControlLine se.Scroller> </dbsScroller> </BODY> </session>
The Query is performed via the <dbqPerform> Tag. The q parameter is an output parameter. It contains the Query result and is passed on to the <dbsQuery> Tag.
The <dbsQuery>, <dbsScroller>, and all subsequent Tags in this code sample are part of the dbs Scroller Library. We will not discuss these Tags in detail here, but include them only to complete our example in a manner that would result in a working (i.e. executable) application. For a reference of the Scroller Library Tags, look here.
We will note in passing, however, that the purpose of these Tags is to break the Query result into manageable pieces and displays them a few at a time (or not at all) depending on additional input from the User.
A Multi-Field Query Form
Now that we've covered the basic structure of a Query Form Application, we'll proceed to demonstrate how a more advanced application could be built. We will use the example outlined above as our model, and make changes and enhancements to it. The purpose here is to make you aware of some of the things you could do in your own applications that would not only enhance their professional appearance, but help you to see how the structured/modular approach we've been following makes it so much easier to modify and maintain heitml applications.
It would be foolish to think that you're first attempt to build any application would result in a masterpiece, so we'll continue our discussion of the Query Forms Library in a manner that demonstrates how easy it is to introduce small changes to your programs; changes that do not require a lot of effort, nor do they require you to throw your code away and start from scratch, yet result in a very mature and capable application that looks as though it had been designed that way from the very start.
We begin by referring you once again to any of the popular Search Engines you may have seen. Have you noticed that a lot of these search engines offer a simple one-field data entry form as a default, but also offer an additional multi-field entry form for advanced Users? Wouldn't it be great if your own applications could achieve such sophistication? Well they can, and you may be surprised to discover how little work is involved in creating a truly comprehensive Query Form that will satisfy even the most demanding Users.
Let's start by assuming that our Query Application will be based on this dual model, with a simple Query Form like the one we designed in the first part of this section as a default, and a comprehensive Form that can appear with just a single mouse click.
We'll first have to tell our application that there's another Form available. We do that by modifying the conditional if statement we included near the top of our code. Here's what the original statement looked like:
|A simple dbq Form: Conditional Statements|
<if !isdecl(se.qf)> <dbqInit name> </if>
And here's the modification:
|Conditional Statements (revised)|
<if !isdecl(se.qf)> <dbqInit se.qf> <dbqInit se.qa> </if>
Our program now knows to initialize two Session Mode Variables, one for the simple form (se.qf) and one for the advanced form (se.qa).
The next thing we need is a way to let the User know that an optional, more advanced form is available. Since we are already using Session Mode, let's use the <sessionOptional> Tag, which is part of the Session Field Library. We'll insert this Tag in the main body of our code, immediately following the code that defined our simple Query Form:
|A simple dbq Form: Main Body (revised)|
<dbqForm se.qf> <dbqAddRelation se.qf "guestbook"> Please enter Part of the Name: <dbqField se.qf "Guest_Name" 10 rel="contains"> <br> and press <dbqSubmit se.qf>. </dbqForm> <sessionOptional "Advanced" onbutton="Advanced Search Options" offbutton="Remove Advanced Options"> *** Advanced Data Entry Form Goes Here *** </sessionOptional>
The <sessionOptional> Tag creates a link for the User to click on. When the advanced Query Form is turned off, the underlined link that appears on the screen reads "Advanced Search Options". And when the advanced Form is on, the link reads "Remove Advanced Options". Clicking on the activates or deactivates the advanced Form, changing the appearance of the Web Page. The User need never know that everything happens within a single heitml Web Page that dynamically changes (and displays the results of his Queries).
Now let's take a look at some of the things we might want to do in our advanced Form. Obviously we'll need to create a new Form definition using the <dbqForm> Tag, and the first thing we should consider is giving the User the ability to restrict (i.e. focus) his Query by allowing him to enter search criteria into any or all data fields in the database.
Here's an example drawn from the data fields in our Guestbook:
|Advanced dbq Form|
<sessionOptional "Advanced" onbutton="Advanced Search Options" offbutton="Remove Advanced Options"> <dbqForm se.qa> <dbqAddRelation se.qa "guestbook"> <TABLE> <TR><TD> <dbqFieldName se.qa "Guest_Name" 10 rel="contains"> </TD></TR> <TR><TD> <dbqFieldName se.qa "Email" 10 rel="contains"> </TD></TR> <TR><TD> <dbqFieldName se.qa "Address" 10 rel="contains"> </TD></TR> <TR><TD> <dbqFieldName se.qa "Country" 10 rel="contains"> </TD></TR> <TR><TD> <dbqFieldName se.qa "Guest_Comment" 10 rel="contains"> </TD></TR> </TABLE> <P> <dbqSubmit se.qa> <dbqApply se.qa> </dbqForm> <dbqPerform se.qa q selectall=true> <dbsQuery se.Scroller> <? q> </dbsQuery> </dbqPerform> </sessionOptional>
Our simple Query Form used the <dbqField> Tag, but here in our advanced Form we elected to use the slightly different <dbqFieldName> Tag. The difference is that <dbqFieldName> prints the name of the data field in front of the spaces reserved for data entry. In most cases, the data field name should be adequate for the User to know what kind of data he can or should be entering as a selection criteria. In cases where the data field name is "xxx" or something equally useless, the Forms designer would be better off using the <dbqField> Tag and adding his own descriptive identifier.
You should also notice that we had to include a <dbqPerform> Tag for this form. The only difference between this and the previous example is that this one passes the newly declared se.qa variable as a parameter instead of the older se.qa variable.
As it stands, our new Query Form contains more selection fields, and that is certainly useful, but it doesn't offer the User any more options as to how those fields can be used.
There are a number of improvements we can make to this Form, but to begin with, we'll see if we can't define a little heitml Tag to cut down on some of the typing we have to do by entering all those <TR> and <TD> Tags.
Here's a little modification that should help:
|Advanced dbq Form: 1st revision|
<sessionOptional "Advanced" onbutton="Advanced Search Options" offbutton="Remove Advanced Options"> <dbqForm se.qa> <dbqAddRelation se.qa "guestbook"> <def stf n> <TR><TD> <dbqFieldName se.qa n 10 rel="contains"> </TD></TR> </def> <TABLE> <stf "Guest_Name"> <stf "Email"> <stf "Address"> <stf "Country"> <stf "Guest_Comment"> </TABLE> <P> <dbqSubmit se.qa> <dbqApply se.qa> </dbqForm> <dbqPerform se.qa q selectall=true> <dbsQuery se.Scroller> <? q> </dbsQuery> </dbqPerform> </sessionOptional>
We defined a Tag called <stf> that now handles all the HTML Table Row and Data Tags for us, and inserts the <dbqFieldName> Tag between them. Now our code is much easier to read, and we don't have to type so much if we should ever need to enter a new data field to our Form.
But this is only the first step in our efforts to make this form more useful. As noted earlier, the rel="contains" parameter allows data matches to be made on a somewhat flexible basis. But it would hardly be reasonable to expect that all Users would be satisfied with this. Some Users will want a data field to be confined to exact matches (equals). Others might want to use a ">=" (greater than or equals) operator. And still others might want each data field to follow its own unique selection criteria. The question then is, "How can we make our Form flexible enough to satisfy every possible set of conditions?
The answer here is to use yet another Tag from the dbq Library. The <dbqFieldRel> Tag creates both a search field and a Select Box. What this means is that the User can type "Smith" into the Guest_Name search field, and choose "=" from the Select Box. He could leave the Address field blank and accept the default "all" condition from the corresponding Select Box. He could type "USA" in the Country field and select "<>" (not equal) from the associated Select Box, thus assuring that he will only view Guestbook entries from other countries.
The following is a complete list of parameters that the <dbqFieldRel> Tag recognizes, and the values these parameters will add to a Select Box:
And now we find out another reason why the <stf> Tag we defined a few moments ago is so helpful when we want to make modifications to our code. By making one little change to our Tag definition, we can generate a sophisticated Form containing all of the Select Box options we talked about:
|<stf> Tag: revised|
<def stf n> <TR><TD> <dbqFieldRel se.qa n 10 order=true match=true nulls=true> </TD></TR> </def>
As you can see, we replaced the <dbqFieldName> Tag with the <dbqFieldRel> Tag, and we added the additional parameters we needed to build our Select Box.
In case you're wondering why we did not also include the all=true, equality=true and contains=true parameters, it is because their default value is true. In other words, we only need to specify these parameters if we want to set them to false, thus excluding them from the Select Box.