MSQL Revisited

From EDM2
Jump to: navigation, search

Written by Fernando Lozano

(Note: the source code for this article is msql.zip here. Ed.)

This article is an expanded version of the one published in the May issue of EDM/2. The first one used mSQL 1.0.16 and presented its simple but useful web gateway.

After the article was published, some OS/2 programmers looked at the mSQL 2.0.x ports to see if they could compile them and fix the bugs that prevented me from using the newer releases on the original article. They quickly provided me with patched mSQL binaries and sources, and then I dropped my plans to write an article about Java and mSQL to write this update of the previous article to cover the new features and syntax of mSQL-2. Thanks to everone who made this article possible!

Parts of this article are the same: installation and first use steps are described as in the previous article, but changed to the syntax of mSQL-2. Then we pick up where the previous article ended and expand the web address book application to use some features not present in mSQL-1.

What changed in mSQL 2?

The second revision of the miniSQL relational database server adds many nice features to the server and to the web gateway. On the server side, we have:

  • New data types, like the TEXT type, which is similar to the "memo" type found on some DBase and Access variants.
  • The ability to define many indexes per table, on any fields we want.
  • Complex conditions on the WHERE clause.
  • Sequences for primary key generation.

And on the web gateway, we have:

  • New syntax with better readability of the code, that resembles C and Perl programming.
  • Built-in functions and loop statements.
  • Run-time error handling.

If mSQl-1 already had great success on the internet, then mSQL-2 has everything to become the light-weight database of choice.

A Quick Start

Let's use Dirk port, as it comes with DLLs compatible with most OS/2 C/C++ compilers. You can download it from ftp://ftp.nerosworld.com/pub/msql/contrib/msql201b.zip. The installation is simple: just extract the files with Info-Unzip or PkZip 2.50 (using the /dir option) to a temporary directory and run the REXX script os2_inst\install.cmd, passing as argument the directory where you wish to install the package. I used:

E:\temp\mSQL-201\OS2_NST] install e:\soft\msql2

The script copies the executables, documentation and files required for C development (*.h and *.lib) into the specified directory and creates a WPS folder containing program objects for configuring and administering the database. You will also get the mSQL-2 manual in both INF, PostScript and HTML formats.

Before you start the server, you need to have the loopback TCP/IP interface enabled and the name "localhost" defined in your hosts database. If you have Warp Connect or Warp 4, just open the TCP/IP Configuration Notebook. Right on the first page, you have the list of interfaces. Select "loopback adapter", check "Enabled" and give it the address 127.0.0.1, which is the default.

Then go to the second Hostnames page and add the IP address 127.0.0.1 with the hostname localhost. You may then need to reboot to enable the interface.

If you have just Warp 3 with IAK, you can still enable the loopback interface. You will have to enable it using the ifconfig command. The Apache for OS/2 docs tell you how to do that, check at http://www.slink.com/ApacheOS2.

Let's check if our TCP/IP configuration is working. Open a command window and type:

  [E:\] ping 127.0.0.1

  PING 127.0.0.1: 56 data bytes
  64 bytes from 127.0.0.1: icmp_seq=0. time=0. ms
  64 bytes from 127.0.0.1: icmp_seq=1. time=0. ms

  ----127.0.0.1 PING Statistics----
  2 packets transmitted, 2 packets received, 0% packet loss
  round-trip (ms)  min/avg/max = 0/0/0

This is a working setup. A bad setup would get something like:

  [E:\soft]ping 127.0.0.1
  PING 1.2.3.4: 56 data bytes
  sendto: Network is unreachable
                        ping: wrote 1.2.3.4 64 chars, ret=-1
  sendto: Network is unreachable
                        ping: wrote 1.2.3.4 64 chars, ret=-1

  ----1.2.3.4 PING Statistics----
  2 packets transmitted, 0 packets received, 100% packet loss

You have also to test the hostname. Try "ping localhost" and see if you get the same output as with the numeric IP address.

You also need to have mSQL.dll somewhere in your LIBPATH. The easiest way is to add the bin directory of mSQL to LIBPATH (I added the directory e:\soft\msql2\bin). You can also add the bin directory to your PATH so you have easy access to any msql command-line utility.

Before we start the server, there's still a last configuration step we have to do. Edit the file msql.conf, located in the mSQL bin directory, or double-click the Configuration icon in the mSQL folder. The entry "admin_user" tells who can do administrative operations on the server, like creating databases or stopping the server. Type any name you like, I used "msqldba". On Unix, this would be a valid logon on the system, but as OS/2 does not have true local user security, the user name is obtained from the environment variable USER (the same used by some IBM TCP/IP utilities). So, you can type:

set USER=msqldba

Or put this command in your CONFIG.SYS.

Now, let's test if the server is working. Don't forget to reboot your computer!

