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

Hello,

Can I have multiple prepare/executes DBI statements with in a loop - or will this cause some problems?
I have a list loaded from <PA> - it is a bunch of names and addresses. I am checking to see if they are in the database. If so - update them.

....<previous code connects to database OK and variable
defined>.......

print "Selecting.... \n";
$sth=$dbh->prepare("SELECT name,address_number from many_address      
                    WHERE name=?
                    AND number=?
                    AND street=? ");
        while ($_=<PA>) {
                ($name,$add,$street)=split(/\|/,$_);
                chomp ($name,$add,$street);
                $name = '' unless defined $name;
                $sth->execute($name,$add,$street);
                #$rec=$sth->dump_results(); ### see results
                @rec = $sth->fetchrow_array();
                if (@rec) { ### check to see if @res is valued
                    print "$name $add,$street --> @rec. \n";
                    $sth=$dbh->prepare("UPDATE many_address
                        SET name=NULL,
                        date_loaded=NULL
                        WHERE name=?
                        AND number=?
                        AND street=? ");
                        $sth->execute($name,$add,$street);
                        print "Clearing $name,$add,$street -->@rec
\n";
                }
                else {
                print " Not available -->@rec is  \n";
                }

        }
close(PA);

[download]
This code executes with no errors - too bad it changes t nothing. I think having multiple prepare statement is the loop are getting confused?
I was thinking of collecting all the @rec values into another array - the looping through it later - outside them main loop. But can you push an array on to another array.

i.e. push (@new_array,@rec);

I tried it for fun but since @rec hold 2 values, @new_array considers each value a separate element.

i.e. @rec looks like "jim 10"
@new_array treats it as "jim"
"10"

help? Any suggestions?
Thanks,
He who laughs last, doesn't get the joke.

Replies are listed 'Best First'.
Re: DBI multiple prepares
by rdfield (Priest) on Nov 04, 2002 at 15:41 UTC
    You're reusing $sth for different SQL statements at the same time. Don't do that, use different variables, e.g. 
    $sth=$dbh->prepare("SELECT name,address_number from many_address      
                    WHERE name=?
                    AND number=?
                    AND street=? ");
...
$sth_update=$dbh->prepare("UPDATE many_address
                        SET name=NULL,
                        date_loaded=NULL
                        WHERE name=?
                        AND number=?
                        AND street=? ");

    [download]

    rdfield

[reply]
[d/l]
[select]
Re: DBI multiple prepares
by tommyw (Hermit) on Nov 04, 2002 at 15:48 UTC

    Apart from anything else, you're re-preparing the update statement every time around the main loop, despite its text not changing. This is exactly why bind variables (which you're using) were introduced.

    
print "Selecting.... \n";
$sel=$dbh->prepare("SELECT name,address_number
                   from many_address      
                   WHERE name=?
                   AND number=?
                   AND street=? ");
$upd=$dbh->prepare("UPDATE many_address
                    SET name=NULL,
                    date_loaded=NULL
                    WHERE name=?
                    AND number=?
                    AND street=?");
while ($_=<PA>) {
      ($name,$add,$street)=split(/\|/,$_);
      chomp ($name,$add,$street);
      $name = '' unless defined $name;
      $sth->execute($name,$add,$street);
      #$rec=$sth->dump_results(); ### see results
      @rec = $sth->fetchrow_array();
      if (@rec) { ### check to see if @res is valued
         print "$name $add,$street --> @rec. \n";
         $upd->execute($name,$add,$street);
         print "Clearing $name,$add,$street -->@rec \n";
      } else {
         print "Not available --> \@rec is empty\n";
      }
}
close(PA);

    [download]

    --
    Tommy
    Too stupid to live.
    Too stubborn to die.

[reply]
[d/l]
(jeffa) Re: DBI multiple prepares
by jeffa (Bishop) on Nov 04, 2002 at 15:58 UTC
    Just a thought for you: if you don't really need to know whether or not the row you are attempting to update exists or not, then just update it: 
    my $sth = $dbh->prepare("
   UPDATE many_address
   SET name=NULL, date_loaded=NULL
   WHERE name=? AND number=? AND street=?
");

while (<RA>) {
   my ($name,$add,$street) = split(/\|/,$_);
   chomp($street);
   $sth->execute($name,$add,$street);
}

    [download]
    SQL doesn't care. If the name, address, and street don't match any existing record, then the update will not happen. Of course, for proper error checking my suggestion may not be helpful, but i still thought i should put this thought in your mind anyway. ;)

    P.S. Get out of the habit of using NULL now, before it is too late! Use another field named 'status' if you are wanting to 'delete' a record without removing it from the table.

    UPDATE:
    Well, there is a way to find out if a row was updated or not, but you have to use do() instead, thus giving up your placeholders. From the docs: 

    $rows_affected = $dbh->do("UPDATE your_table SET foo = foo + 1");

    [download]
    UPDATE x 2:
    Thanks iburrell and mpeppler. execute indeeds returns the number of rows updated or the value 0E0 (which means zero but not true - typos: sigh, thanks grantm!) if no rows were updated (it will return -1 if there is an error). Here is an update of the above code: 
    ...

while (<RA>) {
   my ($name,$add,$street) = split(/\|/,$_);
   chomp($street);
   my $ret = $sth->execute($name,$add,$street);
   print "no update for $name\n" if $ret == 0;
}

    [download]

    jeffa

    L-LL-L--L-LL-L--L-LL-L--
-R--R-RR-R--R-RR-R--R-RR
B--B--B--B--B--B--B--B--
H---H---H---H---H---H---
(the triplet paradiddle with high-hat)
[reply]
[d/l]
[select]
      It is possible to use placeholders with do. The syntax is: 
      $rows = $dbh->do("UPDATE your_table SET foo = ?", undef, $foo);

      [download]
      However, you don't need to use do though. execute returns the number of rows updated for a non-SELECT statement.
[reply]
[d/l]
[select]
      Well, there is a way to find out if a row was updated or not, but you have to use do()
      Normally execute() returns the number of rows affected for update/delete/insert statements. Certainly that's what do() uses to return the value to the caller.

      Michael

[reply]
[d/l]
[select]
Re: DBI multiple prepares
by robartes (Priest) on Nov 04, 2002 at 15:43 UTC
    I haven't looked too closely, but you seem to be redifining $sth inside the if block. That means that the first time you do $sth->execute inside the while block, it executes the correct query, but the second time around, $sth refers to your UPDATE query. This will lead to unexpected results. You could rename the $sth inside the if block to something else, or limit its scope with my or local.

    CU
    Robartes-

[reply]
[d/l]
Re: DBI multiple prepares
by princepawn (Parson) on Nov 04, 2002 at 15:51 UTC
    I think having multiple prepare statement is the loop are getting confused?
    Well, the whole point of preparing a statement is so that it can get precompiled and you can then simply execute against it with new values. It would be better to prepare your other statement outside the loop as well, using a second statement handle: 
    print "Selecting.... \n";
$sth=$dbh->prepare("SELECT name,address_number from many_address      
                    WHERE name=?
                    AND number=?
                    AND street=? ");
$sth_2=$dbh->prepare("UPDATE many_address
                        SET name=NULL,
                        date_loaded=NULL
                        WHERE name=?
                        AND number=?
                        AND street=? ");
        while ($_=<PA>) {
                ($name,$add,$street)=split(/\|/,$_);
                chomp ($name,$add,$street);
                $name = '' unless defined $name;
                $sth->execute($name,$add,$street);
                #$rec=$sth->dump_results(); ### see results
                @rec = $sth->fetchrow_array();
                if (@rec) { ### check to see if @res is valued
                    print "$name $add,$street --> @rec. \n";
                        $sth_2->execute($name,$add,$street);
                        print "Clearing $name,$add,$street -->@rec
\n";
                }
                else {
                print " Not available -->@rec is  \n";
                }

        }
close(PA);

    [download]
    I was thinking of collecting all the @rec values into another array - the looping through it later - outside them main loop. But can you push an array on to another array.
    You can: 
    push @array, @more_data;

    [download]
    But I think you would rather put array references like this: 
    push @array, \@more_data;

    [download]

    Also, be sure to ask your questions on the DBI mailing lists. A link to them is listed at DBI.perl.org which I happen to webmaster :).

[reply]
[d/l]
[select]

Back to Seekers of Perl Wisdom