An introduction to mSQL

From EDM2
Jump to: navigation, search

Written by Fernando Lozano

The First Steps with mSQL

(Note: all html and sql files referred to in the article are available msql.zip here. Ed.)

Imagine you get a free relational database server. It is fast, implements a nice subset of ANSI-SQL and won't need that Pentium II 266MHz with 512Mb of RAM.

Better yet, as everybody's working on dynamic web pages today, our nice database server is very quick at estabilishing a connection. If you have already tried a CGI script connecting to Oracle, DB2 or some other powerhouse database you know how the connection time can slow down your web site.

As with any modern tool, you can write C programs to get your data from, but it's easier to write a REXX script or a Java applet. It even comes ready with an easy to use web gateway. Add to this wide multi-platform coverage and we have a perfect match to our desires.

On the downside, you won't get some nice features common in corporate workhorses like transaction commit and rollback, no locking (our database simply serializes all requests to avoid concurrency problems), no stored procedures, nested queries nor two-phase commit.

Even with that limitations, our database is being successfully used by many web sites and corporations to build a broad range of systems. It's a tried-and-trusted solutions with broad third-party support.

The mSQL Database Server

That nice databse is named mSQL, which stands for mini-SQL.

The mSQL database server was born at the Australian Bond University as part of Project Minerva, whose goal was to develop tools for network management. Current comercial and free databases (like Postgres) were too resource hungry, so researcher David Hughes (a.k.a. Bambi) designed and implemented his own database for the project.

mSQL was quickly adopted by many web developers and got strong support from third parties. Almost every Unix programming tool, eg. Perl, Python and Tcl, can get data from mSQL. The Apache Web Server, which despite Netscape and Microsoft efforts runs more than half of comercial web sites of the Internet, can use mSQL for storing login names and passwords. The popular Apache add-on for dynamic web pages, PHP, also has native support for mSQL.

Now mSQL is at its second major revision. Bambi left Bond University and started his owm company, Hughes Technologies, to develop and sell support for mSQL, but it's still free for non-comercial use. Commercial users pay a cheap license but almost every ISP has mSQL readily availabe for its customers. mSQL 2.0 has ports for Win32 and OS/2 (of course, if not, what this article would be doing here?) besides most Unix platforms.

In this article, we will follow the installation and configuration of mSQL step-by-step, then we will create a test table and build some programs to manipulate data on that table. A little understanding of HTML, CGI and web servers would be nice, as we'll build the examples using mSQL Web gateway.

mSQL and OS/2

mSQL use around the world and in this article will focuse on mSQL as a Web back-end database. But even if you have no interest in building Dynamic Web Pages, mSQL is worth trying, first of all, because it's cheap (it may be free to you) and small. But as a client/server database that understands a limited subset of ANSI SQL, you can use it to learn about relational databases or to build prototypes that can grow to use bigger databases with minimal modification.

Although most major database makers have OS/2 versions, they are not cheap and can be really difficult to get. But to use mSQL you will just do a small download (mSQL 1.0.16 is about 870Kb, complete with docs).

OS/2 users can choose between two ports of mSQL. The first is mantained by Dirk Ohme and uses Visual Age C++ for both OS/2 and Win32 versions. The second is mantained by Garey Smiley, who is also the mantainer of the Apache Web Server for OS/2. Dirk has ported both mSQL 1.x and 2.x. Garey has ported mSQl 1.x and is working on 2.x. More info on these ports, related programming tools and news can be found at The mSQL PC Home Page (http://www.blnet.com/msqlpc).

This article was originally written with mSQL 2.0.x in mind, but a problem with the Web gateway forced the move back to mSQL 1.0.x. When I get that problems solved, I'll release a 2.x version of this article, and maybe an NT or Linux version for users with troubles installing mSQL.

A Quick Start

Let's use Dirk port, as it comes with DLLs compatibles with most OS/2 C/C++ compilers. You can download release 1.0.16 patchlevel E from ftp://ftp.nerosworld.com/pub/msql/contrib/mSQLOS2/msql116e.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 SCRIPTS\INSTALL.CMD passing as an argument the directory where you wish to install the package. I used:

E:\temp\msql-116\scripts] install e:\soft\msql1

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 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 at 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.

It is also possible to get the loopback interface enabled if you have only Warp 3 with IAK; you can get instructions with most OS/2 web servers, like Apache or Ximami.

Let's check if our TCP/IP configurarion 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 wrong 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 also have 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 the LIBPATH (I added the directory E:\SOFT\MSQL1\BIN). You can also add the bin directory to your path so you have easy access to any msql comand-line utility.

