RAD for the Web

Written by Fernando Lozano

[Note: Here is a link to the msql5.zip files used in this article. Ed.]

After two months talking about Java programming, this month we'll go back to the mSQL included web gateway. W3-mSQL belongs to a class of tools called SSS, or Server-Side Scripting tools.

The most successful web development tools are SSS tools. Allaire Cold Fusion, Netscape LiveWire, Microsoft ASP and the freeware PHP are widely known examples.

You can compare Java Servlets and CGI applications to SSS tools the same way you'd compare C/C++ to RAD tools like VB or PowerBuilder: the first one is much more powerful, but also requires much more knowledge to effectively use.

Windows RAD tools also feature IDEs and language extensions specially built for creating Windows GUI and client/server applications. C/C++ development tools took much longer before they had IDEs and libraries that made them easy for standard Information Systems. Can you remember the minimal requirements for running Visual C++ 1.5 compared to Visual Basic 3.0?

Now remember the w3-msql and Java programs we built in the previous tutorials (EDM/2 July and August issues). With W3-mSQL you write an HTML page and insert some database commands. With Servlets you write a program that outputs HTML tags. The first one is easier to build and to learn. For most programmers, it's easier to think about where inside the HTML structure to put database commands than to think where inside a Java program to print HTML tags.

Just as VB lets you focus on the Windows GUI, SSS tools let you focus on the HTML layout. This is what makes SSS tools the RAD tools for web development.

The intent of this article is to leverage w3-msql as a RAD tool for the web. We will develop an Application Template that you can use and reuse, and we will also learn how to create and work with function libraries, allowing code to be reused between web pages from different web applications built with w3-msql.

The New mSQL
My previous articles have already presented you with mSQL 2.0.1B by Dirk Ohme and the new mSQL 2.0.4.1 by Yuri Dario. While the first one is well documented and easy to install, the second one has more features and less severe bugs. But it's a beta release, and as a beta it has its drawbacks:
 * It's difficult to install outside the default directory (\public\msql2)
 * Many tools do not work well with DOS text files, only with Unix text files (so use a text editor like MED, that can save text files using Unix format)
 * It does not remove temporary files after using them, and it can also keep file handles to them, causing the daemon to crash after some time.

In spite of these problems, which we hope will be solved soon (if you want to help, please mail me at lozano@blnet.com), mSQL 2.0.4.1 is already better than the previous port, because in mSQL 2.0.1 could easily corrupt indexes and crash when working with TEXT fields. Most problems with 2.0.4.1 can be worked around in predictable ways, while 2.0.1 problems could not.