To start the server, either double click "Start Server" or run dbStart.cmd from any OS/2 command prompt. The easiest way to check if the daemon is running is to issue the command "msqladm version" from any OS/2 command prompt. You should get something like:

  E:\soft\msql2\bin] msqladmin version

  Version Details :-

          msqladmin version       2.0.1 for OS/2, Sep  7 1997
          mSQL server version     2.0.1 for OS/2, Sep  7 1997
          mSQL protocol version   23
          mSQL connection         127.0.0.1 via TCP/IP
          Target platform         OS/2, 32Bit (VisualAge C++)

  Configuration Details :-

          Default config file     /PUBLIC/mSQL2/msql.conf
          TCP socket              1114
          UNIX socket             e:\soft\msql2/msql2.sock
          read timeout            10
          current user            msqldba
          mSQL user
          Admin user              msqldba
          Install directory       e:\soft\msql2
          PID file location       e:\soft\msql2\bin\msql2.pid
          Memory Sync Timer       30
          Hostname Lookup         False

That's fine, we have mSQL up and running!

Working with mSQL

Now the first thing to do is to create a database. Each database in mSQL is a collection of related tables and indexes which are not tied to any user or "schema", unlike commercial database servers. A single mSQL daemon can manage multiple databases. You can control which users get read and write permissions to each database, and from which hosts, by editing the file msql.acl with any text editor, but I have to tell you that security in mSQL is not strong. It uses just a user name, that the client sends as clear text when connecting, and does not ask for any password. If you are afraid that someone could scramble your tables, allow just local access from a trusted host.

But let's create your first database. You can use the "Create DB" icon (assuming you put "set USER=msqldba" in your CONFIG.SYS) or you can type the following command to create a database named "pim":

[E:\EDM2]msqladm create pim

Before we can use the created database, we must set its permissions. To do so you can open the icon named "Access Control List" or open the file msql.acl with any text editor. You should add the following lines:

  database=pim
  read=*
  write=*
  host=*
  access=local,remote
  option=rfc931

This is the easiest way to start, letting anyone connect from anywhere and make anything. Later we can set up more strict permissions.

To make the changes effective, you have to reload the server. This can be done from the command line, using the command

[E:\EDM2]msqladmin reload

Or you can double-click the icon "Server Reload" (remember that "set USER=msqldba" needs to be in your CONFIG.SYS for this to work).

Now we can finally create some tables in our databse to put some data into. mSQL comes with an SQL Monitor so you can submit SQL commands interactively. It can be started by invoking msql.exe from any OS/2 command prompt, passing the name of your database as a command-line argument, or by double-clicking the "Local Monitor" icon.

Start the mSQL Monitor and type the following statement:

  mSQL >create table addr_book (
      ->        id              integer not null,
      ->        first_name      char(30),
      ->        last_name       char(30),
      ->        title           char(40),
      ->        department      char(40),
      ->        company         char(40),
      ->        tel             char(20),
      ->        fax             char(20),
      ->        e_mail          char(40)
      ->) \g

