in reply to prepare statement within DBI

Sending more than one SQL statement in a prepare() is in general not supported. Exceptions exist for databases such as Sybase and MS-SQL where multiple statements are supported natively by the server.

However, you could push the actual prepare/execute/fetch loop into a subroutine which would avoid the duplicated code (minimal and simplistic implementation): 

my $row1 = fetch_count($dbh, "table_one");
my $row2 = fetch_count($dbh, "table_two");

print "$row1, $row2\n";

sub fetch_count {
    my $dbh = shift;
    my $table = shift;

    my $sth = $dbh->prepare("select count(*) from $table");
    $sth->execute;
    my $row = $sth->fetch_arrayref;
    return $row->[0];
}

[download]
Michael

Replies are listed 'Best First'.
Re^2: prepare statement within DBI
by Smylers (Pilgrim) on Aug 26, 2004 at 14:27 UTC

    For most of the duplication you avoid with your fetch_count function, DBI has a built-in way of avoiding it: whenever you've got a query on which ->fetch_... is only invoked once, you can skip the separate ->prepare and ->execute by using a $db->select_... method.

    So the 4 active lines in your function become just 1:

    sub fetch_count
{
  my ($db, $table) = @_;

  $db->selectrow_array("SELECT COUNT(*) FROM $table");
}

    [download]

    It probably isn't worth writing a function for that, since there isn't that much duplication without it:

    my $row1 = $dbh->selectrow_array('SELECT COUNT(*) FROM table_one');
my $row2 = $dbh->selectrow_array('SELECT COUNT(*) FROM table_two');

    [download]

    But you could always put it in some sort of loop:

    my ($row1, $row2)
  = map { $dbh->selectrow_array("SELECT COUNT(*) FROM $_") }
  qw<table_one table_two>;

    [download]

    (By the way, I think your ->fetch_arrayref is a typo.)

    Smylers

[reply]
[d/l]
[select]

In Section Seekers of Perl Wisdom