Before we start the server, there's still one last configuration item we have to do. When the mSQL Server starts, it uses the contents of the USER environment variable to tell which user can do administrative operations on the server, like creating databases or stopping the server. Use 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, use the TCP/IP settings notebook to define the USER name, or put the following line in your CONFIG.SYS:

set USER=msqldba

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

To start the server, either double click "mSQL Server" or run msqld.exe from any OS/2 command prompt. You should get a message like this:

mSQL Server 1.0.16 for OS/2 starting ...

server located in 'E:\SOFT\MSQL1\BIN'
Warning : Couldn't open ACL file: The file cannot be found.
Without an ACL file global access is Read/Write

Do not worry about that warning, we will be explaning how to remove it later.

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\msql1\bin] msqladm version

 msql admin version      1.0.16 for OS/2
 mSQL connection         localhost via TCP/IP
 mSQL server version     1.0.16 for OS/2
 mSQL protocol version   6
 mSQL TCP socket         4333 (default)
 mSQL root user          msqldba
 Host Architecture       OS/2 32Bit

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

Working with mSQL

Now the first thing to do is 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 comercial 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 creating the file msql.acl on the same directory where it is the daemon (msqld.exe).

Remember that security in mSQL is not strong. It uses just a user name, that the client send as clear text when connecting, and does not ask for any password. If you are afraid that someone might scramble your tables, allow only local access to the server.

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

Now everyone can access the created database, from anywhere. So let's set its permissions. Open (create) the file msql.acl with any text editor. You should add the following lines:

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

That way everybody can have read and write access, but only from the local host. If you want only the msqldba user to have write access, change the following line:

write=msqldba

Multiple user names are separated by colons.

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

E:\EDM2]msqladm reload

Or you can double-click the icon "Server Reload".

