saintex has asked for the wisdom of the Perl Monks concerning the following question:

Hello Monks,
I have a doubt related to prepared statement.

If I'm in a While loop, and I do a call to a subroutine that prepare my statement, my statement is still prepared if I use a lexical variable?

Some code explains my doubt: 
sub addressExists {
    
    my $address=shift @_; 
    
    my $sql = 'SELECT * FROM emails WHERE `email`=?';
    
        
    my $sth = $dbh->prepare( $sql );
        
     $sth->execute($address);

    
}

[download]
And then:
#open connection

# some code here and then:

while (my $address=@address) {
 &addressExists($address);
# other stuff

}

#close connection

[download]

The statement is still prepared, if I use a "my" variable inside my subroutine, or I have a new statement prepration anytime I call my subroutine?

(I think that yes, the statement is still prepared, because my connection is still alive and it is the same).

please let me Know your advise.
Thank you.

Replies are listed 'Best First'.
Re: prepared statement
by ikegami (Patriarch) on Aug 18, 2010 at 16:49 UTC 
    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]
[reply]
[d/l]
[select]
      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]
Re: prepared statement
by jethro (Monsignor) on Aug 18, 2010 at 16:16 UTC
    This has nothing to do with whether you use 'my' or not. You call $dbh->prepare whenever the subroutine is run. (It is possible that the method prepare checks if it is called with the same parameters and does nothing in that case, check your module documentation)
[reply]
      ok,
      I understood!
      Thank you!
[reply]

Back to Seekers of Perl Wisdom