Re: Perl DBI - How to handle large resultsets?
- From: Keith Keller <kkeller-usenet@xxxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Thu, 28 Jul 2005 15:27:20 -0700
On 2005-07-28, david best <david.best@xxxxxxxxx> 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@xxxxxxxxxxxxxxxxxxxxxxxxxx
(try just my userid to email me)
AOLSFAQ=http://wombat.san-francisco.ca.us/cgi-bin/fom
see X- headers for PGP signature information
.
- References:
- Perl DBI - How to handle large resultsets?
- From: david best
- Perl DBI - How to handle large resultsets?
- Prev by Date: Re: multidimensional insertion order
- Next by Date: Re: DBI MySQL Use Another DB Without Disconnecting
- Previous by thread: Re: Perl DBI - How to handle large resultsets?
- Next by thread: FAQ 4.6 Why doesn't & work the way I want it to?
- Index(es):
Relevant Pages
|