Now we can finally create some tables in our databse to put some data into it. mSQL comes with a SQL Monitor so you can submit SQL commands interactively. It can be started 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 (
            ->        nickname        char(10) not null primary key,
            ->        last_name       char(30),
            ->        first_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 Dave's book?).

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

Query Ok. 1 rows modified or retrieved.

The only constraint the mSQL-1 daemon can enforce is the primary key constraint. Referencial integrity and domain constraints have to be enforced by our applications. The mSQL-2 daemon has some more resources, like the ability to define how many indexes you want and creating sequences.

Now we can enter some data to our table (I won't show the msql.exe prompts this time):

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

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

        insert into addr_book values (
                'dohme',
                'Ohme',
                'Dirk',
                '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 = 'BL Informatica'
            ->\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 table above saved in the file create_addr_book.sql, but 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. But 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 good tools on the Internet. It's just a back-end server and the bare minimum support tools. But there is something more interesting included in the basic mSQL archive.

The Web Gateway

As I 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 don'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 open input file

Error at line 1

If you get any other result, either your web server does not support the Common Gateway Interface (CGI) or it is not configured to run CGI scripts from the directory in which you put w3-msql.exe (maybe the virtual directory /cgi-bin is mapped to a diferent file-system 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>
 
   <! msql connect >
   <! msql database pim >
 
   <P>
   Here are the entries of our address book:
   <P>
     <TABLE border>
       <TR><TH>Last Name<TH>First Name<TH>e-mail
 
       <!-- this command should be on just one line but was
            formatted for 2 for EDM/2 -->
       <! msql query "select last_name, first_name, e_mail from addr_book
            order by last_name, first_name" q1 >
 
       <! msql if ($NUM_ROWS == "0") >
                 <TR><TD colspan=3>No entries found.
       <! msql else >
                 <!-- this command should be on just one line -->
                  <! msql print_rows q1 "<TR><TD>@q1.0<TD>@q1.1<TD>@q1.2" >
       <! msql fi >
 
       <! msql free q1 >
 
     </TABLE>
 </BODY>
 </HTML>

And load it with the following URL:

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

The result should be a page similar to the following:

 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 we 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) lets you associate a file extension to a CGI program so the file gets automatically proccessed by w3-msql, just like files with extension .shtml are proccessed by the SSI filter.

A number of commands, like "msql connect" or "msql query", may be contained inside the <! ... > delimiters. W3-msql process these commands and send the web browser just the results.

The msql commands should fit entirely on one line. The w3-msql parser can't handle multiline commands. [Note that for formatting purposes, these long commands have been split onto multiple lines only in this article. The files in the zip archive are fine. Ed.]

The command "msql query" sends the SQL statement passed as an argument to the mSQL Server and assigns it to the supplied handle (in this case "q1") so you can refer to its result set later. The command "msql print_rows" loops through ALL the rows from the given result set applying to each one the given format string.

Inside the format string, the notation @handle.pos represents a column (or field) from the returned rows (or records). Then "@q1.0" is the first column (last_name) and so on.

We use the varable $NUM_ROWS to test if there was any returned data, and the command "msql free" frees the memory allocated to the result set.

Hey, you would never have thought it was so easy to put a database on the web, would you?

A Search Engine

To publish database data listing 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 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>
    <HEAD>
       <TITLE>Address Book Search Form</TITLE>
    </HEAD>
 
    <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_r.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">
         <TR><TD colspan=2 align="center"><INPUT type="submit">
       </FORM>
    </BODY>
 </HTML>
Just a simple HTML form. I use a to keep the proper alignment of the data entry fields (if you are still using Web Explorer, upgrade to 1.1f or above so you get Table support). See the action atribute 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</H1>
         <HR>
 
         <! msql connect >
         <! msql database pim >
 
         <P>
         Here are the results for the search on
         <UL>
             <LI>Last Name =
             <! msql print "'$lname'" >
             <LI>First Name =
             <! msql print "'$fname'" >
             <LI>Company =
             <! msql print "'$company'" >
         </UL>
         <P>
         <TABLE border>
         <TR><TH>Last Name<TH>First Name<TH>Company<TH>e-mail
 
         <!-- this command should be on just one line but was
            formatted for more for EDM/2 -->
         <! msql query "select last_name, first_name, company, e_mail
            from addr_book where first_name like '%$fname%' and last_name
            like '%$lname%' and company like '%$company%' order by
            last_name, first_name" q1 >
 
         <! msql if ($NUM_ROWS == "0") >
                 <TR><TD colspan=4>No match.
         <! msql else >
         <!-- this command should be on just one line but was
            formatted for more for EDM/2 -->
                 <! msql print_rows q1
                     "<TR><TD>@q1.0<TD>@q1.1<TD>@q1.2" >
         <! msql fi >
 
         <! msql free q1 >
 
         </TABLE>
         </BODY>
         </HTML>

Any data passed to w3-msql through a HTML form can be accessed as a variable using a dollar sign inside 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 for the user (a bunch of colunms with the LIKE predicate), it can become very costly for the database to process it.

A real aplication would need more logic than we have. This kind of query can return result sets very large and lead to a very long download time for your internet users. Try to design queries with few columns in the WHERE clause and try to use operators like =, < 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 on the addr_book table: (Save it as "add_s.htm.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_r.htm" method = "post">
    <TABLE>
      <TR><TD>Nick Name:<TD><INPUT name="nick">
      <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 when proccessed by w3-msql inserts the entry into our database:

 <HTML>
 <HEAD>
   <TITLE>Insert Results</TITLE>
 </HEAD>
 <BODY>
   <H1>Insert Results<HR></H1>
   <P>
 
   <! msql connect localhost >
   <! msql database pim >
 
   <! msql if ($nick == "") >
     You must enter a nick name.
     <P>Press the BACK button of your browser to correct.
   <! msql else >
 
   <!-- this command should be on just one line but was
        formatted for more for EDM/2 -->
   <! msql query "insert into addr_book values
        ('$nick', '$lname', '$fname', '$title', '$dept',
         '$company', '$tel', '$fax', '$e_mail')" q1 >
 
   <! msql print "$nick" >
   successfully inserted into the address book.
   <P>Press the BACK button of your to insert another entry.
 
   <! msql fi >
   <! msql free q1 >
 
 </BODY>
 </HTML>

From now on you will encounter some limits of w3-msql. Entering data will normally require a lot of validation code, but w3-msql has a very simple and fragile parser. Its command set does not includes any loop construct, you cannot intercept run-time errors and it has few built-in functions. The w3-msql included with mSQl 1.0.16 is definitely is not adequate for much except simple queries. As the creator David Hughes said, w3-msql was just a "proof of concept".

mSQL 2.0.x includes a much more powerfull w3-msql, that has a full-featured programming language, with multi-line statements, loop constructs, subroutines and libraries of functions. But you can stick with mSQL 1.0.16 and use C, REXX or Java to build the data entry applications. A future article will have more info about these alternatives.

I hope you have enjoyed this article and this short introduction to web database development. Remember to visit The mSQL PC Home Page at http://www.blnet.com/msqlpc for more info about mSQL and let's hope the toolset for OS/2 expands quickly. I think OS/2 lacks varied database support, and even if mSQL is not a solution for everone's needs, it surely is nice and the OS/2 community will benefit from it.