Perl (MySQL & mSQL)

10.1. DBI

As with all Perl modules, you must use DBI to get access:

#!/usr/bin/perl -w

use strict;
use CGI qw(:standard);
use DBI;

NOTE

When running any MySQL/mSQL Perl programs, you should always include the -w command line argument. With this present, DBI will redirect all MySQL and mSQL specific error messages to STDERR so that you can see any database errors without checking for them explicitly in your program.

All interactions between Perl and MySQL and mSQL are conducted through what is known as a database handle. The database handle is an object -- represented as a scalar reference in Perl -- that implements all of the methods used to communicate with the database. You may have as many database handles open at once as you wish. You are limited only by your system resources. The connect() method uses a connection format of DBI:servertype:database:hostname:port (hostname and port and optional), with additional arguments of username and password to create a handle:

my $dbh = DBI->connect('DBI:mysql:mydata', undef, undef);
my $dbh = DBI->connect('DBI:mSQL:mydata:myserver', undef, undef);
my $dbh = DBI->connect('DBI:mysql:mydata','me','mypass');

The servertype attribute is the name of the DBD database-specific module, which in our case will be either "mysql" or "mSQL" (note capitalization). The first version creates a connection to the MySQL server on the local machine via a Unix-style socket. This is the most efficient way to communicate with the database and should be used if you are connecting to a local server. If the hostname is supplied it will connect to the server on that host using the standard port unless the port is supplied as well. If you do not provide a username and password when connecting to a MySQL server, the user executing the program must have sufficient privileges within the MySQL database. The username and password should always be left undefined for mSQL databases.

NOTE

Perl 5 has two different calling conventions for modules. With the object-oriented syntax, the arrow symbol "->" is used to reference a method in a particular class (as in DBI->connect). Another method is the indirect syntax, in which the method name is followed by the class name, then the arguments. The last connect method above would be written as connect DBI'DBI:mysql:mydata','me','mypass'. Early versions of the Msql.pm used the indirect syntax exclusively and also enforced a specific method of capitalization inspired by the mSQL C API. Therefore, a lot of older MsqlPerl code will have lines in it like SelectDB $dbh'test' where a simple $dbh->selectdb('test') would do. If you haven't guessed, we are partial to the object-oriented syntax, if only because the arrow makes the relationship between class and method clear.

Once you have connected to the MySQL or mSQL server, the database handle -- $dbh in all of the examples in this section -- is the gateway to the database server. For instance, to prepare an SQL query:

$dbh->prepare($query);

WARNING

When using mSQL you may select only one database at a time for a particular database handle. The mSQL server imposes this limit. However, you may change the current database at any time by calling connect again. With MySQL, you may include other databases in your query by explicitly naming them. In addition, with both MySQL and mSQL, if you need to access more than one database concurrently, you can create multiple database handles and use them side by side.

Chapter 21, "Perl Reference", describes the full range of methods and variables supplied by DBI as well as Mysql.pm and Msql.pm.

As an example of the use of DBI consider the following simple programs. In Example 10-1, datashow.cgi accepts a hostname as a parameter -- "localhost" is assumed if no parameter is present. The program then displays all of the databases available on that host.

Example 10-1. The CGI datashow.cgi Shows All of the Databases on a MySQL or mSQL Server

#!/usr/bin/perl -w

use strict;
use CGI qw(:standard);
use CGI::Carp;
	# Use the DBI module
use DBI;
CGI::use_named_parameters(1);

my ($server, $sock, $host);

my $output = new CGI;
$server = param('server') or $server = '';

# Prepare the MySQL DBD driver
my $driver = DBI->install_driver('mysql');

	my @databases = $driver->func($server, '_ListDBs');

# If @databases is undefined we assume
# that means that the host does not have
# a running MySQL server. However, there could be other reasons
# for the failure. You can find a complete error message by
# checking $DBI::errmsg.
if (not @databases) {
        print header, start_html('title'=>"Information on $server",
        'BGCOLOR'=>'white');
        print <<END_OF_HTML;
<H1>$server</h1>
$server does not appear to have a running mSQL server.
</body></html>
END_OF_HTML
        exit(0);
}

                                'BGCOLOR'=>'white');
       print <<END_OF_HTML;
<H1>$host</h1>
<p>
$host\'s connection is on socket $sock.
<p>
Databases:<br>
<UL>
END_OF_HTML
foreach (@databases) {
        print "<LI>$_\n";
}
print <<END_OF_HTML;
</ul>
</body></html>
HTML
exit(0)

In Example 10-2, tableshow.cgi accepts the name of a database server (default is "localhost") and the name of a database on that server. The program then shows all of the available tables on that server.

Example 10-2. The CGI tableshow.cgi Shows All of the Tables Within a Database

#!/usr/bin/perl -w

use strict;
use CGI qw(:standard);
use CGI::Carp;
	# Use the Msql.pm module
use DBI;
CGI::use_named_parameters(1);

