The FreeBSD Diary

Providing practical examples since 1998

If you buy from Amazon USA, please support us by using this link.

Accessing a mysql database from perl 9 April 2000

Need more help on this topic? Click here
This article has 1 comment
Show me similar articles

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

Need more help on this topic? Click here
This article has 1 comment
Show me similar articles