The "\g" at the end of the statement instructs the SQL Monitor to send the request to the database server. This resembles Academic Ingres iSQL (have you already read Date's book?).

If you typed the above statement correctly, you should get the message

  Query Ok. 1 rows modified or retrieved.

We could also create indexes and sequences to speed access to our data and to simplify programming, but now we want to see it running as fast as we can.

Now we can enter some data to our table (won't show the prompts):

  insert into addr_book values (
          1,
          'Fernando',
          'Lozano',
          'Writer',
          'N/A',
          'EDM/2',
          null,
          null,
          'lozano@blnet.com'
  ) \g

  insert into addr_book values (
          2,
          'David',
          'Hughes',
          'Creator',
          'R&D',
          'Hughes Technologies',
          null,
          null,
          'bambi@hughes.com.au'
  ) \g

  insert into addr_book values (
          3,
          'Dirk',
          'Ohme',
          'Porter',
          null,
          null,
          null,
          null,
          'dohme@nerosworld.com'
  ) \g

And now let's try some simple queries:

  mSQL >select last_name, first_name, e_mail
      ->from addr_book
      ->order by last_name, first_name
      ->\g

  Query OK.  3 row(s) modified or retrieved.

   +-------------+--------------+------------------------+
   | last_name   | first_name   | e_mail                 |
   +-------------+--------------+------------------------+
   | Hughes      | David        | bambi@hughes.com.au    |
   | Lozano      | Fernando     | lozano@blnet.com       |
   | Ohme        | Dirk         | dohme@nerosworld.com   |
   +-------------+--------------+------------------------+


  mSQL >select last_name, title
      ->from addr_book
      ->where company = 'EDM/2'
      ->\g


  Query OK.  1 row(s) modified or retrieved.

   +--------------------------------+------------------------------------------+
   | last_name                      | title                                    |
   +--------------------------------+------------------------------------------+
   | Lozano                         | Writer                                   |
   +--------------------------------+------------------------------------------+

That's fine for the moment. To exit the SQL Monitor just type "\q".

You can run SQL scripts with the SQL Monitor using I/O redirection. Suppose you had the created table above saved in the file create_addr_book.sql (do not forget to end the file with the command "\q".) You could execute this file by typing at an OS/2 command prompt:

  [E:\EDM2]msql pim < create_addr_book.sql

  Welcome to the miniSQL monitor.  Type \h for help.


  mSQL >     ->     ->     ->     ->     ->     ->     ->     ->     ->     ->
  Query OK.  1 row(s) modified or retrieved.


  mSQL >
  Bye!

If we were lucky, you now have a working mSQL server, with a database and one table filled with data. However, many of us will find it boring to type SQL statements on the monitor. Actually, mSQL is not an end-user tool. It comes with no nice GUI front-ends, no report generators and the like, although you can find some very nice ones on the Internet. It's just a back-end server with the bare minimum supporting tools. But there is something more interesting included in the basic mSQL archive.

The Web Gateway

As mentioned at the begining of this article, mSQL comes with its own web gateway called w3-msql.exe. Copy this file to the cgi-bin directory of your local web server - I can't believe you won't have one! ;-) For example, if you installed IBM ICS at C:\WWW, the cgi-bin directory is by default "C:\WWW\CGI-BIN". Another example, if you installed Apache for OS/2 at c:\httpd, the cgi-bin directory is "c:\httpd\cgi-bin".

Try the following URL just to see if your web server is correctly configured to run w3-msql:

  http://localhost/cgi-bin/w3-msql.exe

If you get a page stating that no file was found, everything is ok.

  W3-mSQL Error!  -  Can't stat script file ()

  Error at line 1

If you get any other result, either your web server dos not support the Common Gateway Interface (CGI) or it is not configured to run CGI scripts from the directory you put w3-msql.exe in (or the URL /cgi-bin is mapped to a different directory).

Now save the following file as addr_book.htm in the HTML directory of your web server:

   <HTML>
   <!-- Exemplo do w3-msql -->
   </HEAD>
   <TITLE>Address Book</TITLE>
   </HEAD>
   <BODY>
   <H1>mSQL Web Address Book</H1>
   <HR>
 
   <!
           $db = msqlConnect ("localhost");
           if ($db < 0) {
                   echo ("Error: $ERRMSG\n");
                   exit (1);
           }
           if (msqlSelectDB ($db, "pim") < 0) {
                   echo ("Error: $ERRMSG\n");
                   exit (1);
           }
 
   >
 
   <P>
   Here are the entries of our address book:
   <P>
   <TABLE border>
   <TR><TH>Last Name<TH>First Name<TH>e-mail
 
   <!
           $err = msqlQuery ($db, "select last_name, first_name, e_mail" +
                             "from addr_book" +
                             "order by last_name, first_name");
           if ($err < 0) {
                   printf ("Erro! $ERRMSG\n");
                   exit (1);
           }
           else {
                   $res = msqlStoreResult ();
                   $row = msqlFetchRow ($res);
                   if (#$row > 0) {
                           while (#$row > 0) {
                                   printf ("<TR><TD>%s<TD>%s<TD>%s\n",
                                           $row[0], $row[1], $row[2]);
                                   $row = msqlFetchRow ($res);
                           }
                   }
                   else {
                           echo ("<TR><TD colspan=3>No entries found.\n");
                   }
                   msqlFreeResult ($res);
           }
   >
 
   </TABLE>
   </BODY>
   </HTML>

And load it with the following URL:

  http://localhost/cgi-bin/w3-msql.exe/addr_book.htm

You should get a page like this:

  mSQL Web Address Book

  ---------------------

  Here are the entries of our address book:

  +---------+---------+--------------------+
  |Last Name|Fist Name|e-mail              |
  +---------+---------+--------------------+
  |Hughes   |David    |Bambi@hughes.com.au |
  +---------+---------+--------------------+
  |Lozano   |Fernando |lozano@blnet.com    |
  +---------+---------+--------------------+
  |Ohme     |Dirk     |dohme@nerosworld.com|
  +---------+---------+--------------------+

(I guess you can design a better-looking web page than that. I just want to keep HTML code simple so it does not obscure the w3-msql tags that are the purpose of this article.)

That example needs more explanation. W3-msql.exe reads an HTML file, and filters all tags delimited by <! and > in a way that resembles standard Server-Side Includes (SSI). W3-msql gets the file name from the PATH_INFO variable passed to every CGI script by the web server. The (virtual) path to the file should be appended to the w3-msql.exe URL. If, for example, my page was under the directory /pim, the URL to invoke w3-msql on that page would be:

  http://localhost/cgi-bin/w3-msql.exe/pim/addr_book.htm

Many web servers (like Apache) let you associate a file extension with a CGI program so the file gets automatically proccessed by w3-msql, just like files with extension .shtml are proccessed by the SSI filter.

Any lite statement may be contained inside the <! ... > delimiters. W3-mSQL processes these statements, which include standard C-like if..then..else constructs and while loops, and sends the web browser just the results. The user has no idea the HTML page he received was generated by a program.

Inside the lite commands, functions like msqlConnect() or msqlQuery() give access to mSQL databases. The lite scripting language can be used outside W3-mSQL, using the lite.exe program, which can be useful for batch-mode proccessing.

Variables in lite do not need to be declared prior to use. All standard CGI environment variables are pre-declared, as are any HTML form variables that get POSTed or are included on the URL that invokes W3-mSQL.

The function msqlQuery() sends the SQL statement passed as an argument to the mSQL Server and the function msqlStoreResult() creates a handle (in this case "$res") so you can refer to its returned data later.

Then we fetch the first row of the resultset using msqlFetchRow(), and if no row was returned, we print the appropriate message. If we do have a row, we loop though the result set printing all rows inside a nicely-formatted HTML table.

You can see that lite has a syntax that resembles C and Perl very much, so it won't be difficult to learn.

Hey, had you ever thought it was so easy to put a database on the web? :-)

A Search Engine

