in reply to prepared statement

my $address_exists_sth;
sub address_exists {
   my ($address) = @_; 
   $address_exists_sth ||= $dbh->prepare('
      SELECT * FROM emails WHERE `email`=?
   );
   $address_exists_sth->execute($address);
   my $rec = $address_exists_sth->fetch();
   $address_exists_sth->finish();
   return $rec && 1;
}

for my $address (@addresses) {
   if (address_exists($address)) {
      ...
   }
}

[download]

Update: DBI can already do some of the work for us: 

my $address_exists_sth;
sub address_exists {
   my ($address) = @_; 
   $address_exists_sth ||= $dbh->prepare('
      SELECT * FROM emails WHERE `email`=?
   );
   return $dbh->selectrow_arrayref($address_exists_sth) && 1;
}

for my $address (@addresses) {
   if (address_exists($address)) {
      ...
   }
}

[download]

Replies are listed 'Best First'.
Re^2: prepared statement
by james2vegas (Chaplain) on Aug 18, 2010 at 17:27 UTC
    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.
[reply]
[d/l]
[select]
      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]

In Section Seekers of Perl Wisdom