Query Screen
by Ken Mayer, SQA Engineer, dBASE, Inc.
Note: This was written for dB2K, but should work for dBASE Plus. There are other useful sources for information on Web Applications with dBASE as well, and you should check around.
Contents
- Introduction
- The Application
- The Application Design
- dB2K Web Applications
- The Application - The Query - Part 1
- The Application - The Query - Part 2
- The Application - Displaying An Article
- The Application - EMailing An Article - Part 1
- The Application - EMailing An Article - Part 2
- The Application - Submitting An Article - Part 1
- The Application - Submitting An Article - Part 2
- HTML Menu/Front-End
- Deployment
- Misc. Bits of Information Not Covered Elsewhere
- Sample Source Code
- Ok, I'm done ...
dB2K and its predecessor Visual dBASE 7.5 have enhanced dBASE in ways that one might have thought impossible until recently. One of the ways that these releases of dBASE have been improved is in the area of Web Applications.
The capabilities are astounding, but like anything new, it sometimes takes a bit of work to learn how to do it, how to put it all in the right perspective, and how to make it all work ...
This HOW TO document is aimed at walking you through the creation of an application that I have written to allow users to search (read-only) a table, do some 'drill' downs in that searching, and to submit a new item to that table. You can enhance this to add deletion and editing with very little effort from there ... the concepts are all there, all you need to do is look at how it works.
Table of Contents
Note: this is not a step-by-step tutorial, but a look at the code of a working dB2K web application, to show you what it's doing and why, so that you can get ideas how to create your own application (you can borrow code from this one if it will help). There are assumptions made throughout that you understand how OODML (dBASE's Object-Oriented Database Manipulation Language) works, at least to a fairly decent extent (you don't have to be an expert) and that you are fairly familiar with basic dBASE coding techniques ...
So, instead, the application we are going to look at is a collection of humor articles. Like many folk, over the years, I have collected from email and other sources quite a lot of very funny stuff (humor being relative, this is all stuff I think is funny ...). I was keeping it all in WordPerfect in a single document, but that got to be extremely unwieldy ... Eventually I put it into a table with the text of each article stored as a memo field, and the author was placed in a field, and ... anyway, you'll see all that.
I wanted to make it possible for people to find any individual article that they might be interested in, but when you have 400 or more articles, it gets a bit tricky.
However, with the use of the file WEBCLASS.CC that is installed with dB2K we make things a lot easier. In addition, with a subclass of this, called KENWEBCLASS.CC, we can add some more ease to our coding (this is included with this HOW TO document) ... We will look at both of these classes in a bit.
It should be noted that the sample application being examined in this text relies heavily on some of the methods in KENWEBCLASS.CC -- the first time these are shown in the code, I attempt to explain what they do, and why. In most cases, these methods are simply a way of not having to deal with outputting the HTML tags for some specific purpose by hand, but instead, just passing along a parameter or two ... for example, which of the following (which both do the exact same thing) is easier to write as a coder?
// Is this easier?
oCGI.fOut.puts( [<TABLE BORDER="0" CELLPADDING="0" CELLSPACING="0" WIDTH="100%">] )
oCGI.fOut.puts( [<TR>] )
oCGI.fOut.puts( [<TD BGCOLOR="LIGHTBLUE">] )
oCGI.fOut.puts( [<CENTER><H1><FONT COLOR="WHITE">My Title</FONT></H1></CENTER>]
oCGI.fOut.puts( [</TD>] )
oCGI.fOut.puts( [</TR>] )
oCGI.fOut.puts( [</TABLE>] )
// or is this easier?
oCGI.streamTitle( "My Title" )
I will attempt to describe each as we come across them in the code, but as noted, only the first time. To really understand the usefulness of these methods, it helps if you read the HOW TO document which describes Web Form Controls, as a lot of the methods in KenWebClass.cc were created to make it easier to output those controls.
Before we start it is recommended that you read both the HOW TO document on setting up Apache, if you wish to use Apache as your webserver (at least for testing if nothing else), as well as the HOW TO document on Web Form Controls, to get a good handle on the different controls used in Web Applications (and to see how they work, as they may look like dB2K form controls - but they don't act that way!). You should also take a good look at the webhelp file that ships with dB2K, as it discusses a lot of details on the relationship between the web server, your application, and so on ...
What does the application need to do? This is always a good step before you start creating an application. If you're like me you don't always do that, and you end up going back and adding functionality that should've been there in the first place ... (I do it all the time ... good intent doesn't always work <g>).
The tables -- there are only two -- the main one, and a category table, which is used as a lookup. The structure of the HUMOR table is:
Field Field Name Type Length Dec Index
1 HumorID AUTOINCREMENT 4 N
2 CATNUM LONG 4 N
3 TITLE CHARACTER 100 N
4 Author CHARACTER 75 N
5 TEXT MEMO 10 N
6 Public LOGICAL 1 N
The HumorID is a unique identifier, the "CATNUM" field is used to link to the Category table (below). The title is just that, the author is what it says, the "Text" field is the actual article in a memo, and the "Public" field is used as noted later, when someone submits a new article, to determine if it's ok for "public consumption". The 'CatnNum' field defaults (through the table designer) to '15', which is "Miscellaneous" in the category table; and the 'Author' field defaults (also through the table designer) to "Anonymous", as many of the humor articles in the table are attributed to no one in particular ...
The category table consists of two fields:
Field Field Name Type Length Dec Index
1 CATEGORY CHARACTER 50 N
2 CATNUM AUTOINCREMENT 4 N
Pretty simple. The CATNUM field links to the humor table's CATNUM field.
A datamodule is used, the only two things it does are to set the index on the HUMOR table to one called "title" -- it is a complex index ... and it sets the lookupSQL property of the catnum field.
The Humor Application needs to:
Now, once they do that, a list will be generated of any humor articles that match the conditions selected. So, the application generates a list of titles, and the user can click on the title, to view the actual humor article.
Once they read it, what next? Well, the user can do what they want, of course.
Well, it's not really complex, which was the idea. I wanted to present you, the person trying to learn this, with an application that isn't really complex, but one you could extrapolate from and expand to your own applications. There are some extra coding hints/suggestions at the end of this article to assist in some areas not covered by this application.
The file WEBCLASS.CC is an important one for designing web applications in dB2K. This one adds a huge amount of functionality to the product by encapsulating a lot of what you need to write an application into one place. It uses some of the cool new features of dB2K (and VdB7.5) to allow web applications to be written that are powerful but not all that difficult to write.
I found when working with this class that I wanted to add even more functionality, so I created a new class, called KENWEBCLASS.CC, which is a subclassed version of WEBCLASS.CC.
WEBCLASS.CC is installed with dB2K in the directory:
C:\Program Files\dBASE\dB2K 01\Classes\web
I have found that it's often easier to copy this file to where I need it for creating an application. It doesn't take much room. However, with the new source alias capabilities of dB2K, you could use it with the appropriate source alias ...
To use this with a source alias, such as "WebClass", you could change all of your set procedure statements that refer to webclass.cc to:
set procedure to :web:\classes\webclass.cc additive
One of the things you need to learn very quickly: dB2K web applications use some real tricks with the operating system, and because of those tricks, a dB2K web application does not need to create a "static" HTML document. What I mean is that the HTML document created NEVER GETS WRITTEN TO DISK! Instead, it is streamed over the wire directly to the users' computer, and again, it is not saved to disk anywhere. It makes for fast, dynamic applications. A "static" HTML file is one that is created once, and does not change ...
The reason I mention this is that you may assume that what your dB2K program will do is create an HTML file on your hard drive that you can just view. You could write the code to do that, but ...
dB2K Web Applications are quite literally simply a series of dB2K executables (.exes). These can use tables (one sort of assumes that they will) -- this requires the use of the BDE. But keep in mind that there is no user interface on the server side itself -- when your executable is done running, it quits, and dB2K is not running at that time. All of my web applications are simply a series of programs which call each other. It is possible to create a single big executable that handles everything by passing specific parameters to it, but I have found it is much easier to build a dB2K web application in a modular fashion ... it is easier to test, and to debug, and since the executables are all very small, you don't gain much (if any) advantage to keeping it all in one larger program.
One thing a web application developer needs to learn early on: ALL web pages that are forms, when they are submitted to the server, must return a response page. If they do not, a server error is generated. It is important to remember this! Even if all that your response page does is say "Thank you!", you must return one ...
This can be done in your dB2K web applications, either by creating the response page in your code or calling the webclass's errorPage() or sorryPage() methods. These last are quite useful, and will be discussed in the text below.
We'll get to using KENWEBCLASS.CC later, but for now, we will assume you want to understand the basics of WEBCLASS.CC itself.
The webclass is a subclass of the Associative Array (ASSOCARRAY) class in dB2K. This is a special form of array. If you do not understand how it works, you should check it out in online help, although just working with this webclass is likely to show you the functionality and power of the associative array.
The basic thing that is important here is that the Associative Array class in dB2K works sort of like a short-hand reference for the fields in your table. There is a "key" which would refer to the field, and a value associated with the key, which would refer to the value of the field. Even more fun, if you read the HOW TO on HTML Forms, this works exactly like the name/value pairs used with HTML forms.
Associative Arrays have methods of their own which are used for finding a key, and so on. Here is a very quick summary of how an Associative Array works:
To define the array, it works like any other class in dB2K.
aMyArray = new assocarray()
To assign values in the array:
aMyArray["KeyName"] = "somevalue"
The "KeyName" is the name used to find or reference the value, such as a field name (i.e., "LastName"). The value can be any valid dB2K data type (character, numeric, logical, date, etc.).
The one trick about associative arrays that trips up everyone, even the most experienced developer, is that the keys are case sensitive. In other words, if you have a key named "LastName", and you search for it as "lastname", it will not be found. This goes a bit contrary to the way that the dBASE programming language generally works, but it is a useful bit of functionality as well ...
Once you have loaded your array, how do you find a value?
There are a couple of ways, the first is to use the isKey() method:
if aMyArray.isKey( "KeyName" )
// do something
endif
The other option is to loop through the array. This is a bit trickier, as the way an associative array works is quite different from a "normal" array: the keys are not necessarily stored in the order you loaded them -- if you stored in your array the fields "LastName", "FirstName", and "Address", you can NOT assume that "Address" is the third item in the array. Does this seem arbitrary? Yep ... why does it do that? I have no idea.
To loop through an associative array to find a specific key, or to list the contents (as shown below), you could do something like the following:
cKey = aMyArray.firstKey // this is a property of the assocarray
for i = 1 to aMyArray.count() // count is a method ...
? aMyArray.cKey // display key name
? aMyArray[ cKey ] // display key value
cKey = aMyArray.nextKey() // move to next key in array ...
next
There are only a couple of other methods and these deal with removing keys, the first "removeKey()" removes the current key, the last empties out the associative array, "removeAll()".
When you want to create a brand new dB2K web program, you must create an instance of the web class, which as noted is a subclass of the associative array class in dB2K. So among the first lines of your program will be:
set procedure to webclass.cc additive oCGI = new CGISession() // CGISession is the actual class name
The object that is created (oCGI) is the one you will be using to do a LOT of the work in your application.
What follows is a brief description of each of the methods of the CGISession object, aimed at familiarizing you with their uses ... Note that to really understand this, you should open the program file "webclass.cc" into the source editor in dB2K and examine it. There is information in each of the methods that you should read, to get a better understanding of how all this works.
setWebMasterAddress
This is a method of this class used to assign a web master email address --
this is used when you do certain things, like generate an error page ... you
should probably set that. The code to do it is:
oCGI.setWebMasterAddress( "myemail@address.com" )
Only, of course, you would use your own email address.
connect()
You will need your application to connect to the web -- this actually executes
a bit of setup code for the class which is necessary for the application to
run.
The program statement to do this is:
oCGI.Connect()
What did that do? Well, when the program is running, what this does is:
It reads information to see if any name/value pairs are fed to the server. If they are, it reads that information from the operating system (this is one of those tricks I mentioned), and stores those as the values of the associative array. To accomplish this, it calls the method loadArrayFromCGI(). For example, if you have a web application that asks for a first and last name as "FirstName" and "LastName", this method will find that information if it was submitted, and create an array element called "FirstName" and assign the value that was submitted for it. It will then create another called "LastName", and assign the value for that. Sound complicated? To an extent, it is ... but the nice thing is that you don't have to write this code -- it's already written.
loadArrayFromCGI()
This is called, as noted above, from the connect() method, chances are
you won't need to call this at all yourself.
In addition, this method calls OEMFormat(), which deals with escape characters and such that are translated by the web browser before sending them over to the server.
errorPage()
This method is used to generate an error page as a response page (see the section
above about web applications requiring response pages).
For this to work, you must use an exception object, which is generated by the dB2K TRY/CATCH construct. A simple example would be:
try
// do whatever you need to in your web application
catch( Exception MyError )
// 'MyError' is the exception object ...
oCGI.errorPage( MyError )
endtry
What this does is to allow your application to function, but when an error occurs, rather than an error occuring that is not recoverable on the server (meaning that the web server has a session of dB2K's runtime just sitting there waiting for interaction with an error message), you can gracefully display an error for your user, they can then contact you and let you know about it. Note that in order to generate the message, errorPage() calls the oCGI method sorryPage().
sorryPage()
This is another built-in "response page", but can be used when your user does
something wrong in their use of the application. If you have, for example, required
fields that must be entered, you can check for the value, and if it is empty,
then tell them that they must fill it in:
if empty( oCGI["LastName"] )
oCGI.sorryPage( "The Last Name field is required!" )
endif
VERY IMPORTANT: use of sorryPage()
terminates the program that was running! When the user clicks the "Back" button
on their browser, the program that generated the response page is not running
anymore. If they fix their error, and click the SUBMIT button again, the program
is started again, and the error checks are run, and so on.
This avoids leaving orphaned sessions of dB2K on the web server, which as you might imagine could clog it up pretty bad ...
HTML Methods -- Used
to Create Your Web Interface
There are several methods in WEBCLASS.CC that are used to generate your own
web interface, which is necessary if you want to interact with your users properly.
These are defined below:
streamHeader() -- used to stream out the basic beginning information for the HTML document. This includes a title and more. Notice that the word "Title" (spelled in exactly that case) is a reserved word if you use the webclass -- in other words, you can't use this for any of your web form object names ...
streamBody() -- streams out the required <BODY> tag, with an optional text that can be streamed out. I tend to not use the optional text, but rather to generate my own text ... To do this here, you would use:
oCGI.streamBody( "" ) // no text
// or:
oCGI.streamBody( "My Own Text Here" )
// or:
oCGI.streamBody() // which outputs "Operation complete. Thank you!" by default
streamFooter() -- this outputs the end of the HTML document that is displayed for the user ...
Other Methods Not Used
in Example App, But Useful To Know About
There are some other methods you should be aware of that I am not using in the
sample application, but they can be very useful if you want to really automate
your web applications without much work. These are used by the web wizards to
stream out forms and get information from forms back into tables. The reason
these are not used in my own applications is that they actually do more than
what I want! I generally want to control which fields are output for the user,
etc. These will grab everything, which is fine for some applications, but may
not be what you need.
LoadArrayFromFields() -- this method loads the CGI array from the fields of a rowset object. You must pass the fields array as a parameter, so you could do it in one of the following ways:
oCGI.LoadArrayFromFields( queryname.rowset.fields )
// or
f = queryname.rowset.fields
oCGI.LoadArrayFromFields( f )
As long as the parameter is a fields array from the rowset, this will load all fields in the fields array into the oCGI object (Associative Array). Note that this assumes that the array already has the field names loaded, from controls displayed or from manually adding them to the oCGI object.
loadFieldsFromArray() -- this works the opposite from the method shown above -- it stores data back into the fields array -- if editing it overwrites what is there, if appending it creates a new record. It has two parameters -- the first is the fields array, the second is a logical value which if left off is assumed to be false, if passed as true, it says to append a new record.
// overwrite the current record -- i.e., edit it ...:
oCGI.loadFieldsFromArray( queryname.rowset.fields )
// or append a new record:
oCGI.loadFieldsFromArray( queryname.rowset.fields, true)
Note that this assumes that the array already has the field names loaded, from controls displayed or from manually adding them to the oCGI object.
loadDataModuleFromArray() -- as above, but it writes the data to a datamodule instead. It has the same optional parameter for appending. Make sure you pass a datamodule object reference rather than a fields array reference as the first parameter.
Note that this assumes that the array already has the field names loaded, from controls displayed or from manually adding them to the oCGI object.
PassDataThrough() -- this method creates a "Hidden" control that is used to pass information from one dB2K web program to another ... the user does not see it on their form, but it can be used to pass a value, such as a key value for a row, from one program to the next. This gives you the ability to maintain a "state" of sorts, in that if you are editing a row, you need to be able to tell your program which row is being edited ...
To output anything else to your form, you will need to use the command:
oCGI.fOut.puts( "whatever" ) Anything you put in the parentheses will be output to your form. This includes text, HTML, and so on.
As we go through the example application you will see that it is possible to subclass webclass.cc, and use some of the code that is there, overwrite some of the code that is there, and add new functionality -- the example for this is kenWebClass.cc, which is used extensively throughout this sample application (and is included in the sample source code). The extra methods add some spiffy functionality, but to use kenWebClass.cc, you must still have webclass.cc available, and it must be compiled into your executables.
Rather than spending more time on philosophy and design concepts, let's jump in feet first and take a look at the first part of the web application. As we go, I will make every attempt to explain the hows and whys of the design.
The first program I created is one that generates the query form -- the form used to ask the user what category or title they want to look for.
The name of the first program is query1.prg. We will examine the source code a bit at a time ... as noted above, the following assumes a basic understanding of the dB2K Object-Oriented DML (OODML), and general dBASE coding techniques.
Here's the very beginning:
/*
Query1.prg
August 2, 2000
Ken Mayer
Ask the user what they want to see ...
*/
// -----------------------------------------------
// Setup:
// -----------------------------------------------
set procedure to kenwebclass.cc additive
oCGI = new KenWebClass()
oCGI.setWebMasterAddress("kmayer@dbase.com")
// get the name/value pairs
oCGI.Connect()
Next, I need to work with the data, and for my application I am using a datamodule. The datamodule contains some basic setup, but it does include a lookupSQL on the category number field of the humor table, so that I can use a category table for the categories. It allows some flexibility. The datamodule is also used on a console application form (included in the source code), which I use to work with the data in the IDE ...
However, the datamodule doesn't have the category table specifically set in it -- and it is needed for this program, so the following code creates an instance of the datamodule, but it also creates a reference to the category table, which then requires that a database object be created (since I am using a BDE Alias to reference the tables -- always a good idea, and really a good idea with web apps!).
To set up the tables:
// Instantiate the datamodule: set procedure to "humor.dmd" additive dHumor = new HumorDataModule() // shorten rowset reference rHumor = dHumor.Humor1.rowset fHumor = rHumor.fields // Open other tables dHumorData = new Database() dHumorData.databaseName := "Humor" dHumorData.active := true // Category data qCat = new Query() qCat.database := dHumorData qCat.sql := "select * from category order by category" qCat.active := true // Shorten the references to the rowset and fields objects rCat = qCat.rowset fCat = rCat.fields
Note that I shorten my references to the rowset and the fields of the rowset -- this makes it easier to write the code I need ... and it also speeds up the processing of the code, as dB2K does not need to parse dHumor.Humor1.rowset.fields to get to a field, instead, it only has to parse fHumor to get to a field ...
Now, the next thing this app needs to do, since we have all of our table references dealt with, is to start generating the HTML Form that the user will interact with to query the data.
The webclasses provide simpler ways of doing this than by creating the HTML by hand.
Examine the following:
// ----------------------------------------------- // Start HTML Document: // ----------------------------------------------- oCGI.streamHeader( "Ken's Humor Files" ) oCGI.streamBody() oCGI.streamTitle( "Ken's Humor Files" ) oCGI.streamDetail( "<P>" ) oCGI.streamDetail( [<CENTER>Select Option to Filter On</CENTER>] ) oCGI.streamFormBegin( "query2.exe" ) oCGI.streamDetail( "<P>" )
The streamBody() method is subclassed in kenwebclass.cc from webclass.cc. This adds some functionality, allowing you to pass parameters for colors, a background image, and more. In this case we're using all defaults, and no background image. The subclassed version does not have a default text string that is displayed. (It is also possible to set the default values for these colors/images when you create an instance of the class -- see kenWebClass.cc in the source code -- check the comments at the beginning for details.)
The streamTitle() method is found only in kenwebclass.cc. It allows you to determine the colors used in the title that is displayed at the top of the HTML document ... this really streams out a very small HTML table, which allows us to define a background color for the text which you normally cannot do without streaming out a document background color. This way you can have some contrast and make the title stand out from the background ... the code shown above uses the default ("lightblue" with white text).
The streamDetail() method is found only in kenwebclass.cc, and all that it does is change "oCGI.fOut.puts()" to "oCGI.streamDetail()" -- more typing, but it makes more sense to me ... Anyway, anything you put in the parentheses is streamed out -- it can be text, HTML tags, or both ...
The streamFormBegin() method is also only found in kenwebclass.cc, and it is used to ensure that the proper FORM tag is streamed out, with the name of the dB2K program (executable) that will be called when your current HTML form is submitted by the user. If you do not wish to use kenWebClass.cc for your own applications, you must make sure that you stream out a form tag along these lines:
oCGI.fOut.puts( [<FORM METHOD="POST" ACTION="MyProgram.exe">]
where "MyProgram.exe" is the name of the program that will be executed when the form is submitted (see below). The reason the streamFormBegin() method was created is that it's easier to use:
oCGI.streamFormBegin( "MyProgram.exe" )
What do I mean, "another program is called"? Well, remember that you must return something to the user -- a response page? If your code is written in a modular fashion, like this application, then to generate the response page we must call another dB2K executable when the user selects their query criteria and clicks on the 'Submit' button.
The first part of the query is the one we are discussing here. This is asking the user what they want to find. The actual search is performed in another program, and the results of that search are displayed for the user from that program. We're calling it "query2.exe" here, so the name of the second program when I created it was called "query2.prg".
Now that we have the beginning of the form, we have to determine what to display for the user and how we are going to display it.
I have found that in order to make an HTML form look good, it is best to use an HTML table (not the same as a dBASE table -- this is a formatting tool, like a table in Word or WordPerfect) -- for more details, see the Web Form Controls HOW TO document. So, the following is streamed out:
oCGI.streamDetail( [<CENTER><TABLE>] )
We have a series of categories which are stored in the category table, so we are going to use a combobox to do this. However, in HTML, a combobox is not called that -- it is called a "select" object. And defining it is different than defining one in dB2K. If you are unclear how this works, you should read (or perhaps re-read) the Web Form Controls HOW TO document ...
We need to loop through the category table, and build our SELECT object so we can display each item in the category table.
The code looks like this (NOTE: We are using code below that is in KenWebClass.cc -- if you don't use that, you need to stream out the correct tags for the SELECT and OPTION objects yourself! The methods for streamSelectBegin(), streamOption() and streamSelectEnd() are all in this subclass ...):
// Category:
oCGI.streamDetail( "<TR><TD VALIGN=TOP ALIGN=RIGHT>" )
oCGI.streamDetail( "Humor Category:" )
oCGI.streamDetail( "</TD>" )
oCGI.streamDetail( "<TD>" )
rCat.first()
// stream out the begin tag:
oCGI.streamSelectBegin( "Category" )
// stream out a default ...
oCGI.streamOption( "-- None Selected --", "-- None Selected --", true )
// loop through the Product Category table:
do while not rCat.endOfSet
cCat = fCat["Category"].value.rightTrim()
nCat = fCat["Catnum"].value
oCGI.streamOption( nCat, cCat )
rCat.next()
enddo
// stream out the end tag
oCGI.streamSelectEnd()
oCGI.streamDetail( "</TD>" )
oCGI.streamDetail( "</TR>" )
Whew. What does all that do? Well, it streams out some table tags, and some text. It then creates the select object, and assigns a name to it. Then it creates the first option of the select object, and sets that as the default value. Why a default value? So that if the user does not select anything, we can act on it in the second program!
Next we loop through the category table, and assign the values to be streamed out for an option for each category entry in the table.
Finally, we stream out the end of the select (a select object has a 'close' tag), and then we close the table data and table row ...
Complex? Well, sort of, but not that bad, really. And best of all, it means that we have a dynamic form -- we could have created a static HTML form for this, but every time the category table got updated, then we'd have to update the form. By doing it this way, the form is created only when needed, the list of categories is always exactly what is in the table at the time ...
The next thing we want is to have a place for a user to enter a keyword (or words) to look for in the Title of the humor article -- this also uses a method from kenWebClass, called streamText() to create the "entryfield".
The code for this is:
// Title: TEXT control oCGI.streamDetail( "<TR><TD VALIGN=TOP ALIGN=RIGHT>" ) oCGI.streamDetail( "Text in Title: " ) oCGI.streamDetail( "</TD>" ) oCGI.streamDetail( "<TD>" ) oCGI.streamText( "HumorTitle", 50, "", 100 ) oCGI.streamDetail( "</TD>" ) oCGI.streamDetail( "</TR>" )
As with the select (combobox) control, we use some HTML table tags, stream out a text (entryfield) control, and we're done ...
At this point, we could add the author for the search criteria, or depending on our needs and the application at hand other types of search criteria ... we don't need it for this application, but the reason I mention this is to point out that just because I only used two criteria for the search here, you are not limited to two search criteria in your own applications ...
We need to close down the table, so we add this code:
// done: oCGI.streamDetail( [</TABLE></CENTER>] )
Finally, we need to give the user the option to submit the form or to reset/clear the form:
// buttons ...: oCGI.streamDetail( [<CENTER>] ) oCGI.streamSubmit( ) oCGI.streamReset( ) oCGI.streamDetail( [</CENTER>] )
This outputs standard "submit" and "reset" buttons. These are defined in HTML already, so we don't need to write anything to tell the form what to do with them. The methods streamSubmit() and streamReset() are in kenWebClass.cc. They have optional parameters, so you could pass whatever text you wished to, which would be displayed on the pushbuttons instead of the defaults.
The last parts of the code are pretty straight-forward:
// end of form:
oCGI.streamFormEnd()
// Stream the footer:
oCGI.streamFooter()
// -----------------------------------------------
// Cleanup ...
// -----------------------------------------------
quit
/*
End of program: Query1.prg
*/
The streamFormEnd() method (from kenWebClass.cc) is really just streaming out a </FORM> HTML tag, but this just looks 'cleaner' in code to me than trying to do this in some other fashion.
The streamFooter() method is over-written in KenWebClass.cc, and adds a bit of text, and the use of a .GIF file created by Gary White (dBVIPS), to state "Powered by:" and the dB2K gif. The .gif file also has a hyper-text anchor link to the dBASE, Inc. website. Otherwise it just closes the HTML document.
The QUIT command is not 100% necessary, but it should ensure that the dB2K runtime is not left in memory on the server.
So, What Do We
Have?
We have
a program that can be used (when compiled to a .EXE) to create an HTML form
dynamically, and ask the user for search criteria. It creates the combobox
based on whatever the contents of the category table are at the time the
user calls the program!
I have found that remembering everything needed to build the executable can be frustrating. Some folk use the project manager for this, but it feels like overkill to me, since you would need an individual project for each executable you need to create. I tend to just create a small program, the only purpose of which is to ensure that the current versions of all my files are used, and that all of the programs needed are compiled into each .EXE.
For this one program to be tested, we need to compile query1.prg, webclass.cc, kenwebclass.cc, and humor.dmd. The program for this is simple (and as I create new executables, I add the necessary commands into this program):
clear // used in nearly all ...: compile humor.dmd compile webclass.cc compile kenwebclass.cc // add specific programs: compile query1.prg // build the executables: set safety off build query1.pro, humor.dmo, kenwebclass.co, webclass.co to query1 ? "query1 program built" set safety on
As I go, I add in the other programs ... this means that every time I run this program, it builds all of the executables in my application for me.
If this runs properly (which it does for me), then bringing up the web server (I use Apache), and a browser, and entering (in the Location or Address entryfield at the top of the browser):
http://localhost/humor/query1.exe
(the 'humor' directory is contained under my wwroot directory ... it has the source code as well as the executables ...) you would see something like the following:
You can click on the combobox and see the list, and so on. But if you were to attempt to "submit" the query, unless "QUERY2.EXE" existed you would get an error from the server ...
The second program in this application will accept whatever was selected in the first program, and attempt to filter the data in the Humor table based on that. The code to set up the web application is the same in this program as it is in the first one -- the datamodule and category tables are opened and references are set up, and then we start into the "meat" of the matter. Rather than repeating that code in this document (although it is be repeated in the actual source code), we'll move down to the actual filter part.
First some error checking ...:
// -----------------------------------------------
// Filter the data, and see if we have any matches
// first
// -----------------------------------------------
if oCGI["Category"].rightTrim() == "-- None Selected --" and;
( not oCGI.isKey( "HumorTitle" ) or empty(oCGI["HumorTitle"]) )
oCGI.sorryPage( "Select a category or a keyword from a title ... can't search without at least one of these ..." )
endif
When the user clicks the "Submit" button in the HTML document displayed by the first program, they are sending over to the server (and through the server to the program 'query2.exe') some name/value pairs. The web class intercepts these (in the connect() method), and loads them into the associative array oCGI. The names are the names of the form objects -- "Category" is the name that was defined for the select (combobox) object, and "HumorTitle" is the name that was defined for the text (entryfield) object. Note that I did not use "Title" -- that is because this is a reserved word with the webclass (it is the "title" displayed in the title bar on the browser ...).
What we are doing in the "if" statement is checking to see if the user passed the "None Selected" (default) option, and if they did, we need to know if they simply pressed the "Submit" button without entering a keyword. If they did enter a keyword, and "None Selected" was passed, all is fine, and we move on. However, if the condition, as shown, is caught, we need to tell the user that we don't have anything to work with, and ask them to try again. This uses a method in the webclass.cc called sorryPage() -- this method streams out a standardized "Sorry Page" -- remember we must return something in the way of a response page, or the server will give an error. The sorryPage() method closes down the application with a "quit" statement ... the only way the user can continue to interact with the application is to use the back button on their browser.
Assuming that the user actually selected one or both of the criteria, then we have to deal with one or both of these.
Now, if the user passed along a keyword to search for in the title field, we run into a problem. As you may know, the $ operator ("is string1 contained in string2?") from XDML does not work in the dB2K OODML. The simplest thing to do is to re-establish the SQL property of the query object for the humor table, and use the WHERE and LIKE operators from SQL:
// Establish new SQL if needed:
cHumorTitle = ""
if oCGI.isKey( "HumorTitle" ) and not empty( oCGI["HumorTitle"] )
cHumorTitle = new String( oCGI["HumorTitle"] )
if not empty( cHumorTitle )
dHumor.humor1.sql := [select * from humor where upper(title) like "%]+;
upper(cHumorTitle)+[%"]
endif
endif
What this does is re-establishes the SQL for the query object -- and uses the wildcard ability of the WHERE/LIKE clauses of SQL.
If the user selected a category, we should also evaluate that, and in this case, all we need to do is set the filter property for the query based on what they selected:
// set filter on catnum field:
cCat = ""
if oCGI["Category"] # "-- None Selected --"
cFilter = "catnum = "+oCGI["Category"].rightTrim()+;
" and public = true"
rHumor.filter := cFilter
rCat.applyLocate( "catnum="+oCGI["Category"] )
cCat = fCat["Category"].value.rightTrim()
else
rHumor.filter := "public = true"
endif
Of course, nothing is as simple as it sounds. The filter is being set using the "public" field -- earlier in this document, I mentioned that I wanted to have the right to decide if an article could be viewed that someone had submitted -- this is how I do it: if the field called Public (logical) is true, then the user(s) can see it. If it is not true (false or null) either I have decided not to allow it to be viewed, or I haven't evaluated yet whether or not to allow it. The filter ensures that this is the case.
In addition, in the code above, I am looking for the actual text value of the category, as what was passed to this program was the numeric value used to link the categories. The reason I look for it (that is what the applyLocate is for) is so that I can use the text in the display of all titles that match the filter ...
If, after all that, we have no matches, we need to tell the user that their selection criteria did not work, and that they should try again. The code for this is:
// if no matches
if rHumor.count() == 0
// output a sorryPage:
cOut = "No matches were found "
if not empty( cCat )
cOut += "where the category is '"+cCat+"'"
endif
if not empty( cHumorTitle )
if not empty( rHumor.filter)
cOut += " and the word '"+cHumorTitle+"' is in the title."
else
cOut += "where the word '"+cHumorTitle+"' is in the title."
endif
else
cOut += "."
endif
oCGI.sorryPage( cOut )
endif
Basically, we are creating a string on the fly based on the condition(s) selected by the user, and telling them what works or doesn't work.
Now, if we get past this code, we can display for the user the titles of all articles that match ...
// Ok, we have matches, now for the fun part ...
// -----------------------------------------------
// Start HTML Document:
// -----------------------------------------------
oCGI.streamHeader( "Ken's Humor Files" )
oCGI.streamBody()
oCGI.streamTitle( "Ken's Humor Files" )
oCGI.streamDetail( "<P>" )
oCGI.streamDetail( [<CENTER>Select the item you wish to view from the list of matches<BR></CENTER>] )
oCGI.streamDetail( "<P>" )
cOut = "Humor items "
if not empty( rHumor.filter )
cOut += "where the category is '"+cCat+"'"
endif
if not empty( cHumorTitle )
if not empty( rHumor.filter)
cOut += "<BR> and the word(s) '"+cHumorTitle+"' is/are in the title."
else
cOut += "where the word(s) '"+cHumorTitle+"' is/are in the title."
endif
else
cOut += "."
endif
oCGI.streamDetail( "<CENTER>"+cOut+"</CENTER>" )
oCGI.streamDetail( "<P>" )
As in the sorryPage() call above, we build a string to show the user the criteria used to limit the data shown.
This also does a lot of what you saw in the first program, setting up the beginning of the HTML form to be displayed, but note that for some reason we are not streaming out an HTML form begin tag? I'll get to that in a moment.
Next we need to display the titles of the articles that match the condition(s) selected -- note we are not displaying the articles themselves, just the titles. This is called "drill-down" -- we are drilling down through the data, based on the criteria presented, until we find the item needed.
// Loop through whatever is in the match list:
rHumor.first()
do while not rHumor.endOfSet
oCGI.streamDetail( [<A HREF="DisplayIt.exe?HUMORID=]+;
rHumor.fields["HUMORID"].value+[">]+;
rHumor.fields["TITLE"].value.rightTrim()+;
[</A><BR>] )
rHumor.next()
enddo
What this code does is to loop through the table, displaying all titles that match. However, what it's also doing is creating an HTML Anchor link to the DisplayIt program (see the next part of this document), which will display a specific article. It is passing with this link a parameter (the "?HUMORID= ..." bits) so that we have a unique value to use to find the item selected. The normal anchor link requires something for the user to click on, so we display the title as the text. Then we loop through and do it again for each item to be displayed.
We add some detail at the end for the user ...
oCGI.streamDetail( "<P><CENTER>" )
oCGI.streamDetail( "Click on title to read item, "+;
"or hit 'Back' button and change your "+;
"selection criteria" )
oCGI.streamDetail( "</CENTER><P>" )
Finally, we do the usual end code:
// Stream the footer:
oCGI.streamFooter()
// -----------------------------------------------
// Cleanup ...
// -----------------------------------------------
quit
/*
End of program: Query2.prg
*/
If all works, and you run the first query, selecting criteria, such as "Star Trek" for the category, and submit it, you should see something like:
Make sure that you update the program to create your application, so it now compiles query2.prg, and builds query2.exe ...
When passing parameters to a CGI program as we're doing here, as opposed to just using the HTML submit (in otherwords, the "?HumorID= ..." part of the code shown above), if you need to pass more than one parameter (or what is really a Name/Value pair), the separator is the ampersand character. For example, if you needed to pass, a lastname and a first name, the values would need to evaluate to a string that looked like: ?LastName="somevalue"&FirstName="somevalue". The question mark is used to start a list of name/value pairs and separate it from the name of the executable; the ampersand separates the individual name/value pairs ... Reminder: the name in a name/value pair is case sensitive.
Once the user selects an article to view, we need to find it and display it in this next part of the application.
This part is fairly easy, although I decided to make things more complex and added some email functionality. We'll take a look at that later in this document.
The setup code at the beginning of this part of the application does not require the category table, so when I copied and pasted the code, I did not copy that part ...
I then set a filter:
// set the filter to the particular humorid: rHumor.filter := "humorid="+oCGI["HUMORID"] rHumor.first() // there will be only one match, but what the heck?
I could have used setRange() or applyLocate() ... any of those would work here.
The following code should be pretty familiar ... only this time we're displaying the title of the humor article at the top, and we're displaying the author and the content. However, the content is being displayed as "preformatted" text. The reason is that many of the humor articles in the table rely on standard mono-spaced font spacing for them to look 'correct'.
// Display it: // ----------------------------------------------- // Start HTML Document: // ----------------------------------------------- oCGI.streamHeader( "Ken's Humor Files" ) oCGI.streamBody() oCGI.streamTitle( "Ken's Humor Files" ) oCGI.streamDetail( "<P>" ) oCGI.streamDetail( [<CENTER>]+fHumor["Title"].value.rightTrim()+[</CENTER>] ) oCGI.streamDetail( "<P>" ) oCGI.streamDetail( [<CENTER>by ]+fHumor["Author"].value.rightTrim()+[</CENTER>] ) oCGI.streamDetail( "<P>" ) oCGI.streamDetail( "<PRE>" ) oCGI.streamDetail( fHumor["text"].value ) oCGI.streamDetail( "</PRE>" ) oCGI.streamDetail( "<P>" )
The final bits of code before the standard 'end' code, deal with calling the email form, or giving the user the option to return to the 'HTML Menu' or trying another query ...:
oCGI.streamFormBegin( "EMailIt.exe" ) oCGI.streamHidden( "HumorID", oCGI["HUMORID"] ) oCGI.streamDetail( "<CENTER>" ) oCGI.streamSubmit( "EMail This" ) oCGI.streamDetail( "</CENTER>" ) oCGI.streamFormEnd() oCGI.streamDetail( [<A HREF="query1.exe">Another query?</A><BR>] ) oCGI.streamDetail( [<A HREF="index.htm">Back to Main Menu</A>] )
Note the call to streamHidden() above -- this creates a special type of HTML Form control called a "Hidden" control - the user doesn't see it, but when the form is submitted, it is passed to the receiving program as a name/value pair. We are passing the HumorID as the value we want, as it is a unique value -- that way the email program can find it ... (You could use the passDataThrough() method of the base webclass instead ...)
There is some more code dealing with checking the registry to see if FUNCky is installed, if not, the ability to email the article is not displayed on the form. Examine the source code for "DisplayIt.prg" if you wish to see that.
Selecting a small article (so everything fits on one screen), the screen might look like:
Email? EMAIL? You mean you can EMail something from a dB2K executable? Even more, from a dB2K web application??
Er ... yeah, but you can't do it without a third-party tool, such as FUNCky or Marshallsoft, or some other email software. For this example, I am using FUNCky, since I have a copy, courtesy of Dirk Lesko. The concepts for what is shown here will be the same, but the actual syntax is likely to be different, depending on what software you use to handle email ...
The EMail routine is broken into two programs, just like the query routine, because you have to ask the user for some information before actually processing the email, and to do that you need to submit the form which calls the second program.
The first program asks the user for information in a manner very similar to the query program, but there is different information being requested. The form tag calls the "Sendit" program which actually processes the email, after verifying the values passed are acceptable.
After the initial setup code, which is a copy of the same code from previous programs, we make sure we filter on the humor article we need, using the value of the hidden object that was submitted when the email button was clicked by the user:
rHumor.filter = "HumorID = "+val(oCGI["HumorID"])
(As noted earlier, applyLocate() or findKey() could have been used here ...)
After that we generate a form:
// -----------------------------------------------
// Start HTML Document:
// -----------------------------------------------
oCGI.streamHeader( "Ken's Humor Files" )
oCGI.streamBody()
oCGI.streamTitle( "Ken's Humor Files" )
oCGI.streamDetail( "<P>" )
oCGI.streamDetail( [<CENTER>Enter EMAIL Information</CENTER>] )
oCGI.streamDetail( [<CENTER>]+fHumor["Title"].value.rightTrim()+;
[</CENTER>] )
oCGI.streamFormBegin( "SendIt.exe" )
oCGI.streamDetail( "<P>" )
// need this in sendit program:
oCGI.streamHidden( "HumorID", oCGI["HumorID"] )
oCGI.streamDetail( [<CENTER><TABLE>] )
// From (provide some defaults)
oCGI.streamDetail( "<TR><TD VALIGN=TOP ALIGN=RIGHT>" )
oCGI.streamDetail( "From (name): " )
oCGI.streamDetail( "</TD><TD>" )
oCGI.streamText( "FromName", 50, "Ken Mayer's Humor Files", 100 )
oCGI.streamDetail( "</TD></TR>" )
oCGI.streamDetail( "<TR><TD VALIGN=TOP ALIGN=RIGHT>" )
oCGI.streamDetail( "From (EMail): " )
oCGI.streamDetail( "</TD><TD>" )
oCGI.streamText( "FromEMail", 50, "kmayer@dbase.com", 100 )
oCGI.streamDetail( "</TD></TR>" )
// To:
oCGI.streamDetail( "<TR><TD VALIGN=TOP ALIGN=RIGHT>" )
oCGI.streamDetail( "To (name): " )
oCGI.streamDetail( "</TD><TD>" )
oCGI.streamText( "ToName", 50, "", 100 )
oCGI.streamDetail( "</TD></TR>" )
oCGI.streamDetail( "<TR><TD VALIGN=TOP ALIGN=RIGHT>" )
oCGI.streamDetail( "To (EMail): " )
oCGI.streamDetail( "</TD><TD>" )
oCGI.streamText( "ToEMail", 50, "", 100 )
oCGI.streamDetail( "</TD></TR>" )
// Subject:
oCGI.streamDetail( "<TR>><TD VALIGN=TOP ALIGN=RIGHT>" )
oCGI.streamDetail( "Subject: " )
oCGI.streamDetail( "</TD><TD>" )
oCGI.streamText( "Subject", 50, "[Humor] "+;
fHumor["title"].value.RightTrim(), 100 )
oCGI.streamDetail( "</TD></TR>" )
This is minimal information. We could provide a place to add comments by the sender, and more ...
This is really about it, so we add the last bits, and we're done:
// done: oCGI.streamDetail( [</TD></TR>] ) oCGI.streamDetail( [</TABLE></CENTER>] ) // buttons ...: oCGI.streamDetail( [<CENTER>] ) oCGI.streamSubmit( "EMail It ..." ) oCGI.streamReset( ) oCGI.streamDetail( [</CENTER>] ) // end of form: oCGI.streamFormEnd() // Stream the footer: oCGI.streamFooter()
When this is executed, it will look like:
The second program involved in the email routine validates that we have everything, loads the FUNCky routines, creates an instance of the FUNCky SMTP mail object, and then creates the actual email message and sends it.
That isn't real difficult, but it can take a bit of code ... we'll look at the parts briefly ...
The setup routines are mostly as they are in the other routines, with the exception that we also add a statement to load the file FUNCKY.CC into memory:
set procedure to funcky.cc additive
Next we need to validate the items that were submitted in the emailit program, to be sure nothing is empty that is required:
// -----------------------------------------------
// Error check for required items:
// -----------------------------------------------
if not oCGI.isKey( "FromName" ) OR ;
empty( oCGI["FromName"] )
oCGI.sorryPage( "Need a name that message is sent from!" )
endif
if not oCGI.isKey( "FromEMail" ) OR ;
empty( oCGI["FromEMail"] )
oCGI.sorryPage( "Need an email address that message is sent from!" )
endif
if not oCGI.isKey( "ToName" ) OR ;
empty( oCGI["ToName"] )
oCGI.sorryPage( "Need a name that message is sent to!" )
endif
if not oCGI.isKey( "ToEMail" ) OR ;
empty( oCGI["ToEMail"] )
oCGI.sorryPage( "Need an email address that message is sent to!" )
endif
if not oCGI.isKey( "Subject" ) OR ;
empty( oCGI["Subject"] )
oCGI.sorryPage( "Need a subject for the email message!" )
endif
Note that we only see one sorryPage(), no matter how many empty values there are on the emailit form -- if the first field is empty, we call the sorryPage, and everything stops. The user must click the 'back' button on their browser and fix it. If they're having a bad day, this could get a bit frustrating. We could have had just one sorryPage call and listed each of the missing items, but that is an issue of style ...
Assuming the user filled in all the blanks, we need to actually build the email message and mail it. The following code does this:
// ----------------------------------------------- // EMail humor item ... // ----------------------------------------------- fEMail = new FUNCkySMTP() // the server is vital: make sure it's correct ... // this is the SMTP Outgoing EMail server fEMail.Server = "myMailServer.myISP.net" // from user: fEMail.fromName = oCGI["FromName"] fEMail.fromAddress = oCGI["FromEMail"] fEMail.toName = oCGI["ToName"] fEMail.toAddress = oCGI["ToEMail"] fEMail.subject = oCGI["Subject"] cMessage = center( fHumor["Title"].value.rightTrim(), 100 ) cMessage += chr(13)+chr(10)+chr(13)+chr(10) cMessage += center( "By: "+fHumor["Author"].value.rightTrim(), 100 ) cMessage += chr(13)+chr(10)+chr(13)+chr(10) cMessage += fHumor["Text"].value // message to send fEMail.message = cMessage
The code shown above creates an instance of the FUNCky SMTP mail object, and assigns properties to it that are requred, based on the user's responses. The bit with the string "cMessage" is building the message content to display the title, author, and text, with some blank lines (the call to chr(13)+chr(10)) in between.
Once you have that, you send it -- or attempt to. If an error occurs, you want to trap it, so that the user gets either a response that states that it was sent, or a response that states that something was wrong, and what it was. The following code does this:
if fEMail.send(true)
// output a page here that returns info about it having
// been sent, and to whom ...
// -----------------------------------------------
// Start HTML Document:
// -----------------------------------------------
oCGI.streamHeader( "Humor Message Sent" )
oCGI.streamBody()
oCGI.streamTitle( "Humor Message Sent" )
oCGI.streamDetail( "<P>" )
oCGI.streamDetail( [<CENTER>]+fHumor["Title"].value.rightTrim()+[</CENTER>] )
oCGI.streamDetail( "<P>" )
oCGI.streamDetail( "Successfully emailed:<P>" )
oCGI.streamDetail( "From: "+oCGI["FromName"]+;
" at "+oCGI["FromEMail"]+"<BR>" )
oCGI.streamDetail( "To: "+oCGI["ToName"]+;
" at "+oCGI["ToEMail"]+"<BR>" )
oCGI.streamDetail( "Subject: "+oCGI["Subject"] )
oCGI.streamDetail( "<P>" )
oCGI.streamDetail( [<A HREF="query1.exe">Another?</A>] )
oCGI.streamFooter()
else
// output an error
if fEMail.status > 0
oCGI.sorryPage( "SMTP Status: "+fEMail.status+"<BR>"+;
"Status Message: "+fEMail.statusMessage )
elseif fEMail.error > 0
oCGI.sorryPage( "SMTP Error: "+fEMail.error+"<BR>"+;
"Error Message: "+fEMail.errorMessage )
else // unknown error
oCGI.sorryPage( "An unknown error has occurred ... sorry."+;
" Please check your entry and try again ..." )
endif
endif
The user (and the server!) has their response page, so you ask the user (if successful) if they want to try again ...
The rest of the program is simply a "Quit" statement ...
The next step is to allow someone to submit an article to the humor database. This is appending a row in a table ... sounds pretty basic? It is ... really.
First we need to create a form to present to the person submitting the article. We're going to create it dynamically in the same fashion we've done with other parts of this application.
The setup code for the program "submit1.prg" is pretty much the same as in other programs in the application. This one uses the category table to build a select object (combobox), so if you look at the full code, you will see that the database object and the query for the category table are created in the setup code.
The next part of the code is the beginning of the HTML document that will be displayed for the user:
// ----------------------------------------------- // Start HTML Document: // ----------------------------------------------- oCGI.streamHeader( "Ken's Humor Files" ) oCGI.streamBody() oCGI.streamTitle( "Submit Item to Ken's Humor Files" ) oCGI.streamDetail( "<P>" ) oCGI.streamDetail( [<CENTER>Enter All Information Requested</CENTER>] ) oCGI.streamFormBegin( "Submit2.exe" ) oCGI.streamDetail( "<P>" ) oCGI.streamDetail( [<CENTER><TABLE>] )
We are creating a form, so we need to tell the form what the name of the next program will be, and we're going to use the unimaginative but appropriate "submit2.exe" for this.
Just like other parts of this application this routine just creates the form for the user to interact with, and the second program actually appends the new record to the table ...
The code to create the first item we need -- the category, is below:
// Category:
oCGI.streamDetail( "<TR><TD VALIGN=TOP ALIGN=RIGHT>" )
oCGI.streamDetail( "Humor Category:" )
oCGI.streamDetail( "</TD>" )
oCGI.streamDetail( "<TD>" )
rCat.first()
// stream out the begin tag:
oCGI.streamSelectBegin( "Category" )
// loop through the Product Category table:
do while not rCat.endOfSet
cCat = fCat["Category"].value.rightTrim()
nCat = fCat["Catnum"].value
oCGI.streamOption( cCat, cCat, iif( cCat == "Miscellaneous", true, false))
rCat.next()
enddo
// stream out the end tag
oCGI.streamSelectEnd()
oCGI.streamDetail( "</TD>" )
oCGI.streamDetail( "</TR>" )
Note that the line that streams out the option is setting the default value for the category to "Miscellaneous" -- this way, if the user doesn't know what to select this is what gets picked.
Next we need the title, author, and the text of the article -- there isn't a need to discuss these in much detail, except to note that the author is being given a default value of "Anonymous", the text field has a default of "Insert Text Here", as well as a note under the editor control about keeping the width to 80 characters or less for each line.
// Title: TEXT control oCGI.streamDetail( "<TR><TD VALIGN=TOP ALIGN=RIGHT>" ) oCGI.streamDetail( "Title: " ) oCGI.streamDetail( "</TD>" ) oCGI.streamDetail( "<TD>" ) oCGI.streamText( "HumorTitle", 50, "", 100 ) oCGI.streamDetail( "</TD>" ) oCGI.streamDetail( "</TR>" ) // Author: TEXT control oCGI.streamDetail( "<TR><TD VALIGN=TOP ALIGN=RIGHT>" ) oCGI.streamDetail( "Author: " ) oCGI.streamDetail( "</TD>" ) oCGI.streamDetail( "<TD>" ) // set default to Anonymous oCGI.streamText( "Author", 50, "Anonymous", 100 ) oCGI.streamDetail( "</TD>" ) oCGI.streamDetail( "</TR>" ) // Text: TEXTAREA control oCGI.streamDetail( "<TR><TD VALIGN=TOP ALIGN=RIGHT>" ) oCGI.streamDetail( "Text: " ) oCGI.streamDetail( "</TD>" ) oCGI.streamDetail( "<TD>" ) oCGI.streamTextArea( "HumorText", 10, 50, "Insert Text Here" ) oCGI.streamDetail( "<I>Please keep columns to 80 characters or less ...</I>" ) oCGI.streamDetail( "</TD>" ) oCGI.streamDetail( "</TR>" )
One we have all that, we want a bit more information that isn't stored in the table (for this application ...), but is used anyway in the next program ... these are the name and email address of the person who submitted the article:
// Submittor name/email: oCGI.streamDetail( "<TR><TD VALIGN=TOP ALIGN=RIGHT>" ) oCGI.streamDetail( "Submittor Name: " ) oCGI.streamDetail( "</TD>" ) oCGI.streamDetail( "<TD>" ) oCGI.streamText( "SubName", 50, "", 100 ) oCGI.streamDetail( "</TD>" ) oCGI.streamDetail( "</TR>" ) oCGI.streamDetail( "<TR><TD VALIGN=TOP ALIGN=RIGHT>" ) oCGI.streamDetail( "Submittor EMail: " ) oCGI.streamDetail( "</TD>" ) oCGI.streamDetail( "<TD>" ) oCGI.streamText( "SubEMail", 50, "", 100 ) oCGI.streamDetail( "</TD>" ) oCGI.streamDetail( "</TR>" )
These can be used to generate a thank you email message to the person who submitted the item (or whatever you might need them for) ...
The rest of the code is the submit/reset button, and the cleanup code ... No need to repeat all that here.
When run, this form looks like:
The second half of the code used to submit an article is a bit more interesting. We need to verify that the user passed on all the fields, and so on ... and then we get to more email stuff ...
For this to work, the setup code is not using the datamodule this time, but instead we are opening the humor table "straight up" as it were -- this means that the lookupSQL on the category field is not being used (the reason for this is that I could not get the value to be saved properly ... probably something in my own code ...). Opening the tables looks like the following:
// Rather than use the datamodule, we are // going to make sure we can store the // category information properly, and with // the lookupSQL, there is a difficulty I haven't // been able to work around ... dHumorData = new Database() dHumorData.databaseName := "Humor" dHumorData.active := true // Category data qHumor = new Query() qHumor.database := dHumorData qHumor.sql := "select * from humor" qHumor.active := true // shorten rowset reference rHumor = qHumor.rowset fHumor = rHumor.fields // Category data qCat = new Query() qCat.database := dHumorData qCat.sql := "select * from category order by category" qCat.active := true // Shorten the references to the rowset and fields objects rCat = qCat.rowset fCat = rCat.fields
We need to set the procedure for the FUNCKY.CC (or whatever email software you might want to use). There is also code in here to see if FUNCky is registered, and if not, the code to generate an email is not executed -- if you wish to examine that code, check the full source for "submit2.prg".
The error checking code looks like:
// -----------------------------------------------
// Look at what was submitted, make sure it's
// all here:
// -----------------------------------------------
if not oCGI.isKey( "Category" ) OR ;
empty( oCGI["Category"] )
oCGI.sorryPage( "Select a category!" )
endif
if not oCGI.isKey( "HumorTitle" ) OR ;
empty( oCGI["HumorTitle"] )
oCGI.sorryPage( "Must have a title for this ..." )
endif
if not oCGI.isKey( "Author" ) OR ;
empty( oCGI["Author"] )
oCGI.sorryPage( "Enter 'Anonymous' if no author or author unknown ..." )
endif
if not oCGI.isKey( "HumorText" ) OR ;
empty( oCGI["HumorText"] )
oCGI.sorryPage( "Need the text of the humor article!" )
endif
if not oCGI.isKey( "SubName" ) OR ;
empty( oCGI["SubName"] )
oCGI.sorryPage( "Need the name of the person submitting this to my system!" )
endif
if not oCGI.isKey( "SubEMail" ) OR ;
empty( oCGI["SubEMail"] )
oCGI.sorryPage( "Need the EMail address of person submitting this to my system!" )
endif
As in other parts of this application, the sorryPage() method call stops execution of the application, and the user must use the 'back' button on their browser to actually update the information ...
The simple part of the program is actually adding the new record to the table, except that in order to get the correct category information, we are using the category number, which is done below using a filter:
// ----------------------------------------------- // Find catagory 'number' based on the // category text: // ----------------------------------------------- rCat.filter := "category = ["+oCGI["Category"].rightTrim()+"]" nCat = fCat["CatNum"].value
Then we store the actual article:
// ----------------------------------------------- // Store it: // ----------------------------------------------- rHumor.beginAppend() fHumor["CatNum"].value := nCat fHumor["Title"].value := oCGI["HumorTitle"] fHumor["Author"].value := oCGI["Author"] fHumor["Text"].value := oCGI["HumorText"] rHumor.Save()
Now that this has been saved, the last part of the program sends an email, but it doesn't send it to the person who submitted the article -- they know they've done it. It sends it to me, so I can know that something's been added (otherwise I would have to constantly be checking the table ...). This way I can then go find the article, examine it, look at the formatting if necessary, and if I like it I can set the 'public' flag and it can be viewed by the public ...
The code for this looks like:
// -----------------------------------------------
// Send email to Ken ...:
// -----------------------------------------------
fEMail = new FUNCkySMTP()
// the server is vital: make sure it's correct ...
// this is the SMTP Outgoing EMail server
fEMail.Server = "mail.megapathdsl.net"
// from user:
fEMail.fromName = oCGI["SubName"]
fEMail.fromAddress = oCGI["SubEMail"]
fEMail.toName = "Ken Mayer"
fEMail.toAddress = "kmayer@dbase.com"
fEMail.subject = "New Humor Article ..."
cMessage = center( fHumor["Title"].value.rightTrim(), 100 )
cMessage += chr(13)+chr(10)+chr(13)+chr(10)
cMessage += "-- Check this to see if you want to allow it ..."
// message to send
fEMail.message = cMessage
if NOT fEMail.send(true)
// output an error
if fEMail.status > 0
oCGI.sorryPage( "SMTP Status: "+fEMail.status+"<BR>"+;
"Status Message: "+fEMail.statusMessage+"<BR>" )
elseif fEMail.error > 0
oCGI.sorryPage( "SMTP Error: "+fEMail.error+"<BR>"+;
"Error Message: "+fEMail.errorMessage )
else // unknown error
oCGI.sorryPage( "An unknown error has occurred ... sorry."+;
" Please check your entry and try again ..." )
endif
endif
// output a page here that lets the user
// know it's submitted, and thanks them.
// -----------------------------------------------
// Start HTML Document:
// -----------------------------------------------
oCGI.streamHeader( "Humor Article Submitted" )
oCGI.streamBody()
oCGI.streamTitle( "Humor Article Submitted" )
oCGI.streamDetail( "<P>" )
oCGI.streamDetail( [<CENTER>]+fHumor["Title"].value.rightTrim()+[</CENTER>] )
oCGI.streamDetail( "<P>" )
oCGI.streamDetail( "Successfully Submitted -- thanks!<P>" )
oCGI.streamDetail( "I will examine it at the next opportunity, and if I feel it is acceptable,"+;
"I will turn it 'on' for public consumption ..." )
oCGI.streamDetail( "<P>" )
oCGI.streamDetail( [<A HREF="index.htm">Back to Menu</A>] )
oCGI.streamFooter()
Note that the response page lets the user know that the article was submitted.
That's it for this program except for the 'quit' ... the article is submitted, the administrator of the system is notified (by email) that a new article exists ... we're done.
For all of this to work, we really need one static HTML document, which is effectively a menu for the application. We could call it "menu.htm", but it's probably a good idea for your main HTML document to be called by "index.htm" (or "index.html") so that the server software knows what to look for automatically.
For this particular application, there are only two "entry" points, one is the query1 program (which follows through the drilldown to the specific article, then allowing the user to email the article), or the submit1 program (allowing the user to submit the article).
The index.htm for this application does not need to be very complex.
For testing purposes, I have kept everything in the same directory -- source code, data, HTML, images, etc. See the section on deployment for some suggestions there.
To create the INDEX.HTM, you can use either dB2K's source editor, or WordPad (in Windows), or Notepad, or ... any text editor. (If you use WordPad, make sure you save your HTM document as text, or it will default to a Word 6 document ...)
The INDEX.HTM source is given below, in sections, like the source code for the application.
The beginning of the HTML document looks like:
<HTML>
<HEAD>
<TITLE>Ken's Humor Files</TITLE>
<! This is a sample web application, built entirely using
dB2K, except for the email interface, which is done
using Dirk Lesko's FUNCky 6.0 product. It was designed
to help show users of dB2K how to build a Web Application.
This is just the front-end HTML document ... -->
</HEAD>
<BODY>
To see how the text is displayed across the top in the colors shown, see the HTML here:
<TABLE BORDER="0" CELLPADDING="0" CELLSPACING="0" WIDTH="100%"> <TR><TD BGCOLOR="LIGHTBLUE"> <CENTER> <H1> <FONT COLOR="WHITE"> Ken's Humor Files </FONT> </H1> </CENTER> </TD></TR> </TABLE>
To sum up, it's a mini HTML table with one table row, one table cell (table data) ... the colors are defined as shown, and there you have it.
Some more text is used to give an overview of what this website is about:
<CENTER> <I>"If we couldn't laugh, we would all go insane ..." -- Jimmy Buffet</I> </CENTER> <P> Greetings, and welcome to a site that should be interesting, as well as educational, at least for dB2K developers interested in seeing how to build a web application using dB2K itself. <P>
The actual 'menu' part, which gives the user selections is:
<HR> <P> <FONT COLOR=BLUE SIZE=+1>Menu</FONT> <P> <A HREF="query1.exe">Find a Humor Article</A><BR> <A HREF="submit1.exe">Submit a Humor Article</A> <P>
Note that no "path" is given for the executables (or the zip file) -- if you store this HTML in WWROOT, and the tables in a CGI-BIN folder, you need to add the "CGI-BIN/" (or whatever the path is) to the location of the files, or you will get an error from the server that they cannot be found. More details on this in the deployment section ...
The rest of the INDEX.HTM document looks like the following:
<HR> <P> For details on dB2K, please visit the dBASE, Inc. website: <P> <A HREF="http://www.dbase.com">http://www.dbase.com</A> <P> For details on the use of FUNCky (which is used to handle the email parts of this application), see this website: <P> <A HREF="http://www.funcky.com">http://www.funcky.com</A> </BODY> </HTML>
This includes links to the dB2K and FUNCky websites (since I am using FUNCky, I only feel it fair to advertise it to folk who might be interested).
Now that you have your menu, and your code, all you need to know is how to get it all to the server it will really be used on.
At this point, it is assumed you have done a lot of testing of your code, trying to find errors and so on, the usual debugging process.
If you are ready to deploy a dB2K web app (either to a server you are adminstering, or to another server somewhere), here are some tips that may help ...
Location of files
Various people have various opinions on this one. One person espouses a solid
"three folder" rule, which basically means storing the HTML documents, the executables,
and the data in three different locations.
Others don't feel you need to be that stringent. There are many ways to examine this.
What I recommend is that the data be kept in one location, the executables in another, and the source be kept on your development machine. The data can be kept in the same folder as the executables or not, but you should always have a BDE Alias and use that.
For security reasons, it may be a good idea (hackers are devious, eh?) to keep the data in a separate folder ...
So, you might have something defined like:
C:\MyHumorApp
Store the HTML index/menu here
C:\MyHumorApp\Data
Store the tables (.DBF, .MDX, .DBT) here
C:\MyHumorApp\Exes
(or CGI-BIN) store the executables and images
here
If you use the layout shown above, make sure that the INDEX.HTM has each of the links set to point to the EXES folder, i.e.,:
<A HREF="Exes/query1.exe">Find a Humor Article</A><BR>
Note that most web server software is case sensitive for the paths ...
On your dB2K CD is a folder called "Runtime", which contains several executables that have one purpose, to deploy the dB2K runtime files and BDE files necessary to set up an application. You can use these for your console apps (see the How To document on working with Inno Setup), but they can be used here as well ...
All that needs to be done is for one of them to be run. If you need to set up your BDE Alias, do this after the BDE/Runtime is installed. You may need to provide instructions to the person actually running the installer/setting your application up on the server; or you could write code using BDEAlias.cc in the dBASE Users' Function Library (in the Knowledgebase for dB2K).
The reason there are multiple runtime install files is so that you can deploy the one appropriate for your language. This list should help:
dB2KRuntimeEngine_EN.exe English dB2KRuntimeEngine_DE.exe German dB2KRuntimeEngine_ES.exe Spanish dB2KRuntimeEngine_IT.exe Italian dB2KRuntimeEngine_JA.exe Japanese dB2KRuntimeEngine.exe All of the above, will ask for language when run
If you actually studied the code, you now have the basics that you really need to create a web application. However, there are some bits that may be useful below that were not discussed in the text above, or only briefly discussed ...
I didn't spend much time discussing how to deal with errors in your web applications in the text above.
If you examine the source code for the sample application, you will find that I inserted a try statement toward the beginning of each program, with a catch/endtry toward the very end. This is a simple way to attempt to catch all errors in your web application. However, you should note that this may not be sufficient. You may want to nest try/catch/endtry statements for specific commands, to trap specific possible problems.
The try/catch mechanism in dB2K is great -- it makes it very easy to catch most errors. On a Web application, if you do not attempt to catch errors, you may find your web application taking the web server down -- this is because many errors in dB2K create dialog boxes that require that the error be acknowledged. If this happens, your user will see nothing; the server will sit there waiting for a response; and you have just locked up one session of the dB2K runtime engine. The try/catch construct shuold catch all errors of this sort ...
By using the try/catch mechanism you can trap errors, and present the user with an error. The error may not mean much to your user, but your user can then communicate with you, telling you what the error is. The webclasses have a method called "errorPage" that is designed to take the exception class that is created by the CATCH ... example:
try
// do something in code
catch( Exception MyError ) // create exception object if error occurs
oCGI.errorPage( MyError ) // pass exception object to errorPage method
endtry
While this is a rather simple example, it should give you an idea. For more details on this extremely powerful capability, see the online help for dB2K ...
A Minor Problem ...
I found an interesting aspect of the errorPage() method in the webclass.cc
file that is also replicated in the version in kenWebClass.cc. The problem that
occurs is if an error is caught by your try/catch AFTER the form's header and
body tags are streamed out. What I have seen happen is one of two things:
What's interesting here (at least to me) is that the error is actually streamed out to the browser, but it's just not displayed. If you examine the "Page Source" (use the "View" menu for Netscape or Internet Explorer, it will be titled either "Page Source" or "Source"), you will see the tags and the error streamed out ... Why doesn't it display? I have no idea ...
In kenWebClass.cc you will find a new method called errorMessage(). I recommend that you consider nesting a try/catch inside your code that will execute after you have streamed out your header and body tags. Something along these lines:
// wrap application in a try/catch:
try
// set up your tables
// whatever else you need to do
// stream out the header/body tags:
oCGI.streamHeader( "MyApplication" )
oCGI.streamBody()
// nested try/catch here:
try
// the rest of your application
catch( Exception MyError )
oCGI.errorMessage( MyError )
endtry
// this is for the first 'try':
catch( Exception MyError )
oCGI.errorPage( MyError )
endtry
The errorMessage method in kenWebClass.cc does everything that the errorPage method does, except that it does not attempt to create a new web page -- in other words, it does not generate the header and body tags -- this seems to work in the testing I have done with it, and the display isn't quite as weird.
Another Way To Handle
Errors
There are several ways to do anything in dBASE code, and another solution is
to avoid the problem noted above completely. The way this is done is that rather
than using any of the streaming code in the webclasses, you build a string of
characters (in dB2K a character string can be very very large -- it is effectively
limited only by the available memory of your computer), and if you have not
hit any errors (you would still use a try/catch block to catch errors and output
an errorPage()), you then output the single string to your user ... this
might look something like (this is VERY simple):
try
// Header:
cOut = "Content-type: text/html"
cOut += "<HTML>"
cOut += "<HEAD>"
cOut += [<META HTTP-EQUIV="Content-Type" CONTENT="text/html">]
cOut += "<TITLE>"+cTitle+"</TITLE>"
cOut += "</HEAD>"
// End of the header, on to body:
cOut += "<BODY>"
// Stream out whatever else you want ...
cOut += "Operation complete, thank you!"
// Whatever processing needs to take place, adding to cOut
// as shown
// add the end of the HTML document:
cOut += "</BODY></HTML>"
// output the whole page at once:
oCGI.fOut.puts( cOut )
// all done ...
quit
catch( Exception MyError )
oCGI.errorPage()
endtry
An enterprising developer could create a subclass of WEBCLASS.CC that build the string using methods of the class, rather than having to write the HTML themselves ...
Debugging a web app is a bit more difficult than debugging a Windows application, because you have the added layer of the server ...
Here are a few hints:
Your application may act like it's working properly, but the display is wrong, something isn't displaying at all, etc. How can you determine what is streaming out? Take a look in the browser, under the "View" menu (in Netscape/Internet Explorer -- I can't speak about other browsers) you will find a menu option "Page Source" or "Source" -- click on that. You can see all of the HTML tags that were streamed to the browser. You can then see if maybe you left something out. I find that often if you accidentally leave out an angle bracket (< or >) the browser gets lost and doesn't know what to do, or it formats things in a really odd fashion.
Other things besides missing brackets: starting a textarea control and not ending it ( </TEXTAREA> ), same for a select control ( </SELECT> ) -- in either case, you form will not display everything.
Starting a table, and not ending it will cause some odd results as well. More fun is forgetting to put your <TD> or <TR> (or closing tags) around something you wanted in the table -- these items will end up displaying above the table, rather than in it ...
Other debugging: you should be able, with the use of the try/catch construct as shown earlier in this document, to catch most or all errors in your code.
However, if your code goes into a loop that you forget to set a way out of, you can run into problems -- dBASE doesn't know that it's in an endless loop, the web server doesn't know, and so on. Be careful with looping through data and be sure to include the rowset.next() call, or whatever you need ...
Depending on your application's needs, you might want to allow the user to find a record and edit or delete it. You could use the query code (as a place to start) in QUERY1.PRG and QUERY2.PRG, and rather than just displaying it, generate an HTML document (much like we've done elsewhere here) and set the value for each of the controls to the value of the fields of the row that was found from the queries.
When your user submits the form, another program would find the record (using a hidden control, for example to hold a key field's value), and update the data.
If you wanted to delete the current record you could get fancy and require that they they click a button on yet another screen, to delete the item -- that way there is verification ...
Navigating through data should be pretty easy, really. You know how to pass a hidden key value from one program to another, you could have one program that handled navigation, and pass another hidden value that was the direction of the navigation ... and then in the program you would find the current record, then issue the appropriate navigation command (first(), last(), next(), next(-1) ...).
The sample application does not work with checkboxes as there are no logical fields in the application (that are interacted with by the users of the application). The reason I wish to discuss this here is that checkboxes are a bit tricky in HTML forms.
The problem is that unlike a checkbox in a dB2K form, where the value is either true or false, the value of an HTML checkbox is either the value property (if the checkbox has been checked) or it does not exist. What this means for your CGI application is that if the user does not check the checkbox, then no name/value pair is streamed through the server to your application for the checkbox.
This can be frustrating in your code as you will get errors if you do not check to see if this name/value pair exists at all. You can check to see if a value was passed with:
if oCGI.isKey( "MyCheckBox" ) // user checked it
// do something
else
// do something else
endif
If the key does not exist, the user did not check the checkbox. You can assign a true/false value to a logical field based on whether or not the checkbox was checked:
qMyQuery.rowset.fields["MyLogical"].value := oCGI.isKey( "MyCheckBox" )
When filtering data, or using applyLocate() to find information that your user enters, you have very little control over how the data is entered. If the user enters a last name as "SMITH", and it is stored in your table as "Smith", finding it can trip you up.
This is actually a very simple fix, and if you are aware of this capability, you can skip over this section. The rowset has two properties that can help here, filterOptions and locateOptions -- these both have the same possible values, and they both default to the same values ...
The default is "0 - Match length and case" - this means that the exact length of what the user types, as well as the exact case must match.
You can set this to ignore the length, ignore the case, or ignore both. For searching via the web, it may be quite useful to use the last option, and to do that, the syntax is very simple:
qMyQuery.rowset.filterOptions := 3 // Match partial length and ignore case
For more details, check filterOptions or locateOptions in online help.
The source code and data for the humor application are included in the file WebAppSource.zip.
To get everything to run properly, run the program file "HumorApp.prg" to start -- this sets the BDE Alias and it runs the Windows application side of things ...
To compile all the web programs and build the application, run the program "MakeWebApp.prg" in dB2K. This will do all the work.
Note that I cannot legally hand off FUNCky's .DLLs, so it's not included in the source code zip file. I have modified the source code to check for FUNCky, and if it's not there, it doesn't allow the e-mailing routines to function properly. This is done by using the registry.prg and winreg.h files in the samples directories of dB2K, and checking for a registry key that is set when FUNCky is installed.
If you work with this code and modify it for your own use, the information regarding the mail server should be modified to point to your own email server, and so on ... please don't use mine ... (If I was paying attention, the final release of the source code does not include my server information in any of the programs ...)
As you can see, dB2K web applications are pretty much unlimited in what they can do.
For example, one of our (dBASE, Inc.) customers has created a Chat Room using dB2K, others have done some pretty amazing things as well ... if you can think of it, chances are, you can do it ...
Enjoy!