Perl
  Home arrow Perl arrow SQL and CGI with Perl and DBI
Dev Shed Forums  
Administration  
AJAX  
Apache  
BrainDump  
DHTML  
Flash  
Java  
JavaScript  
Multimedia  
MySQL  
Oracle  
Perl  
PHP  
Practices  
Python  
Reviews  
Security  
Smartphone Development  
Style-Sheets  
Web Services  
XML  
Zend  
Zope  
Mobile Linux  
App Generation ROI  
IBM® developerWorks  
Forums Sitemap  
E-Commerce Hosting  
Linux Web Hosting  
Managed Hosting  
Small Business Hosting  
VPS Hosting  
Weekly Newsletter

 
Developer Updates  
Free Website Content 
 RSS  Articles
 RSS  Forums
 RSS  All Feeds
Write For Us Get Paid  
Request Media Kit
Contact Us  
Site Map  
Privacy Policy  
Support  
 USERNAME
 
 PASSWORD
 
 
  >>> SIGN UP!  
  Lost Password? 
PERL

SQL and CGI with Perl and DBI
By: Apress Publishing
  • Search For More Articles!
  • Disclaimer
  • Author Terms
  • Rating: starstarstarstarstar / 4
    2008-04-03


    Table of Contents:
  • SQL and CGI with Perl and DBI
  • A More Complex Example
  • Use Placeholders
  • DBI and Table Joins
  • Perl DBI CGI = Fun!
  • What We Didn’t Talk About

  • Rate this Article: Poor Best 
      ADD THIS ARTICLE TO:
      error-file:tidyout.log Del.ici.ous error-file:tidyout.log Digg
      error-file:tidyout.log Blink error-file:tidyout.log Simpy
      error-file:tidyout.log Google error-file:tidyout.log Spurl
      error-file:tidyout.log Y! MyWeb error-file:tidyout.log Furl
    Email Me Similar Content When Posted
    Add Developer Shed Article Feed To Your Site
    Email Article To Friend
    Print Version Of Article
    PDF Version Of Article

     
     
    ADVERTISEMENT


    SQL and CGI with Perl and DBI
    ( Page 1 of 6 )

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

    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 musicians table. It is called showmusicians.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 the prepare() 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 the prepare() method succeeds, it returns back an object, known as the state handler, that is assigned to $sth . If the prepare() method fails, it returns back false, and if so, we die() 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();

    The execute() method executes the query, storing the result into the $sth object. If execute() fails, we die() , and explain why by executing $sth->errstr() .

    The $sth object has the result of the query stored within it, so we have to retrieve that information. The fetchrow() 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";
    }

    The fetchrow() 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 the player_id , name , and phone from the musicians table, we take those three pieces of infor mation 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 Articles          >>> More By Apress Publishing
     

       

    PERL ARTICLES

    - Basic Charting with Perl
    - Using Getopt::Long: More Command Line Option...
    - Command Line Options in Perl: Using Getopt::...
    - Web Access with LWP
    - More Templating Tools for Perl
    - Site Layout with Perl Templating Tools
    - Build a Perl RSS Aggregator with Templating ...
    - Looping, Security, and Templating Tools
    - Perl: Bon Voyage Lists and Hashes
    - Templating Tools
    - Perl: Number Crunching
    - Perl Debuggers in Detail
    - Debugging Perl
    - Perl: More on Lists and Hashes
    - Perl: Dimensional Lists





    © 2003-2009 by Developer Shed. All rights reserved. DS Cluster 4 hosted by Hostway
    Stay green...Green IT