To publish database data listings on the web is fun, but most database-enabled web sites have some kind of search form where users can specify what information they want. W3-msql can use any variable sent from an HTML form or as part of the URL, so we will modify our previous example to use some inputted data.

Save the following text as name_f.html:

   <HTML>
   <HEADER>
   <TITLE>Address Book Search Form</TITLE>
   </HEADER>
   </HTML>
   <BODY>
   <H1>Address Book Search Form<HR></H1>
   <P>
   Type the desired name, or part of, on the following fields:
   <BR>(If you leave a field blank, that stands for "don't matter")
   <P>
   <FORM action="/cgi-bin/w3-msql.exe/name_r2.htm" method="post">
   <TABLE>
   <TR><TD>Last Name: <TD><INPUT name="lname" type="text">
   <TR><TD>First Name: <TD><INPUT name="fname" type="text">
   <TR><TD>Company: <TD><INPUT name="company" type="text">
   </TABLE>
   <P>
   <INPUT type="submit">
   </FORM>
   </BODY>
Just a simple HTML form. I use a to keep the proper alignment of the data entry fields and labels (if you are still using Web Explorer, upgrade to 1.1f or above so you get Table support.) See the action attribute of the <FORM> tag. That's a URL like the one we saw above, and I use the POST method because I do not like the "dirty" URL the browser shows when we use the method GET, but w3-msql can handle GET as easily as POST. Now here's the page called by our form:
   <HTML>
   <!-- Exemplo do w3-msql -->
   </HEAD>
   <TITLE>Address Book Search Results</TITLE>
   </HEAD>
   <BODY>
   <H1>Address Book Search Results<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);
           }
 
   >
 
   <P>
   Here are the results for the search on
   <UL>
       <LI>Last Name =
       <! echo ("'$lname'"); >
       <LI>First Name =
       <! echo ("'$fname'"); >
       <LI>Company =
       <! echo ("'$company'"); >
   </UL>
   <P>
   <TABLE border>
   <TR><TH>Last Name<TH>First Name<TH>Company<TH>e-mail
 
   <!
           $err = msqlQuery ($db, "select last_name, first_name, company, e_mail"+
                   "from addr_book" +
                   "where last_name like '%$lname%'" +
                   "and first_name like '%$fname%'" +
                   "and company like '%$company%'" +
                   "order by last_name, first_name");
           if ($err < 0) {
                   printf ("Error: $ERRMSG\n");
                   exit (1);
           }
           else {
                   $res = msqlStoreResult ();
                   $row = msqlFetchRow ($res);
                   if (#$row > 0) {
                           while (#$row > 0) {
                                   printf ("<TR><TD>%s<TD>%s<TD>%s<TD>%s\n",
                                           $row[0], $row[1], $row[2], $row[3]);
                                   $row = msqlFetchRow ($res);
                           }
                   }
                   else {
                           echo ("<TR><TD colspan=4>No matching entries found.\n");
                   }
                   msqlFreeResult ($res);
           }
   >
 
   </TABLE>
   </BODY>
   </HTML>

Any data passed to w3-msql through an HTML form can be accessed as a variable using a dollar sign in a w3-msql command, just like the $NUM_ROWS variable we already know. The name of the variable w3-msql gets is the same as specified on the NAME atribute of the <INPUT> tag. You could also use <TEXTAREA> and <SELECT> tags with the same results.

We used the LIKE predidate of the SQL SELECT command so the search does not need an exact match. Although this kind of query is very convenient to the user (a bunch of columns with the LIKE predicate), it can become very costly for the database to proccess it. Avoid this kind of query.

A real application would need more logic than we have. This kind of query can return very large result sets and lead to a very long download times for your internet users. Try to design queries with few columns in the WHERE clause and try to use operators =, < and >.

Getting Data From the Web

You can also use w3-msql to enter data into your databases. Here's an example of a form to insert new data into the addr_book table: (add_f.htm)

   <HTML>
   <HEAD>
   <TITLE>Add Address Entry</TITLE>
   </HEAD>
   <BODY>
   <H1>Add Address Entry<HR></H1>
   <P>
   <FORM action="http:/cgi-bin/w3-msql.exe/add_r2.htm" method="post">
   <TABLE>
   <TR><TD>Id:<TD><INPUT name="id">
   <TR><TD>Last Name:<TD><INPUT name="fname">
   <TD>First Name:<TD><INPUT name="lname">
   <TR><TD>Title:<TD><INPUT name="title">
   <TR><TD>Departament:<TD><INPUT name="dept">
   <TR><TD>Company:<TD><INPUT name="company">
   <TR><TD>Tel:<TD><INPUT name="tel">
   <TD>Fax:<TD><INPUT name="fax">
   <TR><TD>E-mail:<TD><INPUT name="e_mail">
   <TR><TD colspan=4 align="center">
   <BR><INPUT type="submit" value="Add Entry">
   </TABLE>
   </BODY>
   </HTML>

