A mSQL and perl Web Server HOWTO

4. Running an Example Database

After completing the system installation we can now finally run a model application. Depending on the version of msql installed and the perl database interface used, we have to modify the sample programs in a few points.

First however, the file index.html residing in /home/httpd/html/ must be modified to allow calling a sample database application. We can place our database (which we call database.cgi or inventur.cgi here despite its archive name perl.lst.ck) in /home/httpd/html/test/.

We add one line (of course, depending on your installation choices) similar to the following to index.html:

<LI>Test the <A HREF="test/database.cgi">Database, DBI:DBD-mSQL style!</A>
<LI>Test the <A HREF="test/inventur.cgi">Database, MsqlPerl style!</A>

4.1 Adapting the sample script for MsqlPerl

Our sample script has to be told to use the MsqlPerl interface. The modification takes place in several locations. First, near the beginning of the file, we change the use clause:

#
# use DBI;            # Generisches Datenbank-Interface
use Msql;

Then, near line 27, the MsqlPerl syntax does not require the mentioning of a specific driver:

# $dbh = DBI->connect($host, $database, '', $driver) ||
$dbh = Msql->connect($host, $database) ||

Then, from line 33 onward throughout the whole script, we have to change all instances of do against query:

# $dbh->do("SELECT * FROM hw") || db_init($dbh);
$dbh->query("SELECT * FROM hw") || db_init($dbh);

Finally, in MsqlPerl speak, line 207 can be commented out:

# $sth->execute || msg("SQL Error:", $sth->errstr);

In addition, it may become necessary to swap all errstr calls like the one in the preceding code fragment against errmsg. This is also version dependent.

After these modifications, the script should run smoothly.

4.2 Adapting the sample script for msql-2

The SQL syntax was redefined during the development of mslq-2. The original script will fail to execute the table initialization statements in lines 45 -- 58. The primary key modifier is no longer supported by msql-2, and should simply be skipped:

    $dbh->do(<<EOT) || die $dbh->errstr; # Neue Personen-Tabelle
        create table person (
# We do not need the 'primary key' modifier anymore in msql-2!
#           pn        int primary key,   # Personalnummer
            pn        int,               # Personalnummer
            name      char(80),          # Nachname, Vorname
            raum      int                # Raumnummer
        )
EOT
    $dbh->do(<<EOT) || die $dbh->errstr; # Neue Hardware-Tabelle
        create table hw (
# We do not need the 'primary key' modifier anymore in msql-2!
#           asset int primary key,       # Inventurnummer
            asset int,                   # Inventurnummer
            name   char(80),             # Bezeichnung
            person int                   # Besitzer
        )
EOT

Unfortunately, this specific script will then accept new entries with identical personnel numbers; the msql-1 modifier primary key intends to prevent exactly this behaviour. The msql-2 documentation shows how to use the CREATE INDEX clause to create unique entries.