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

Comment on Re: prepare statement within DBI Download Code

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]


Welcome to the Monastery
	PerlMonks