how to pass in sql query input file to a perl dbi sub routine
I like to create a generic perl script that will input sql query from a separate file and use it with perl dbi (subroutine), rather than hardcoding the statement. Can someone sh开发者_如何学编程ow me an example how to do this?
For example I have this in a sub routine:
sub get_val
{
my $sth = $dbh->prepare(q{SELECT count(*) AS COUNT FROM TEST1) ||
die ("Can't connect: ".$DBI::errstr."\n");
$sth->execute;
my $row = $sth->fetchrow_hashref;
$sth->finish;
return $row->{COUNT};
}
This would be the general idea:
$/ = ';';
open FH, "< file.sql";
while (<FH>) {
$dbh->do($_);
# or:
# my $sth = $dbh->prepare($_);
# $sth->execute();
}
close FH;
Of course this won't necessarily handle comments, or ;
characters in quoted strings, etc. But this should head you in the right direction.
Or if you know the file will only contain a single statement:
undef $/;
open FH, "< file.sql";
my $sth = $dbh->prepare(<FH>);
close FH;
$sth->execute();
精彩评论