And here's the page that w3-msql inserts the entry into our database when proccessed:

   <HTML>
   <HEAD>
   <TITLE>Insert Results</TITLE>
   </HEAD>
   <BODY>
   <H1>Insert Results<HR></H1>
   <P>
 
   <!
           $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 == "") {
                   echo ("
                           You must supply an <B>id</B> for the entry.\n
                           <P>Press the BACK button of your browser to correct.\n
                   ");
                   exit (1);
           }
 
 [Note that the next line has been broken for EDM/2. Ed.]
           $err = msqlQuery ($db, "insert into addr_book" +
                   "values ($id, '$lname', '$fname', '$title', '$dept', '$company'," +
                           "'$tel', '$fax', '$e_mail')");
 
           if ($err < 0) {
 [Note that the next line has been broken for EDM/2. Ed.]
                   echo ("Could not insert into the address book:<BR>\n
                           $ERRMSG\n
                           <P>Press the BACK button of your browser if
                           you want to try again.\n
                   ");
                   exit (1);
           }
 
           msqlClose ($db);
   >
 
   <BIG>
   $lname, $fname
   </BIG>
   <BR>
   Successfully inserted into the address book.
   <P>Press the BACK button of your to insert another entry.
   </BODY>
   </HTML>

You can see that our simple example already has many elements of a full-blown application. We catch run-time errors from the database and show appropriate messages for the user. We check if the "id" field has a value, but we should also check if it is a numerical value.

We should also check if the data entered was meaningful. For example, it does not make sense to enter a record without first name, last name and company. It also does not makes sense to enter a record without telephone number, fax number or e-mail address. These checks are related to the functionality of the applications.

A real app should also have a slightly different implementation. The id field is just a key for the records, so we can link them to other records. Unless your data already has a unique identifier, you should have that kind of id field. In our example, why can we not have "Smith, John" from two different companies? it could be costly to link everything using this whole string, so we have a lighter id field. But our app does not check if the id field is unique.

The mSQL-2 database has the ability to mantain unique indexes for any field of a table. This can greatly reduce the time spent searching data and making joins, but can also be used to prevent duplicate values from being inserted.

So, lets create the index on id. Enter the following command on the mSQL monitor:

  mSQL > create index pk_add_book on addr_book (id) \g

This command will fail if you already have duplicate values on the id fields from different records. So let's start over and delete all records:

  mSQL > delete from addr_book \g

Now you can create our index. Try again inserting records using add_f.htm, but now you won't be able to insert duplicate id values.

mSQL Sequences

Well, now it has a more reliable design, but the user has to know which id values are already in use before he can insert a new entry. Of course we can mantain the last used value somewhere (maybe an mSQL table ?) and increment it each time we insert a new record.

That solution is not very elegant and can lead to concurrency problems (what happens if two users read the same last value?). The bigger databases have transaction and lock mechanisms to overcome these problems, but mSQL has none of them. The mSQL daemon serializes the SQL commands it executes, but this is not sufficient for our id-generation problems. The UPDATE command on mSQL does not allows expressions so you would have to SELECT the value, increment it, and then UPDATE it. During the time someone takes to increment and UPDATE the value, another user could SELECT it.

Be careful as you can find many other situations where this kind of "race condition" can happens, and depending on the applications this could be reason to upgrade from mSQL to a more powerful database server.

But for unique key generation, mSQL has a very nice solution. You just create a SEQUENCE on the table, and everytime you use the _seq system variable, you get a value incremented in an atomic operation. So, lets create our sequence:

  mSQL > create sequence on addr_book \g

Now experiment repeating the following command a few times:

  mSQL > select _seq from addr_book \g

Let's change our add entry form and scripts to implement improved data validation and to make use of sequences. Here's the data entry form:

   <HTML>
   <HEAD>
   <TITLE>Add Address Entry</TITLE>
   </HEAD>
   <BODY>
   <H1>Add Address Entry<HR></H1>
   <P>
   <FORM action="http:/cgi-bin/w3-msql.exe/add_r2b.htm" method="post">
   <TABLE>
   <TR><TD>Last Name:<TD><INPUT name="fname">
   <TD>First Name:<TD><INPUT name="lname">
   <TR><TD>Title:<TD><INPUT name="title">
   <TR><TD>Departament:<TD><INPUT name="dept">
   <TR><TD>Company:<TD><INPUT name="company">
   <TR><TD>Tel:<TD><INPUT name="tel">
   <TD>Fax:<TD><INPUT name="fax">
   <TR><TD>E-mail:<TD><INPUT name="e_mail">
   <TR><TD colspan=4 align="center">
   <BR><INPUT type="submit" value="Add Entry">
   </TABLE>
   </BODY>
   </HTML>

And here's the HTML script for W3-mSQL:

   <HTML>
   <HEAD>
   <TITLE>Insert Results</TITLE>
   </HEAD>
   <BODY>
   <H1>Insert Results<HR></H1>
   <P>
 
   <!-- connect to mSQL -->
 
   <!
           $db = msqlConnect ("localhost");
           if ($db < 0) {
                   echo ("Error: $ERRMSG\n");
                   exit (1);
           }
           if (msqlSelectDB ($db, "pim") < 0) {
                   echo ("Error: $ERRMSG\n");
                   exit (1);
           }
   >
 
   <!-- validate inputed data -->
 
   <!
           if ($lname == "" && $fname == "" && $company == "") {
                   echo ("
                           You must enter at least one of<BR>\n
                           <B>First Name</B>, <B>Last Name</B> or
                           <B>Company</B> name<BR>\n
                           for an address book entry.\n
                           <P>Press the BACK button of your browser to correct.\n
                   ");
                   exit (1);
           }
 
           if ($tel == "" && $fax == "" && $e_mail == "") {
                   echo ("
                           You must enter at least one of<BR>\n
                           <B>Tel</B> number, <B>Fax</B> number or
                           <B>e-mail</B> address<BR>\n
                           for an address book entry.\n
                           <P>Press the BACK button of your browser to correct.\n
                   ");
                   exit (1);
           }
   >
 
   <!-- generate new key and insert new record -->
 
   <!
           msqlQuery ($db, "select _seq from addr_book");
           $res = msqlStoreResult ();
           $row = msqlFetchRow ($res);
           $id = $row[0];
           msqlFreeResult ($res);
 
 [Note that the next line has been broken for EDM/2. Ed.]
           $err = msqlQuery ($db, "insert into addr_book" +
                   "values ($id, '$lname', '$fname', '$title', '$dept'," +
                           "'$company', '$tel', '$fax', '$e_mail')");
 
           if ($err < 0) {
 [Note that the next line has been broken for EDM/2. Ed.]
                   echo ("Could not insert into the address book:<BR>\n
                           $ERRMSG\n
                           <P>Press the BACK button of your browser if
                           you want to try again.\n
                   ");
                   exit (1);
           }
 
           msqlClose ($db);
   >
 
   <BIG>
   $lname, $fname from $company
   </BIG>
   <BR>
   Successfully inserted into the address book.
   <P>Press the BACK button of your to insert another entry.
   </BODY>
   </HTML>

HTML Links and W3-mSQL

Just to give you a better felling of what can be done with mSQL and its web gateway, I'll present an example of a set of linked pages that allows viewing the address book by company or by name, and to click on any name listed to see its details.

Here's the first page, save it as "menu.htm":

 
   <HTML>
   <HEADER>
   <TITLE>Address Book Main Menu</TITLE>
   </HEADER>
   </HTML>
   <BODY>
   <H1>Address Book Main Menu<HR></H1>
   <P>
   Choose on the following links to browse the address book or
   use the search form to look for a particular name or company.
   <UL>
     <LI><A href="/cgi-bin/w3-msql.exe/view_name.htm">Browse by <B>Name</B></A>
     <LI><A href="/cgi-bin/w3-msql.exe/view_comp.htm">Browse by <B>Company</B></A>
   </UL>
   <P>
   <HR>
   <P>
   Type the desired name, or part of, on the following fields:
   <BR>(If you leave a field blank, that stands for "any")
   <P>
   <FORM action="/cgi-bin/w3-msql.exe/search_name.htm" method="get">
   <TABLE>
   <TR><TD>Last Name: <TD><INPUT name="lname" type="text">
   <TR><TD>First Name: <TD><INPUT name="fname" type="text">
   <TR><TD>Company: <TD><INPUT name="company" type="text">
   </TABLE>
   <P>
   <INPUT type="submit">
   </FORM>
   </BODY>

You see, that's just our previous search form with two links added. Now let's see the search results page (search_name.htm)

 
   <HTML>
   <!-- Exemplo do w3-msql -->
   </HEAD>
   <TITLE>Address Book Search Results</TITLE>
   </HEAD>
   <BODY>
   <H1>Address Book Search Results<HR></H1>
   <P>
   <small> |
   <A href="/menu.htm">Main Menu/Search</A> |
   <A href="/cgi-bin/w3-msql.exe/view_name.htm">Browse by Name</A> |
   <A href="/cgi-bin/w3-msql.exe/view_company.htm">Browse by Company</A> |
   </small>
 
   <!
           $db = msqlConnect ("localhost");
           if ($db < 0) {
                   echo ("Error: $ERRMSG\n");
                   exit (1);
           }
           if (msqlSelectDB ($db, "pim") < 0) {
                   echo ("Error: $ERRMSG\n");
                   exit (1);
           }
   >
 
   <P>
   Here are the results for the search on
   <UL>
       <LI>Last Name =
       <! echo ("'$lname'"); >
       <LI>First Name =
       <! echo ("'$fname'"); >
       <LI>Company =
       <! echo ("'$company'"); >
   </UL>
   <P>
   <TABLE border>
   <TR><TH>Name<TH>Company<TH>e-mail
 
   <!
           $err = msqlQuery ($db, "select id, last_name, first_name, company, e_mail" +
                   "from addr_book" +
                   "where last_name like '%$lname%'" +
                   "and first_name like '%$fname%'" +
                   "and company like '%$company%'" +
                   "order by last_name, first_name");
           if ($err < 0) {
                   printf ("Error: $ERRMSG\n");
                   exit (1);
           }
           else {
                   $res = msqlStoreResult ();
                   $row = msqlFetchRow ($res);
                   if (#$row > 0) {
 [Note that the next line has been broken for EDM/2. Ed.]
                           while (#$row > 0) {
                                   printf ("<TR><TD><A
                                   href=\"/cgi-bin/w3-msql.exe/name_details.htm?
                                   id=%s\">%s, %s</A>
                                   <TD>%s<TD>%s\n",
                                   $row[0], $row[1], $row[2], $row[3], $row[4]);
                                   $row = msqlFetchRow ($res);
                           }
                   }
                   else {
 [Note that the next line has been broken for EDM/2. Ed.]
                           echo ("<TR><TD colspan=3>No matching
                                 entries found.\n");
                   }
                   msqlFreeResult ($res);
           }
   >
 
   </TABLE>
 
   <P>
   <HR>
   <small> |
   <A href="/menu.htm">Main Menu/Search</A> |
   <A href="/cgi-bin/w3-msql.exe/view_name.htm">Browse by Name</A> |
   <A href="/cgi-bin/w3-msql.exe/view_company.htm">Browse by Company</A> |
   </small>
 
   </BODY>
   </HTML>

Different from the previous examples is that this time we provide links between the pages of our address book web application. It's common practice in web applications to present the navigational links both at the top and at the bottom of the page so the user does not need to scroll when he wants to go to another page.

You can see that the names of each matching entry is highlighted, and they are links to the page "name_details.htm", which is also proccessed by W3-mSQL. For each entry, we construct a link that passes the id of the entry as if it was invoked by an HTML form using the GET method. So, let's see the name_details.htm page:

 
   <HTML>
   <!-- Exemplo do w3-msql -->
   </HEAD>
   <TITLE>Address Book Details</TITLE>
   </HEAD>
   <BODY>
   <H1>Address Book Details<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);
           }
   >
 
   <P>
   <small> |
   <A href="/menu.htm">Main Menu/Search</A> |
   <A href="/cgi-bin/w3-msql.exe/view_name.htm">Browse by Name</A> |
   <A href="/cgi-bin/w3-msql.exe/view_company.htm">Browse by Company</A> |
   </small>
 
   <P>
   <TABLE border>
 
   <!
           $err = msqlQuery ($db, "select last_name, first_name, title, department, company," +
                             "tel, fax, e_mail" +
                             "from addr_book" +
                             "where id = $id");
           if ($err < 0) {
                   printf ("Error: $ERRMSG\n");
                   exit (1);
           }
           else {
                   $res = msqlStoreResult ();
                   $row = msqlFetchRow ($res);
                   if (#$row > 0) {
                           printf ("<TR><TD>Last Name:<TD><B>%s</B>\n", $row[0]);
                           printf ("<TR><TD>First Name:<TD><B>%s</B>\n", $row[1]);
                           printf ("<TR><TD>Title:<TD><B>%s</B>\n", $row[2]);
                           printf ("<TR><TD>Department:<TD><B>%s</B>\n", $row[3]);
                           printf ("<TR><TD>Company:<TD><B>%s</B>\n", $row[4]);
                           printf ("<TR><TD>Tel:<TD><B>%s</B>\n", $row[5]);
                           printf ("<TR><TD>Fax:<TD><B>%s</B>\n", $row[6]);
                           printf ("<TR><TD>e-mail:<TD><B>%s</B>\n", $row[7]);
                   }
                   else {
                           echo ("<TR><TD colspan=2>No matching entries found.\n");
                   }
                   msqlFreeResult ($res);
           }
   >
 
   </TABLE>
 
   <P>
   <HR>
   <small> |
   <A href="/menu.htm">Main Menu/Search</A> |
   <A href="/cgi-bin/w3-msql.exe/view_name.htm">Browse by Name</A> |
   <A href="/cgi-bin/w3-msql.exe/view_company.htm">Browse by Company</A> |
   </small>
 
   </BODY>
   </HTML>

There's nothing really special about this page. Just SELECT the record which matches the supplied id, and display its fields. We use an HTML table so the fields are properly aligned. Again we put navigational links at the top and bottom of the page. It's very important that you use the same links in the same place on every page of your web application. This consistency will make the app easier to use.

Now, here are the pages that allows you to browse the address book by name and by company. First, by Name:

 
   <HTML>
   <!-- Exemplo do w3-msql -->
   </HEAD>
   <TITLE>Address Book by Name</TITLE>
   </HEAD>
   <BODY>
   <H1>Address Book by Name<HR></H1>
 
   <P>
   <small> |
   <A href="/menu.htm">Main Menu/Search</A> |
   <A href="/cgi-bin/w3-msql.exe/view_name.htm">Browse by Name</A> |
   <A href="/cgi-bin/w3-msql.exe/view_company.htm">Browse by Company</A> |
   </small>
 
   <!
           $db = msqlConnect ("localhost");
           if ($db < 0) {
                   echo ("Error: $ERRMSG\n");
                   exit (1);
           }
           if (msqlSelectDB ($db, "pim") < 0) {
                   echo ("Error: $ERRMSG\n");
                   exit (1);
           }
 
   >
 
   <P>
   Here are the entries of our address book:
   <P>
   <TABLE border>
   <TR><TH>Name<TH>Company<TH>e-mail
 
   <!
           $err = msqlQuery ($db, "
                   select id, last_name, first_name, company, e_mail
                   from addr_book
                   order by last_name, first_name");
           if ($err < 0) {
                   printf ("Erro! $ERRMSG\n");
                   exit (1);
           }
           else {
                   $res = msqlStoreResult ();
                   $row = msqlFetchRow ($res);
                   if (#$row > 0) {
                           while (#$row > 0) {
 [Note that the next line has been broken for EDM/2. Ed.]
                                   printf ("<TR><TD><A
                                   href=\"/cgi-bin/w3-msql.exe/name_details.htm?
                                   id=%s\">%s, %s</A>
                                   <TD>%s<TD>%s\n",
                                   $row[0], $row[1], $row[2],  $row[3], $row[4]);
                                   $row = msqlFetchRow ($res);
                           }
                   }
                   else {
                           echo ("<TR><TD colspan=3>No entries found.\n");
                   } msqlFreeResult ($res);
           }
   >
 
   </TABLE>
 
   <P>
   <HR>
   <small> |
   <A href="/menu.htm">Main Menu/Search</A> |
   <A href="/cgi-bin/w3-msql.exe/view_name.htm">Browse by Name</A> |
   <A href="/cgi-bin/w3-msql.exe/view_company.htm">Browse by Company</A> |
   </small>
 
   </BODY>
   </HTML>

And now by company:

 
   <HTML>
   <!-- Exemplo do w3-msql -->
   </HEAD>
   <TITLE>Address Book by Company</TITLE>
   </HEAD>
   <BODY>
   <H1>Address Book by Company<HR></H1>
 
   <P>
   <small> |
   <A href="/menu.htm">Main Menu/Search</A> |
   <A href="/cgi-bin/w3-msql.exe/view_name.htm">Browse by Name</A> |
   <A href="/cgi-bin/w3-msql.exe/view_company.htm">Browse by Company</A> |
   </small>
 
   <!
           $db = msqlConnect ("localhost");
           if ($db < 0) {
                   echo ("Error: $ERRMSG\n");
                   exit (1);
           }
           if (msqlSelectDB ($db, "pim") < 0) {
                   echo ("Error: $ERRMSG\n");
                   exit (1);
           }
 
   >
 
   <P>
   Here are the entries of our address book:
   <P>
   <TABLE border>
   <TR><TH>Company<TH>Name<TH>e-mail
 
   <!
           $err = msqlQuery ($db, "select id, company, last_name, first_name, e_mail" +
                                  "from addr_book" +
                                  "order by company, last_name, first_name");
           if ($err < 0) {
                   printf ("Erro! $ERRMSG\n");
                   exit (1);
           }
           else {
                   $res = msqlStoreResult ();
                   $row = msqlFetchRow ($res);
                   $company = "";
                   if (#$row > 0) {
                           while (#$row > 0) {
                                   if ($row[1] != $company) {
                                           printf ("<TR><TD>%s<TD><TD>\n",
                                                   $row[1]);
                                           $company = $row[1];
                                   }
 [Note that the next line has been broken for EDM/2. Ed.]
                                   printf ("<TR><TD><TD>
                                   <A href=\"/cgi-bin/w3-msql.exe/name_details.htm?
                                   id=%s\">%s, %s</A><TD>%s\n",
                                           $row[0], $row[2],  $row[3], $row[4]);
                                   $row = msqlFetchRow ($res);
                           }
                   }
                   else {
                           echo ("<TR><TD colspan=3>No entries found.\n");
                   } msqlFreeResult ($res);
           }
   >
 
   </TABLE>
 
   <P>
   <HR>
   <small> |
   <A href="/menu.htm">Main Menu/Search</A> |
   <A href="/cgi-bin/w3-msql.exe/view_name.htm">Browse by Name</A> |
   <A href="/cgi-bin/w3-msql.exe/view_company.htm">Browse by Company</A> |
   </small>
 
   </BODY>
   </HTML>

Note that we do not repeat the company name for every record. We print the company name only when it changes. That way the user sees an outline with the company names as the first level and the personal names as the second level. That's just a demo on how a bit of simple W3-mSQL programming can generate sophisticated HTML layouts.

You should have no problems expanding the address book sample to include the ability to insert new records, update existing entries or deleting them. This is left as an exercise for the reader.

Your web address book design still has some flaws, though. The number of records can grow very large, and the resulting pages (browse by name or by company) can take a long time to download. A real web app should have a means of limiting the size of the output and present it page by page, just like the Alta Vista search engine.

You can work around that problem by never listing the whole table. You could present first a list of companies and then list the persons from that company. Or you could list only people with names starting with the letter 'e'.

These are generic web app design problems. They are not specific to mSQL or W3-mSQL.

Index Corruption

The 2.0.1 release of mSQL (the latest we currently have for OS/2) can corrupt its indexes with ease. When an index becomes corrupt, you can get no matches for a record that you know is in some table. The problem is that the index does not show the record, and mSQL will try to use the index any time it thinks it will be faster (like the search for id values we use in the name_details.htm page).

I found no way of recovering a corrupt index except by dropping the table and recreating it. To ease the process, I recomend you to have a text file with the commands used to create the table and its indexes (so you can pipe the commands to the mSQL monitor) and also recommend you always keep an up-to-date backup of the table data generated by the msqlexport utility.

The latest Unix release, 2.0.3, has many bug fixes for these index problems. I hope we soon will have that version available for OS/2 (if you want to help, please contact me as lozano@blnet.com).

What's Next?

We still have too much to cover about relational database design. I'll write more articles giving practical advice on how to design and implement applications using mSQL and its Web Gateway, but I strongly recommend you read a good book on the SQL query language and relational databases. The "Design and Implementation of Relational Database Systems" by Carl Date is a book every programmer should have on his bookshelf.

Future articles will also teach how to use the third-party JDBC driver for mSQL, and by extension how to use any relational database from a Java application or applet. Stay connected!