in reply to Re^2: prepared statement
in thread prepared statement

thank you all for your answers.
It's safe to have multiple statements running in a while, or there is the risk of confusion (I mean having some result sent indeed another one)? I mean: 
my $sth1 = $dbh->prepare( $sql1 ); # it can be a select
my $sth2 = $dbh->prepare( $sql2 ); # it can be an insert


while(my $var=@arr){
$sth1->execute($param1);
$sth2->execute($param2);
}

[download]

Replies are listed 'Best First'.
Re^4: prepared statement
by ikegami (Patriarch) on Aug 18, 2010 at 20:23 UTC
    Not a problem, although some database can't have more than one active statement handles per database handle. For those, you'll need to create a database handle for each statement.
[reply]
Re^4: prepared statement
by james2vegas (Chaplain) on Aug 18, 2010 at 20:03 UTC
    as long as you use separate variables to hold the different statement handles, then no. The only risk of confusion is you or a future maintainer of the code confusing $sth1 or $sth2, so more meaningful names for your variables may be appropriate.
[reply]
[d/l]
[select]
Re^4: prepared statement
by saintex (Scribe) on Aug 19, 2010 at 11:11 UTC
    If I try to diconnect from my connection, I have this warning message, while the statements are still active:
    disconnect invalidates 1 active statement handle (either destroy state
+ment handles or call finish on them before disconnecting)

    [download]
    The DBI manual tell us don't use $sth->finish statement.
    How can I avoid to have that warn messagge?
[reply]
[d/l]

      You should not ->finish () select handles that are done reading:

      my $sth = $dbh->prepare ("select * from foo");
$sth->execute;
while (my $ref = $sth->fetch) {
    print $ref->[0], "\n";
    }
# No need for $sth->finish;

      [download]

      The safest way to never worry about this is to make those select loops have the smallest possible context, so the handle gets DESTROY'd on leaving scope:

      {   my $sth = $dbh->prepare (...);
    $sth->execute;
    ...
    } # End-of-scope: $sth is destroyed

      [download]

      It is usually advisable to finish update/insert/delete handles, as they have no obvious "done" state.

      {   my $stu = $dbh->prepare ("update foo set blah = ? where c_foo = ?"
+);
    my $sth = $dbh->prepare ("select c_foo, bar from brimble");
    $sth->execute;
    while (my $r = $sth->fetchrow_hashref) {
        $stu->execute ($r->{bar}, $r->{c_foo});
        }
    # no need for $sth->finish
    $stu->finish; # Good practice
    } # End of scope: clean up

      [download]

      Enjoy, Have FUN! H.Merijn
[reply]
[d/l]
[select]

In Section Seekers of Perl Wisdom