in reply to Re: prepared statement
in thread prepared statement

Since DBI's prepare_cached statement handle method caches the prepared statement handle in the database handle object, you could also do:
my $if_active = 1;
my $address_exists_sth = $dbh->prepare_cached(
   'SELECT * FROM EMAILS WHERE `email`=?', 
   { sub =>'address_exists' },
   $if_active,
);

[download]
in address_exists as long as you call $address_exists_sth->finish before exiting address_exists or set $if_active to whatever is appropriate, i.e. probably 1 (automatically call $sth->finish), though 3 (create a new statement handle and remove the previous one from the cache) is an option if you are using DBI greater than 1.40.

If this code is not guaranteed to be tied forever to MySQL, I would also replace the use of `s for quoting column names (which is a MySQLism) with "s which is more portable, including to MySQL.

Replies are listed 'Best First'.
Re^3: prepared statement
by saintex (Scribe) on Aug 18, 2010 at 19:30 UTC
    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]
[reply]
[d/l]
      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]
      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]
      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