An Introduction to mSQL
Written by Fernando Lozano
Contents
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, 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 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:
<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 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:
<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 
