Perl DBI - How to handle large resultsets?

Discussion in 'Perl Misc' started by david best, Jul 28, 2005.

  1. david  best

    david best Guest

    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;
    }
     
    david best, Jul 28, 2005
    #1
    1. Advertising

  2. david  best

    Guest

    "david best" <> 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
     
    , Jul 28, 2005
    #2
    1. Advertising

  3. david  best

    stone Guest

    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.
     
    stone, Jul 28, 2005
    #3
  4. david  best

    Brian Wakem Guest

    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
     
    Brian Wakem, Jul 28, 2005
    #4
  5. david  best

    Keith Keller Guest

    On 2005-07-28, david best <> 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

    --
    -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
     
    Keith Keller, Jul 28, 2005
    #5
  6. david  best

    stone Guest

    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.
     
    stone, Jul 29, 2005
    #6
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Amber
    Replies:
    0
    Views:
    460
    Amber
    Oct 7, 2003
  2. John Dalberg
    Replies:
    0
    Views:
    461
    John Dalberg
    Mar 23, 2006
  3. Ron Pagliuca
    Replies:
    0
    Views:
    436
    Ron Pagliuca
    Feb 25, 2004
  4. mistral

    Combining resultsets?

    mistral, May 22, 2004, in forum: Java
    Replies:
    6
    Views:
    5,867
    Dimitri Maziuk
    May 22, 2004
  5. Marc E
    Replies:
    1
    Views:
    1,032
    Bjorn Abelli
    Nov 5, 2005
Loading...

Share This Page