Perl DBI - How to handle large resultsets?

[david best]

Hey all,

I'm getting the errors:

DBD:g::st fetchrow_array failed: no statement executing at ./snap.pl
line 115.
DBD:g::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]

Hey all,

I'm getting the errors:

DBD:g::st fetchrow_array failed: no statement executing at ./snap.pl
line 115.
DBD:g::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

 

[stone]

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]

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.

 

[Keith Keller]

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

 

[stone]

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.