Fetch Data from MySQL

[anu2011]

Hi This may be a very simple question to those who have experience coding in Perl. I am trying to see whether I can use Perl along with mySQL database to develop one application. The available Perl commands are more than sufficient. Then, when I asked a programmer to write a small program to read and write data from a simple mySQL database, she got into problems and still could not find a way to do that. Could you please let me know whether it is too difficult to fetch data from a mySQL Database. If not, I's appreciate if you could you provide a simple sample code for me to try out. I am using mySQL and Perl in Fedora O/S. Thanks in advance. Anu

[zwon]

It's quite simple. Standard way to access databases in perl is DBI module with appropriate DBD::* driver. Have a look onto DBD::mysql, its documentation contains example.

[runrig]

It should be easy. You will need to make sure DBI and DBD::mysql are installed, then examples should be easy to find. You could even search this site for examples.

[zentara]

Here is your basic example, setting up and populating the test data table is up to you. You must have DBI and DBD::Mysql installed. DBI will find all installed sql drivers automatically

#!/usr/bin/perl
use warnings;
use strict;
use DBI;

# Gather some information from the user :
print "Enter your MySQL username: ";
my $username = <STDIN>;
chomp $username;  # remove trailing newline from Enter keypress
print "Enter your MySQL password; ";
my $password = <STDIN>;
chomp $password;

# Connect to the database
my $dbh = DBI->connect("DBI:mysql:database=perl:host=localhost",$usern
+ame,$password) || die  "Couldn't connect to database";
print "Database connection established.";
# Prepare statement
my $sth = $dbh->prepare( "SELECT * FROM perl_table" );
# Send statement to database
$sth->execute || die "Couldn't execute statement";
# Print out the data
my @row;
while ( @row = $sth->fetchrow_array() ) {
print "@row \n";
}
# One more thing: we need to disconnect from the database server
# when we're done.
$dbh->disconnect;

[tospo]

Yes, this is standard stuff.
Apart from the documentation for the DBI module, you might also want to get "Programming the Perl DBI" from O'Reilly. It is quite old now but if you use simple DBI then it still tells you all you need to know about it and it is well written and has easy examples.
This may be all you need for a simple project but be aware of the fact that there are newer approaches to database interaction around, such as object-relational mapping, e.g. with DBIC which allow you to interact with a database without writing SQL queries.
IMHO, if you are just getting started with this, you might as well learn DBIC instead of bare-bones DBI.

[Corion]

Ugh. Please don't recommend any of the object-relational mappers as a way around learning SQL. All ORMs I've seen so far allow you to avoid writing simple SQL statements, but horribly fail once your needs go beyond fetching single rows. Some ORMs allow you to define how to join tables together, but they all fail once your needs go towards more complex statements.

In addition, ORMs are the wrong way to think about your data. They make you think of aggregates over your data in terms of Perl loops instead of pushing the work down to the database. Also, they either force you to write a separate class representing a query result or think that all results you will ever fetch from the database correspond to a single row in one table. Neither approach makes much sense to me.

[aaron_baugher]

Thank you. I'm glad I'm not the only one who doesn't see the point of working so hard to abstract away the SQL. DBIC is recommended so often that I keep looking at it, but it just looks like extra work. Writing and handling SQL queries -- especially ones simple enough for an ORM to handle -- isn't really that difficult.