Yehoshua has asked for the wisdom of the Perl Monks concerning the following question:

I am told to find the differences between these 2 functions: report1 and report2. I cant figure out what benefits each function has.

I know that report1 uses placeholders and calls execute many times while report2 calls execute once, and doesnt use placeholders.

Cheers!


#!/usr/local/bin/perl

use lib '/usr/local/ct.com/lib';
use CTC::DB;

#---------------------------------------------------------------------
+---------

{
    my $table = 'tech_sched.student_enrollment_statuses';
    $DBH = CTC::DB->connect();
    &report1($table,35,36,11);
    print "----------------\n";
    &report2($table,35,36,11);
}

#---------------------------------------------------------------------
+---------

sub report1 {
    my $table = shift @_;
    my @statusids = @_;

    my $sql = "SELECT COUNT(*) FROM $table WHERE statusid = ?";
    my $sth = $DBH->prepare($sql);
    
    foreach my $statusid (sort @statusids) {
    $sth->execute($statusid);
    my ($count) = $sth->fetchrow_array;
    print "$statusid = $count\n";
    }
}

#---------------------------------------------------------------------
+---------

sub report2 {
    my $table = shift @_;
    my @statusids = @_;

    my $sql = "SELECT statusid FROM $table WHERE statusid IN (" 
    . join(',',@statusids) . ")";

    my $sth = $DBH->prepare($sql);
    $sth->execute;

    my %count;
    while (my ($statusid) = $sth->fetchrow_array) {
    $count{$statusid}++;
    }
    
    foreach my $statusid (sort @statusids) {
    my $count = $count{$statusid}++ || 0;
    print "$statusid = $count\n";
    }
}

[download]
#------------------------------------------------------------------------------

Replies are listed 'Best First'.
Re: Dont understand differences between these 2 functions!
by ikegami (Patriarch) on Oct 09, 2007 at 19:33 UTC

    report1

    • Assumes the table argument is a properly quoted table name.
    • Use a placeholder to avoid problems from (intentially or unintentially) badly formed statusid arguments.
    • Executes one query per statusid argument.
    • Does the counting server-side.
    • Can do result ordering server-side.

    report2

    • Assumes the table argument is a properly quoted table name.
    • Assumes the statusid arguments are valid numbers.
    • Only executes one query.
    • Does the counting client-side.
    • Result ordering must be done client-side.

    The different assumptions impose different validation requirements on the caller.
    The number of queries affects performance.

    Update: report3 uses the best features of report1 and report2. 

    sub report3 {
    my $table = shift @_;
    my @statusids = @_;

    my $sql = "
        SELECT statusid, COUNT(statusid)
          FROM $table
         WHERE statusid IN (". join(',', ('?')x@statusids) . ")
         GROUP BY statusid
    ";

    my $sth = $DBH->prepare($sql);
    $sth->execute(@statusids);

    while (my ($statusid, $count) = $sth->fetchrow_array) {
        print "$statusid = $count\n";
    }
}

    [download]
    • Assumes the table argument is a properly quoted table name.
    • Use a placeholder to avoid problems from (intentially or unintentially) badly formed statusid arguments.
    • Only executes one query.
    • Does the counting server-side.
    • Can do result ordering server-side.
[reply]
[d/l]

Back to Seekers of Perl Wisdom