Being aware of that, visit The mSQL PC Home Page (http://www.blnet.com/msqlpc/) and download mSQL 2.0.4.1 beta 3 to start working. Read the README file carefully and refer to my article in the June issue ("mSQL Revisited") for instructions on installing and running mSQL-2 under OS/2.

A Standard Web Application Design
If you browse web sites that show product catalogs, shopping carts and search engines, you'll see that they share a common basic layout. Its web pages are built around a list of items which provide links to a more detailed page about a particular item.

If you have the option to delete an item from the list, generally there's a link next to every item that performs the delete operation.

To get a complete application, we need some way to insert new items in the list and to edit the information contained in existing items. The "more detailed page" referred to above could provide the editing capability (as an HTML form), and this same page, if left with all fields blank, could provide the way to insert new items.

So our web database application consists of four pages/programs: a page for viewing records, a page for deleting records and a page for inserting or updating records.

Every operation must be triggered by clicking a hyperlink or a submit button inside a HTML form. So, an insert or update operation really needs two URLs: one for displaying the form and another to effectively commit the changes to the database. Figure 1 shows a navigation diagram representing this design.



Before we follow the rest of this tutorial, you should create a database named "pim" and create a table named "addr_book" in this database. If you already have mSQL 2.0.x up and running, the following sequence of commands should setup the environment for mSQL: msqladmin create pim msql pim < create_addr_book.sql Where the file "create_addr_book.sql" should contain the following SQL statements: drop table addr_book \g create table addr_book (            id              integer not null,             first_name      char(30),             last_name       char(30),             title           char(40),             company         char(40),             e_mail          char(40) ) \g create unique index pk_addr_book on addr_book ( id ) \g create sequence on addr_book \g select _seq from addr_book \g insert into addr_book values (    1, 'Fernando', 'Lozano', 'Project Manager',     'BL Informatica', 'lozano@blnet.com' ) \g

Implementing the Standard Web Application Desing with w3-msql
Let's start with the list page. This will be the main page of the application, and it'll have links to the insert/update and to the delete pages. It is basically the same page we've shown in the June 1998 issue of EDM/2. Let us save it as list.htm:  

 Address Book 



mSQL Web Address Book

<!   $db = msqlConnect ("localhost"); if ($db < 0) { echo ("Error: $ERRMSG\n"); exit (1); }   if (msqlSelectDB ($db, "pim") < 0) { echo ("Error: $ERRMSG\n"); exit (1); }

if (#$del > 0) { $err = msqlQuery ($db, "DELETE FROM addr_book WHERE id = $del"); if ($err < 0) { printf ("Error: $ERRMSG\n"); exit (1); }       else { echo ("Entry deleted.\n"); }   } >

 <A href="/cgi-bin/w3-msql.exe/edit.htm?id="> Insert new entry </A> <P>

</BODY> </HTML> </PRE> Copy the file list.htm to the HTML directory of your web server and run it using the following URL: http://localhost/cgi-bin/w3-msql.exe/list.htm We use the URL to pass the id of each record for the update and the delete pages: we append to the URL for the page a question mark (?) and any number of name=value pairs, separated by ampersands (&). So, to pass the parameter id=10 to the program referenced by the URL delete.htm the URL is: http://localhost/cgi-bin/w3-msql.exe/delete.htm?id=10 The delete page just asks for confirmation before deleting the record. The record is actually deleted by the list.html page, which when called from the delete.htm page gets the parameter "del" with the id of the record to be deleted.

Now, let's see the delete.htm page source: <PRE> <HTML>

 Address Book: Delete</TITLE> </HEAD>

<BODY>

mSQL Web Address Book<HR></H1>

<!   $db = msqlConnect ("localhost"); if ($db < 0) { echo ("Error: $ERRMSG\n"); exit (1); }   if (msqlSelectDB ($db, "pim") < 0) { echo ("Error: $ERRMSG\n"); exit (1); } >

<BIG> <P> <I>Are you sure you want to delete the address book entry for</I> <BR>

<!   $err = msqlQuery ($db, "\        SELECT id, last_name, first_name\        FROM addr_book\        WHERE id = $id\    "); if ($err < 0) { printf ("Error: $ERRMSG\n"); exit (1); } else { $res = msqlStoreResult ; $row = msqlFetchRow ($res); printf (      "%s, %s</B> ?       <A href=\"/cgi-bin/w3-msql.exe/list.htm?del=%s\">Yes, please.</A>       <A href=\"/cgi-bin/w3-msql.exe/list.htm\">No, thanks.</A>\n",       $row[1], $row[2], $row[0]   ); msqlFreeResult ($res); } >

</BIG>

</BODY> </HTML> </PRE> Here's the edit.htm page so you can insert some records and delete then, guaranteeing that the application works. Note that these pages are still incomplete, because they can't handle updating existing records yet: <PRE> <HTML>

 Address Book: Edit</TITLE> </HEAD>

<BODY>

mSQL Web Address Book<HR></H1> <P> <A href="/cgi-bin/w3-msql.exe/list.htm"> Back to the Address Book listing.</A> <P>

<!   if (#$id > 0) { /*        * here we would connect to mSQL and read the record identified by $id */    }    else { /*        * initialize the form to empty values */       $last_name = ""; $first_name = ""; $title = ""; $company = ""; $e_mail = ""; } >

<P> Please suply the data for the new Address Book entry:</B> <P> <FORM method="post" action="/cgi-bin/w3-msql.exe/update.htm">

<P> <INPUT type="submit" value="Insert New Entry"> <INPUT type="reset" value="Clear Form">

</FORM>

</BODY> </HTML> </PRE> The previous page just shows the form so the user can type in the data. Now here's the page that actually inserts the records. Later we will complete these two pages so they can also edit an existing record. <PRE> <HTML>

 Address Book: Update</TITLE> </HEAD>

<BODY>

mSQL Web Address Book<HR></H1>

<P> <A href="/cgi-bin/w3-msql.exe/list.htm"> Back to the Address Book listing.</A> <P> <BIG>

<!   $db = msqlConnect ("localhost"); if ($db < 0) { echo ("Error: $ERRMSG\n"); exit (1); }   if (msqlSelectDB ($db, "pim") < 0) { echo ("Error: $ERRMSG\n"); exit (1); }

if (#$id > 0) { /*        * here we would update the record identified by $id */   }    else { /*        * generate a new id from the sequence */       $err = msqlQuery ($db, "select _seq from addr_book"); if ($err < 0) { printf ("Error: $ERRMSG\n"); exit (1); }       else { $res = msqlStoreResult ; $row = msqlFetchRow ($res); $id = $row[0]; msqlFreeResult ($res); }       /*         * insert the new record */       $err = msqlQuery ($db, " \            INSERT INTO addr_book \            (id, last_name, first_name, title, company, e_mail) \            VALUES \            ($id, '$last_name', '$first_name', '$title', \            '$company', '$e_mail') \        "); if ($err < 0) { printf ("Error: $ERRMSG\n"); exit (1); }       else { echo ("               Entry for $last_name, $first_name</B> inserted                successfully.\n            "); }   } >

</BIG>

</BODY> </HTML> </PRE> You will note that these four pages have some pieces of code in common. For example, when connecting to the database, or the sequence of function calls to retrieve rows from the database.

If you write another application, you will note many more similarities: both will use more or less the same table layout for the listing page, or the same form layout for the edit page. A set of pages from the same application will have the same header, showing the company logo and a navigation bar for other pages of the application.

It's easy to see that we could create a library of functions for general use or for use only by pages from the same application. W3-mSQL allows us to do that, let's see how before we complete our web application:

Working With Lite Libraries
The programming language used by W3-mSQL inside the <! ... > marks is called lite. Lite can also be used as a scripting language in the same way you can use Perl, Python, TCL or REXX. The stand-alone lite interpreter is implemented by the program lite.exe, and you just supply it the name of an ASCII file containing the script to be run.

The keyword funct is used to define new functions. For example, a function to connect to a specific database on a specific host, showing the error messages if it cannot connect, would be: funct dbConnect (char $host, char $database) {        $db = msqlConnect ($host); if ($db < 0) { echo ("Error: $ERRMSG\n"); exit (1); }        if (msqlSelectDB ($db, $database) < 0) { echo ("Error: $ERRMSG\n"); exit (1); }        return ($db); }

And this function would be called by our application as: $db = dbConnect ("localhost", "pim"); This function could be defined inside the W3-mSQL program but it's better to store it in a separate file, so many programs would just include (or load) the functions from the same file, resulting in smaller programs and easier maintenance.

Save the function definition in a file named db_util.lite and compile it using the following command on a Command Window: lite -l db_util.lib db_util.lite This compilation does not produces machine executable code, but just a proprietary p-code file that lite and W3-mSQL understand, providing faster loading of the library.

To use a library you include the load statement in your programs, as follows: load "db_util.lib"; As an example, change the list.htm file as indicated by the following: <PRE> ...    ...     mSQL Web Address Book<HR></H1> <!    	load "db_util.lib"; $db = dbConnect ("localhost", "pim"); if (#$del > 0) { $err = msqlQuery ($db, "delete from addr_book where id = $del"); ...    ... </PRE> Copy both db_util.lib and the edited list.htm to the HTML directory of you web server, and load the page http://localhost/cgi-bin/w3-msql.exe/list.htm to verify that it still works correctly after the change.

Looking through the code of the previous examples, we see the following pieces of code that are nice candidates for the library db_util.lib: Let's see the source code for the complete db_util.lib lite library: <PRE> /*     * db_util.lite : db_util.lib library source code *     * Utility database functions for W3-mSQL applications */    funct dbConnect (char $host, char $database) {        $db = msqlConnect ($host); if ($db < 0) { echo ("Error: $ERRMSG\n"); exit (1); }        if (msqlSelectDB ($db, $database) < 0) { echo ("Error: $ERRMSG\n"); exit (1); }        return ($db); }    funct dbExecuteSQL (int $db, char $sql) {        $err = msqlQuery ($db, $sql); if ($err < 0) { printf ("Error: $ERRMSG\n"); exit (1); }    }     funct dbExecuteQuery (int $db, char $sql) {        dbExecuteSQL ($db, $sql); return (msqlStoreResult ); }    funct dbGetOneRow (int $db, char $sql) {        dbExecuteSQL ($db, $sql); $res = msqlStoreResult ; $row = msqlFetchRow ($res); msqlFreeResult ($res); return ($row); }    funct dbGetNextSeq (int $db, char $table) {        $row = dbGetOneRow ($db, "select _seq from $table"); return ($row[0]); } </PRE> Compile it as before and copy the p-code file db_util.lib to the HTML page directory of your web server.
 * Connect to the database
 * Send a SELECT statement to the database
 * Send an INSERT, UPDATE or DELETE statement to the database
 * Get the next value for a sequence

We could also think about another utility library for easier generation of HTML tags. More complex applications would be utility libraries for validating data received from an HTML form (date/time, numbers, currency, social security numbers, etc) and would also have application-specific libraries for the common layout all pages from the application should follow.

I won't build these libraries here because the article would become too long, but if you have ideas or have actually programmed some of these libraries, send me e-mail as lozano@blnet.com. The mSQL PC Home Page (http://www.blnet.com/msqlpc/) is about to start an archieve of such lite libraries.

The Complete Web Address Book Application
Now, let's rewrite the Web Address Book Application to use the library db_util.lib. We'll also complete the edit.htm and update.htm pages so they handle both inserting a new record and updating an existing record.

First the list2.htm page: <PRE> <HTML>  Address Book</TITLE> <META http-equiv="pragma" content="no-cache"> </HEAD> <BODY> mSQL Web Address Book<HR></H1> <!        load "db_util.lib"; $db = dbConnect ("localhost", "pim"); if (#$del > 0) { dbExecuteSQL ($db, "DELETE FROM addr_book WHERE id = $del"); echo ("<CENTER><P><B>Entry deleted.</B></CENTER>\n"); }    >     <P> <A href="/cgi-bin/w3-msql.exe/edit2.htm"> Insert new entry </A> <P> </BODY> </HTML> </PRE> Then the delete2.htm page: <PRE> <HTML> <HEAD> <TITLE>Address Book: Delete</TITLE> </HEAD> <BODY> <H1>mSQL Web Address Book<HR></H1> <!    	load "db_util.lib"; $db = dbConnect ("localhost", "pim"); >    <BIG> <P> <I>Are you sure you want to delete the address book entry for</I> <BR> <!        $row = dbGetOneRow ($db, "\             select id, last_name, first_name\             from addr_book\             where id = $id\         "); printf (            "<B>%s, %s</B> ?             <A href=\"/cgi-bin/w3-msql.exe/list2.htm?del=%s\">Yes, please.</A>             <A href=\"/cgi-bin/w3-msql.exe/list2.htm\">No, thanks.</A>\n",             $row[1], $row[2], $row[0]         ); >    </BIG> </BODY> </HTML> </PRE> Now the edit2.htm page: <PRE> <HTML> <HEAD> <TITLE>Address Book: Edit</TITLE> </HEAD> <BODY> <H1>mSQL Web Address Book<HR></H1> <P> <A href="/cgi-bin/w3-msql.exe/list2.htm"> Back to the Address Book listing.</A> <!    	load "db_util.lib"; $db = dbConnect ("localhost", "pim"); if (#$id > 0) { $row = dbGetOneRow ($db, " \                SELECT last_name, first_name, title, company, e_mail \                 FROM addr_book \                 WHERE id = $id \             "); $last_name = $row[0]; $first_name = $row[1]; $title = $row[2]; $company = $row[3]; $e_mail = $row[4]; $button = "Update Entry"; }        else { $id = ""; $last_name = ""; $first_name = ""; $title = ""; $company = ""; $e_mail = ""; $button = "Insert Entry"; }    >     <P> <B>Please suply the data for the new Address Book entry:</B> <P> <P> <FORM method="post" action="/cgi-bin/w3-msql.exe/update2.htm"> <P> <INPUT type=\"submit\" value=\"$button\"> <INPUT type=\"reset\" value=\"Clear Form\"> ");    >     </FORM>     </BODY>     </HTML> </PRE> And finally the update2.htm page: <PRE>     <HTML>     <HEAD>     <TITLE>Address Book: Update</TITLE>     </HEAD>     <BODY>     <H1>mSQL Web Address Book<HR></H1>     <P>     <A href="/cgi-bin/w3-msql.exe/list2.htm">     Back to the Address Book listing.</A>     <P>     <A href="/cgi-bin/w3-msql.exe/edit2.htm">     Insert Another Entry.</A>     <P>     <BIG>     <!     	load "db_util.lib";         $db = dbConnect ("localhost", "pim");         if (#$id > 0) {             dbExecuteSQL ($db, " \ UPDATE addr_book SET \ last_name = '$last_name', \ first_name = '$first_name', \ title = '$title', \ company = '$company', \ e_mail = '$e_mail' \ WHERE id = $id \ ");            $message = "updated";         }         else {             $id = dbGetNextSeq ($db, "addr_book");             dbExecuteSQL ($db, "\ INSERT INTO addr_book \ (id, last_name, first_name, title, company, e_mail) \ VALUES \ ($id, '$last_name', '$first_name', '$title', '$company',                '$e_mail') \ ");            $message = "inserted";         }         echo (" Entry for <B>$last_name, $first_name</B> $message successfully.\n ");    >     </BIG>     </BODY>     </HTML> </PRE> This set of four pages is a template you can use for your future W3-mSQL applications. It provides all the basic functionality needed by any database application. If you have search pages, they are only slight modification of list2.htm, with an associated HTML form for entering the search criteria.

Maybe you would like to have the delete2.htm page display a separate page for user feedback, just like the edit2.htm page displays the update2.htm page. Maybe you'd prefer the update2.htm page functionality to be integrated with the list2.htm page on the same way this page performs deletes.

Some people would even prefer to put all functionality on a single page, which displays different contents and performs different operations depending on the parameters it receives. Personally I do not like this approach: this kind of fat, monolithic application is the antithesis of the network computer applications made possible by the Internet.

Anyway, feel free to adapt this template to suit your own needs and preferences.

Afterword
The idea of a lite library archive comes from Peter Velikanov ([mailto:peter@jadetech.com peter@jadetech.com]). He built a nice Data Utility for browsing mSQL databases using only W3-mSQL (there's a link for downloading it on The mSQL PC Home Page).

And don't miss the next issue of EDM/2, where I'll talk about relational database design, enabling you, the readers to start building really complex applications using the tools described in my series of articles.