Apr 092000

Accessing a mysql database from perl

In previous articles I wrote about how to install and configure mysql (a very popular database).  In this article, I write about how to access a mysql database from within a perl program.  This will not be a full tutorial on the perl/mysql interface.  Rather, it just shows you what ports need to be installed.

The install

Remember, I have the entire ports tree installed.  So adding these ports were.  See also, Installing a port without installing the ports.

First, I just tried to connect to mysql, thinking that perhaps the required software was already installed.  I mean, why bother installing without first checking.  Nope.  It was not installed.  Here’s the error message:

$ perl connect.pl 
Can't locate DBI.pm in @INC 
                    (@INC contains: /usr/libdata/perl/5.00503/mach
                    /usr/local/lib/perl5/site_perl/5.005 .)
                    at connect.pl line 3.
BEGIN failed--compilation aborted at connect.pl line 3.

I didn’t know what ports I needed in order to get at mysql from within perl.   While I was at one of my resource sites, I found a reference to the DBI (which I think means DataBase-independent Interface; comments are welcome.)   So I found and installed that port:

cd /usr/ports/databases/p5-DBI
make install

Then I tried again.  No luck:

$ perl connect.pl 
install_driver(mysql) failed: Can't locate DBD/mysql.pm in @INC 
                     (@INC contains: /usr/libdata/perl/5.00503/mach
                     /usr/local/lib/perl5/site_perl/5.005 .)
                     at (eval 1) line 3.
Perhaps the DBD::mysql perl module hasn't been fully installed,
or perhaps the capitalisation of 'mysql' isn't right.
Available drivers: ADO, ExampleP, Proxy.
 at connect.pl line 5

So then I looked for a perl-mysql port.  I found one and installed it:

cd /usr/ports/databases/p5-Mysql
make install

And I tried again:

$ perl connect.pl

Which means it worked.  See the next section for the sample code:

Sample code

For this test, I just wanted a very simple program which would connect to the database, select some stuff from a table, and print it out.  Here it is:
$ more connect.pl 

use DBI;

$dbh = DBI->connect('dbi:mysql:mydb','myuserid','mypassword');

$sql = "select name from boxes";

$sth = $dbh->prepare($sql);

$sth->execute || 
           die "Could not execute SQL statement ... maybe invalid?";

#output database results
while (@row=$sth->fetchrow_array)
   { print "@row\n" }

Where you must make the following substitutions:

  • mydb = the name of your database
  • myuserid = the user id to connect with
  • mypassword = the password to connect with

You should also subsititue your own SQL.  Perhaps you should also disconnect from the database.  I haven’t read the perl/mysql module yet.  The above is based on the sample code I found on the web.



  One Response to “Accessing a mysql database from perl”

  1. One thing this was missing what how to connect to something other than localhost. The URL below has more info on DBI syntax from the mysql people:

    <a href="http://www.mysql.com/doc/P/e/Perl_DBI_Class.html">here</a&gt;