The FreeBSD Diary

The FreeBSD Diary (TM) Remember
I remember
[ HOME | TOPICS | INDEX | WEB RESOURCES | BOOKS | CONTRIBUTE | SEARCH | FEEDBACK | FAQ | FORUMS ]

Things look quiet here. But I've been doing a lot of blogging at dan.langille.org because I prefer WordPress now. Not all my posts there are FreeBSD related. I am in the midst of migrating The FreeBSD Diary over to WordPress (and you can read about that here). Once the migration is completed, I'll move the FreeBSD posts into the new FreeBSD Diary website.

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


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