An Introduction to mSQL

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 establishing 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 database 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 commercial 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, e.g. Perl, Python and Tcl, can get data from mSQL. The Apache Web Server, which despite Netscape and Microsoft efforts runs more than half of commercial 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 own company, Hughes Technologies, to develop and sell support for mSQL, but it's still free for non-commercial use. Commercial users pay a cheap license but almost every ISP has mSQL readily available 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 focus 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 maintained by Dirk Ohme and uses VisualAge C++ for both OS/2 and Win32 versions. The second is maintained by Garey Smiley, who is also the maintainer 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 Xitami.

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 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 command-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 explaining 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 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 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 effective, 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 database 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. Referential 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 beginning 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 different file-system directory).

Now save the following file as addr_book.htm in the HTML directory of your web server:   Address Book   mSQL Web Address Book  <! msql connect > <! msql database pim >  Here are the entries of our address book:    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 processed by w3-msql, just like files with extension .shtml are processed 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 variable $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:   Address Book Search Form</TITLE> </HEAD>  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"> </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 attribute of the <INPUT> tag. You could also use <TEXTAREA> and <SELECT> tags with the same results.

We used the LIKE predicate 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 columns with the LIKE predicate), it can become very costly for the database to process it.

A real application 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 =,.

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")   Add Address Entry</TITLE> </HEAD>  Add Address Entry<HR></H1> <P> <FORM action="http:/cgi-bin/w3-msql.exe/add_r.htm" method = "post"> </BODY> </HTML>

And here's the page that when processed by w3-msql inserts the entry into our database:   Insert Results</TITLE> </HEAD>  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 > <! 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 powerful 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 everyone's needs, it surely is nice and the OS/2 community will benefit from it.