Accessing a mysql database from perl

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/libdata/perl/5.00503 
                    /usr/local/lib/perl5/site_perl/5.005/i386-freebsd 
                    /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/libdata/perl/5.00503 
                     /usr/local/lib/perl5/site_perl/5.005/i386-freebsd
                     /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
wocker
march
daniel
dan
dan1

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 
#!/usr/bin/perl

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.

Resources

http://stars.com/Authoring/Languages/Perl/PerlfortheWeb/wheres_perl.html
http://www.mysql.org