PERL Miscellaneous - Perl DBI - How to handle large resultsets?

Author

Perl DBI - How to handle large resultsets?

david best

2005-07-28, 5:01 pm

Hey all,

I'm getting the errors:

DBD::Pg::st fetchrow_array failed: no statement executing at ./snap.pl
line 115.
DBD::Pg::st fetchrow_array failed: no statement executing at ./snap.pl
line 115.

The only reason I can figure is because I have a couple of queries that
return a large resultset... How do I handle such cases? Here is same
code:

The database handlers get passed in. Don't worry about the parameters
to the queries because I edited that out.

sub myproc {

my $err=0;
my ($repo_dbh, $target_dbh, $snap_id) = @_;
my $target_sth = $target_dbh->prepare(
q{ SELECT columns
FROM dba_free_space }) or "Can't prepare statement:
$DBI::errstr";
$target_sth->execute() or die $DBI::errstr;

while (my ($data) = $target_sth->fetchrow_array()) {
eval {
$repo_sth = $repo_dbh->prepare("INSERT into mytable
(snap, data)
VALUES (?, '$data')");
$repo_sth->execute($snap_id) or die $DBI::errstr;
};
}
# check for errors.. If there are any rollback
if ( $@ ) {
$err = 1;
}

$repo_sth->finish();
$target_sth->finish();

return $err;
}

xhoster@gmail.com

2005-07-28, 5:01 pm

"david best" <david.best@gmail.com> wrote:
> Hey all,
>
> I'm getting the errors:
>
> DBD::Pg::st fetchrow_array failed: no statement executing at ./snap.pl
> line 115.
> DBD::Pg::st fetchrow_array failed: no statement executing at ./snap.pl
> line 115.

Which line is line 115?

> The only reason I can figure is because I have a couple of queries that
> return a large resultset

How do you figure that?

>
> sub myproc {
>
> my $err=0;
> my ($repo_dbh, $target_dbh, $snap_id) = @_;
> my $target_sth = $target_dbh->prepare(
> q{ SELECT columns
> FROM dba_free_space }) or "Can't prepare statement:
> $DBI::errstr";

Is there supposed to be a die in there somewhere? A very good reason to
use RaiseError and let DBI do the dirty work for you.

> $target_sth->execute() or die $DBI::errstr;
>
> while (my ($data) = $target_sth->fetchrow_array()) {
> eval {
> $repo_sth = $repo_dbh->prepare("INSERT into mytable
> (snap, data)
> VALUES (?, '$data')");

Are you using strict? If not, then why not? If so, where is $repo_sth
being declared?

> $repo_sth->execute($snap_id) or die $DBI::errstr;
> };
> }
> # check for errors.. If there are any rollback
> if ( $@ ) {
> $err = 1;
> }
>
> $repo_sth->finish();

Are you allowed to "finish" an insert statement? I thought that was only
for selects.

> $target_sth->finish();
>
> return $err;
> }

Xho

--
-------------------- http://NewsReader.Com/ --------------------
Usenet Newsgroup Service $9.95/Month 30GB

stone

2005-07-28, 5:01 pm

Hey, tnx for the reply.

I'm pretty sure i'm getting these errors because of the large result
set because I have 6 other identical functions which work but only
return a couple of dozen rows. In this particular case its in the
10's of thousands.

I've commented out the functions that return the large result sets and
the program runs without error.

And Opps.. I missed the declare of repo_sth in that function.

Brian Wakem

2005-07-28, 5:02 pm

stone wrote:

> Hey, tnx for the reply.
>
> I'm pretty sure i'm getting these errors because of the large result
> set because I have 6 other identical functions which work but only
> return a couple of dozen rows. In this particular case its in the
> 10's of thousands.
>
> I've commented out the functions that return the large result sets and
> the program runs without error.
>
> And Opps.. I missed the declare of repo_sth in that function.

I doubt the size of the result set is your problem. The DBI module is quite
mature. I have a app that returns 10's of thousands of rows per execution,
and it gets hit thousands of times a day. In the extreme I've run queries
that have returned millions of rows and DBI has never choked on me.

--
Brian Wakem

Keith Keller

2005-07-28, 5:02 pm

On 2005-07-28, david best <david.best@gmail.com> wrote:
>
> The only reason I can figure is because I have a couple of queries that
> return a large resultset... How do I handle such cases?

At the risk of being a me-too, I have also had no problems with DBI and
large result sets. I suspect a problem with the code is triggering the
errors (and, as Xho asked, which line is 115?).

> Here is same
> code:
>
> The database handlers get passed in. Don't worry about the parameters
> to the queries because I edited that out.
>
> sub myproc {
>
> my $err=0;
> my ($repo_dbh, $target_dbh, $snap_id) = @_;
> my $target_sth = $target_dbh->prepare(
> q{ SELECT columns
> FROM dba_free_space }) or "Can't prepare statement:
> $DBI::errstr";
> $target_sth->execute() or die $DBI::errstr;
>
> while (my ($data) = $target_sth->fetchrow_array()) {
> eval {
> $repo_sth = $repo_dbh->prepare("INSERT into mytable
> (snap, data)
> VALUES (?, '$data')");

This line is probably better outside the while loop; prepare $repo_sth
with two placeholders, and execute it in the while loop passing in
$snap_id and $data.

> $repo_sth->execute($snap_id) or die $DBI::errstr;
> };
> }
> # check for errors.. If there are any rollback
> if ( $@ ) {
> $err = 1;
> }
>
> $repo_sth->finish();
> $target_sth->finish();

Again, as Xho suggests, finish() should not be called on $repo_sth. In
fact, in this case it shouldn't be called on $target_sth, since the
while loop should have eaten up the result set and caused DBI to call
finish() automatically. If you feel that you must call finish(), I'd do
so in the if ($@) block, but it really looks like it's not needed.
Read perldoc DBI on the finish() method for more info.

--keith

--
kkeller-usenet@wombat.san-francisco.ca.us
(try just my userid to email me)
AOLSFAQ=http://wombat.san-francisco.ca.us/cgi-bin/fom
see X- headers for PGP signature information

stone

2005-07-29, 5:03 pm

FYI all, thanks for the responses.. I found my problem....

The functions in question had routines which returned alot of data but
that wasn't the problem. The problem was on the insert into my
repository database that it failed. Once I changed it to using bind
variables it ran fine...

Not sure why tho.

Sponsored Links