Your skill will accomplishwhat the force of many cannot PerlMonks

### Fetch Data from MySQL

by anu2011 (Initiate)
 on Sep 21, 2011 at 16:04 UTC Need Help??
anu2011 has asked for the wisdom of the Perl Monks concerning the following question:

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

Replies are listed 'Best First'.
Re: Fetch Data from MySQL
by zwon (Abbot) on Sep 21, 2011 at 16:15 UTC

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.

Re: Fetch Data from MySQL
by runrig (Abbot) on Sep 21, 2011 at 16:17 UTC
Re: Fetch Data from MySQL
by zentara (Archbishop) on Sep 21, 2011 at 17:48 UTC
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 :
my $username = <STDIN>; chomp$username;  # remove trailing newline from Enter keypress
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;

[download]

I'm not really a human, but I play one on earth.
Old Perl Programmer Haiku ................... flash japh
Re: Fetch Data from MySQL
by tospo (Hermit) on Sep 22, 2011 at 08:24 UTC
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.

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.

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.

Create A New User
Node Status?
node history
Node Type: perlquestion [id://927161]
Approved by Corion
Front-paged by Corion
help
Chatterbox?
 erix . o O( "what fools the french are, Jeeves" ) [choroba]: Also some Достое́вс кий [Discipulus]: mmh windows understand something like: cd c:\\\\path\\\\to weird.. [Discipulus]: even with odd number of \

How do I use this? | Other CB clients
Other Users?
Others exploiting the Monastery: (11)
As of 2017-05-24 08:14 GMT
Sections?
Information?
Find Nodes?
Leftovers?
Voting Booth?
My favorite model of computation is ...

Results (183 votes). Check out past polls.