Home arrow Perl Programming arrow SQL and CGI with Perl and DBI

SQL and CGI with Perl and DBI

In this conclusion to a four-part series on Perl and DBI, you will learn about SQL queries and DBI, using Perl and DBI with CGI, and more. This article is excerpted from chapter 15 of the book Beginning Perl (Apress; ISBN: 159059391X).

TABLE OF CONTENTS:
  1. SQL and CGI with Perl and DBI
  2. A More Complex Example
  3. Use Placeholders
  4. DBI and Table Joins
  5. Perl DBI CGI = Fun!
  6. What We Didnít Talk About
By: Apress Publishing
Rating: starstarstarstarstar / 5
April 03, 2008

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement

Executing an SQL Query with DBI

The last example, connect.pl, demonstrated how we can connect to the database. Now it is time to talk about how to execute arbitrary SQL queries.7 We will first look at a program that will connect to the database musicians_db and display all the rows in the musicianstable. It is calledshowmusicians.pl:

#!/usr/bin/perl -w
# showmusicians.pl

use strict;
use DBI;

my $dbh = DBI->connect("DBI:mysql:musicians_db", "musicfan", "CrimsonKing");

die "connect failed: " . DBI->errstr() unless $dbh;

# prepare the query to get the data out
# of the musicians table
my $sth = $dbh->prepare("SELECT player_id,name,phone FROM musicians")
             or die "prepare failed: " . $dbh->errstr();

$sth->execute() or die "execute failed: " . $sth->errstr();

my($player_id, $name, $phone);

# loop through each row of data, printing
it
while (($player_id, $name, $phone) = $sth->fetchrow()) {
    print "$player_id : $name : $phone\n";
}

$sth->finish();

$dbh->disconnect();

This program connects as before, prepares and executes an SQL query, and then loops through the result of the query. Here is the code that prepares the query:

# prepare the query to get the data out
# of the musicians table
my $sth = $dbh->prepare("SELECT player_id,name,phone FROM musicians")
             or die "prepare failed: " . $dbh->errstr();

The database handler,$dbh, executes theprepare()method. This methodís job is to take its argument, an SQL query, and compile it and prepare it to execute. The query in this case is
"SELECT player_id,name,phone FROM musicians", which will select those three fields from the musicians table. If theprepare()method succeeds, it returns back an object, known as the state handler, that is assigned to$sth. If theprepare()method fails, it returns back false, and if so, wedie()printing$dbh->errstr(), the reason for the failure.

If all is well at this point, we then execute the query.

$sth->execute() or die "execute failed: " . $sth->errstr();

Theexecute()method executes the query, storing the result into the$sthobject. Ifexecute()fails, wedie(), and explain why by executing$sth->errstr().

The$sthobject has the result of the query stored within it, so we have to retrieve that information. Thefetchrow()method does this for us.

# loop through each row of data, printing it while (($player_id, $name, $phone) = $sth->fetchrow()) {
    print "$player_id : $name : $phone\n";
}

Thefetchrow()method returns the next row of information returned by the query. This code takes that row and copies it memberwise into three variables. Since our query asked for theplayer_id,name, andphonefrom themusicianstable, we take those three pieces of information and store them in$player_id,$name, and$phone, respectively. Those variables are then printed.

After we are done with the state handler, it is good practice to finish it with this code:

$sth->finish();

Executing this code produces the following:

$ perl showmusicians.pl
1 : Roger Waters : 555-1212
2 : Geddy Lee : 555-2323
3 : Marshall Mathers III : 555-3434
4 : Thom Yorke : 555-4545 
5 : Lenny Kravitz : 555-5656
6 : Mike Diamond : 555-6767
$
  

 



 
 
>>> More Perl Programming Articles          >>> More By Apress Publishing
 

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort
   

PERL PROGRAMMING ARTICLES

- Perl Turns 25
- Lists and Arguments in Perl
- Variables and Arguments in Perl
- Understanding Scope and Packages in Perl
- Arguments and Return Values in Perl
- Invoking Perl Subroutines and Functions
- Subroutines and Functions in Perl
- Perl Basics: Writing and Debugging Programs
- Structure and Statements in Perl
- First Steps in Perl
- Completing Regular Expression Basics
- Modifiers, Boundaries, and Regular Expressio...
- Quantifiers and Other Regular Expression Bas...
- Parsing and Regular Expression Basics
- Hash Functions

Developer Shed Affiliates

 


Dev Shed Tutorial Topics: