Home arrow Perl Programming arrow Page 3 - SQL and CGI with Perl and DBI

Use Placeholders - Perl

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

Notice how the SQL query strings change in showinstrument2.pl:

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

use strict;
use DBI;

my($who, $player_id, $inst_id);

print "Enter name of musician and I will show you his/her instruments: ";
chomp($who = <STDIN>);

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

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

# first, grab the musicians player_id
my $sth = $dbh->prepare("SELECT player_id FROM musicians WHERE name = ?")
            
or die "prepare failed: " . $dbh->errstr();  

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

($player_id) = $sth->fetchrow();
die "player_id not found" unless defined $player_id; 
 

# given the player_id, grab their inst_ids from what_they_play
$sth = $dbh->prepare("SELECT inst_id FROM what_they_play
                             WHERE player_id = ?")
               or die "prepare failed: " . $dbh->errstr();  

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

# foreach inst_id, grab the instrument name from the
# instruments table and print it
while (($inst_id) = $sth->fetchrow()) {
   
my $sth = $dbh->prepare("SELECT instrument FROM instruments
                                WHERE inst_id = ?")
              or die "prepare failed: " . $dbh->errstr();

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

    my($instrument) = $sth->fetchrow();
    print "    $instrument\n";

    $sth->finish();
}

$sth->finish();

$dbh->disconnect();

The first call to prepare() and execute() has changed to

my $sth = $dbh->prepare("SELECT player_id FROM musicians WHERE name = ?")
            
or die "prepare failed: " . $dbh->errstr();  

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

Instead of using the variable $who in the query string, we use a question mark (?). This acts as a placeholder for a variable or value that we will provide later. That later ends up being an argument to the execute() method:$sth->execute($who).DBIwill take the argument$whoand plug it into the question mark in the query string. The nice thing about using this feature is that we don’t have to worry about escaping the single quote. Much better!

 

You may be wondering—what if there is more than one variable in the query string? All of their values are provided in theexecute()method and are plugged into the placeholders member-wise as shown in this snippet:

$sth = $dbh->prepare("SELECT * FROM data WHERE name = ? AND age = ?");
$sth->execute($name, $age);

But wait a minute! Bothshowinstruments1.plandshowinstruments2.plare using three SQL queries. We learned earlier in this chapter that we could obtain the same information using one query by using a table join.



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