my ($db);
my $output = new CGI;
$db = param('db') or die("Database not supplied!");

# Connect to the requested server.
my $dbh = DBI->connect("DBI:mysql:$db:$server", undef, undef);

# If $dbh does not exist, the attempt to connect to the
# database server failed. The server may not be running,
# or the given database may not exist.
if (not $dbh) {
        print header, start_html('title'=>"Information on $host => $db",
        'BGCOLOR'=>'white');

        print <<END_OF_HTML;
<H1>$host</h1>
<H2>$db</h2>
The connection attempt failed for the following reason:<BR>
$DBI::errstr
</body></html>
END_OF_HTML
        exit(0);
} 

print header, start_html('title'=>"Information on $host => $db",
        'BGCOLOR'=>'white');
print <<END_OF_HTML;
<H1>$host</h1>
<H2>$db</h2>
<p>
Tables:<br>
<UL>
END_OF_HTML
# $dbh->listtable returns an array of the tables that are available
# in the current database.
my @tables = $dbh->func( '_ListTables' );
foreach (@tables) {
         print "<LI>$_\n";
}
print <<END_OF_HTML;
</ul>
</body></html>
END_OF_HTML
       exit(0);

And, finally, Example 10-3 shows all of the information about a specific table.

Example 10-3. The CGI tabledump.cgi Shows Information About a Specific Table

#!/usr/bin/perl -w

use strict;
use CGI qw(:standard);
use CGI::Carp;
	# Use the DBI module
use DBI;
CGI::use_named_parameters(1);

my ($db,$table);
my $output = new CGI;
$server = param('server') or $server = '';
$db = param('db') or die("Database not supplied!");

# Connect to the requested server.
my $dbh = DBI->connect("DBI:mysql:$db:$server", undef, undef);

# We now prepare a query for the server asking for all of the data in
# the table.
my $table_data = $dbh->prepare("select * from $table");
# Now send the query to the server.
$table_data->execute;

# If the return value is undefined, the table must not exist. (Or it could
# be empty; we don't check for that.)
if (not $table_data) {
        print header, start_html('title'=>
        "Information on $host => $db => $table", 'BGCOLOR'=>'white');

        print <<END_OF_HTML;
<H1>$host</h1>
<H2>$db</h2>
The table '$table' does not exist in $db on $host.
</body></html>
END_OF_HTML
        exit(0);
}

# At this point, we know we have data to display. First we show the
# layout of the table.
print header, start_html('title'=>"Information on $host => $db => $table",
        'BGCOLOR'=>'white');
print <<END_OF_HTML;
<H1>$host</h1>
<H2>$db</h2>
<H3>$table</h3>
<p>
<TABLE BORDER>
<CAPTION>Fields</caption>
<TR>
 <TH>Field<TH>Type<TH>Size<TH>NOT NULL
</tr>
<UL>
END_OF_HTML

# $table_data->name returns a referece to an array 
# of the fields of the database.
my @fields = @{$table_data->NAME};
# $table_data->type return an array reference of the types of fields.
# The types returned here are in SQL standard notation, not MySQL specific.
my @types = @{$table_data->TYPE};
# $table_data->is_not_null returns a Boolean array ref indicating which fields
# have the 'NOT NULL' flag.
my @not_null = @{$table_data->is_not_null};
# $table_data->length return an array ref of the lengths of the fields. This is
# fixed for INT and REAL types, but variable (defined when the table was
# created) for CHAR.
my @length = @{$table_data->length};

# All of the above arrays were returned in the same order, so that $fields[0],
# $types[0], $not_null[0] and $length[0] all refer to the same field.

foreach $field (0..$#fields) {
        print "<TR>\n";
print "<TD>$fields[$field]<TD>$types[$field]<TD>";
        print $length[$field] if $types[$field] eq 'SQL_CHAR';
        print "<TD>";
        print 'Y' if ($not_null[$field]);
        print "</tr>\n";
}

print <<END_OF_HTML;
</table>
<p>
<B>Data</b><br>
<OL>
END_OF_HTML

# Now we step through the data, row by row, using DBI::fetchrow_array().
# We save the data in an array that has the same order as the informational
# arrays (@fields, @types, etc.) we created earlier.
while(my(@data)=$table_data->fetchrow_array) {
        print "<LI>\n<UL>";
        for (0..$#data) {
                print "<LI>$fields[$_] => $data[$_]</li>\n";
        }
        print "</ul></li>";
}

print <<END_OF_HTML;
</ol>
</body></html>
      END_OF_HTML

Chapter 10. Perl

Contents:

NOTE

10.1. DBI

NOTE

NOTE

WARNING

Example 10-1. The CGI datashow.cgi Shows All of the Databases on a MySQL or mSQL Server

Example 10-2. The CGI tableshow.cgi Shows All of the Tables Within a Database

Example 10-3. The CGI tabledump.cgi Shows Information About a Specific Table