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
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>