problem with $dbh->execute in a For loop

[Ravi]

Hi All,
I am trying to execute a select statement using the DBI module
of perl in a for loop. I am getting a strange behaviour, the select
statement is excuting correctly only for the last element in the for
loop.
I am including the portion of the code :

#Get the connection to the database
my $dbh = &getConnection( );
my @acodes;
my @bcodes = ('ADADADA', 'ADASDASDAS', 'BBBBBB', 'CCCCCCC');

#Create an SQL statement for getting the list of acodes
my $sql = $dbh->prepare("SELEC T DISTINCT Vial.ACODE from VIAL
WHERE VIAL.BCODE = ?");

my @row;
my $bcode;
$i = 0;

for($i=0; $i<@bcodes; $i++){

print "i :".$i."\n";
$sql->bind_param(1 , $bcodes[$i]);
$sql->execute();
$acodes[$i] = "";
while(@row = $sql->fetchrow_array ){
$acodes[$i] = $row[0];
}

}
#Close the connection to the databse
&closeConnectio n($dbh);

When I try to print the @acodes, it only has an entry for the
corresponding last element of the @bcode.
When I remove the for loop in the above code, and hardcode
the elements present in @bcodes it works fine.

It would be a great help, if anyone can let me know what I am doing
wrong.

Thanks in advance.
Ravi.

[David Frauzel]

Re: problem with $dbh-&gt;execute in a For loop

ravi_b_m@yahoo. com (Ravi) wrote in
news:117cce06.0 309221115.dd9e9 0a@posting.goog le.com:
[color=blue]
> for($i=0; $i<@bcodes; $i++){[/color]
....[color=blue]
> while(@row = $sql->fetchrow_array ){
> $acodes[$i] = $row[0];
> }
> }[/color]

Just a stab in the dark...

If my understanding of fetchrow_array hasn't lapsed, I believe the while
loop you have is going to fetch every single row until it reaches the last
row - so that every single $acodes[$i] is going to be populated with only
the last row fetched. (Unless you can assume you will always have only one
row to fetch - but that makes the while loop redundant and dangerous for
future expansion of the db.)

Does removing the while loop produce the desired effect?

[Ravi]

Re: problem with $dbh-&gt;execute in a For loop

David Frauzel <net.weatherson gATnemo> wrote in message news:<151cc663c d9395d78714e0a1 18a9addf@news.t eranews.com>...[color=blue]
> ravi_b_m@yahoo. com (Ravi) wrote in
> news:117cce06.0 309221115.dd9e9 0a@posting.goog le.com:
>[color=green]
> > for($i=0; $i<@bcodes; $i++){[/color]
> ...[color=green]
> > while(@row = $sql->fetchrow_array ){
> > $acodes[$i] = $row[0];
> > }
> > }[/color]
>
> Just a stab in the dark...
>
> If my understanding of fetchrow_array hasn't lapsed, I believe the while
> loop you have is going to fetch every single row until it reaches the last
> row - so that every single $acodes[$i] is going to be populated with only
> the last row fetched. (Unless you can assume you will always have only one
> row to fetch - but that makes the while loop redundant and dangerous for
> future expansion of the db.)
>
> Does removing the while loop produce the desired effect?[/color]

Yes the sql statement will always give me a single row, and no it
still does not work if I remove while loop.
I added a print statement inside the while loop to check see if it
is getting the result:
for($i=0; $i<@bcodes; $i++){

print "i :".$i."\n";
$sql->bind_param(1 , $bcodes[$i]);
$sql->execute();
$acodes[$i] = "";
while(@row = $sql->fetchrow_array ){
$acodes[$i] = $row[0];
print "result :".$row[0];
}
}

strange behaviour, only for the last element in the bcodes array it
goes into the while loop, for all others it does not. I am sure that
for all the elements in the bcode array there is an entry in the
database. If I move the first element to the last element of the bcode
array, now it enters the while loop for the last element (which was
the first element in the bcode array, before moving it to the last
position in the bcode array).

Is it possible that perl is not waiting for the fetchrow_array to
fetch data completely from the database ? Is there a workaround for it
?

Thanks in advance
Ravi.

[David Frauzel]

Re: problem with $dbh-&gt;execute in a For loop

ravi_b_m@yahoo. com (Ravi) wrote in
news:117cce06.0 309230649.11d58 b2e@posting.goo gle.com:
[color=blue]
> Yes the sql statement will always give me a single row, and no it
> still does not work if I remove while loop.[/color]

Like I said, then, there's no need for the while loop. It's redundant at
best, and a potential time bomb at worst. You may want to look up
selectrow_array instead of fetchrow_array, since you only need it for one
row, and selectrow_array reduces three statements into one. It allows for
param binding.
[color=blue]
> strange behaviour, only for the last element in the bcodes array it
> goes into the while loop, for all others it does not. I am sure that
> for all the elements in the bcode array there is an entry in the
> database. If I move the first element to the last element of the bcode
> array, now it enters the while loop for the last element (which was
> the first element in the bcode array, before moving it to the last
> position in the bcode array).[/color]

How about testing with a row-count to verify there really is an entry in
the db?

(Warning: back-of-the-napkin code...)

for(@bcodes){

$count = "SELECT COUNT(*) FORM VIAL WHERE VIAL.BCODE = $_";
print "$count -> " .
$dbh->selectrow_arra y($count) .
"\n";

push @acodes, $dbh->selectrow_arra y($sql, {}, $_);

}

Without the test code, you could reduce this to a one-liner:

#Get the connection to the database
my $dbh = &getConnection( );
my @acodes;
my @bcodes = ('ADADADA', 'ADASDASDAS', 'BBBBBB', 'CCCCCCC');

#Create an SQL statement for getting the list of acodes
my $sql = $dbh->prepare("SELEC T DISTINCT Vial.ACODE from VIAL
WHERE VIAL.BCODE = ?");

push @acodes, $dbh->selectrow_arra y($sql, {}, $_) for @bcodes;

#Close the connection to the databse
&closeConnectio n($dbh);

[Kris Wempa]

Re: problem with $dbh-&gt;execute in a For loop

> Like I said, then, there's no need for the while loop. It's redundant at[color=blue]
> best, and a potential time bomb at worst. You may want to look up
> selectrow_array instead of fetchrow_array, since you only need it for one
> row, and selectrow_array reduces three statements into one. It allows for
> param binding.
>[/color]

This is incorrect. Here is the description of fetchrow_array from CPAN:

----------------
fetchrow_array:
An alternative to fetchrow_arrayr ef. Fetches the next row of data and
returns it as a list containing the field values. Null fields are returned
as undef values in the list.

If there are no more rows or if an error occurs, then fetchrow_array returns
an empty list. You should check $sth->err afterwards (or use the RaiseError
attribute) to discover if the empty list returned was due to an error.

---------------

I've written a lot of Perl code to interface to mysql and the loop he is
using looks good. Each time fetchrow_array is called, it gets the NEXT row
in the result set and assigns each field to the corresponding array
elements: row[0], row[1], ....

[David Frauzel]

Re: problem with $dbh-&gt;execute in a For loop

"Kris Wempa" <calmincents(NO _SPAM)@yahoo.co m> wrote in
news:bksehd$ma9 6@kcweb01.netne ws.att.com:
[color=blue][color=green]
>> Like I said, then, there's no need for the while loop. It's redundant
>> at best, and a potential time bomb at worst. You may want to look up
>> selectrow_array instead of fetchrow_array, since you only need it for
>> one row, and selectrow_array reduces three statements into one. It
>> allows for param binding.
>>[/color]
>
> This is incorrect. Here is the description of fetchrow_array from
> CPAN:[/color]

I'm not sure which part of my statement was incorrect? Please let me
know, as I'm doing a lot of work with mysql myself, and I'd rather know
if some of my knowledge is faulty. I've read the CPAN.org doc you quoted
several times, if that makes a difference. :}
[color=blue]
> I've written a lot of Perl code to interface to mysql and the loop he
> is using looks good. Each time fetchrow_array is called, it gets the
> NEXT row in the result set and assigns each field to the corresponding
> array elements: row[0], row[1], ....[/color]

I mentioned that the while loop was redundant because Ravi specifically
stated he only "expects" one row to be returned. When this is the case,
you don't need a while loop, and using a "bare" fetchrow_array (or
selectrow_array ) works as needed: it fetches the "next" row, which is the
first row, and that's all you want, so that's all you need. This is how
my own mysql code has operated in dozens of places, so I have no reason
to believe otherwise.

I was recommending selectrow_array as an alternative to fetchrow_array,
because it combines the three statements into one, making three lines of
code into one. The only reason I've ever found to not use selectrow_array
is when you're working on a server with an older version of DBI, and your
only option is to use fetchrow_array. (After prepare and execute.)

I don't see anything wrong, *syntactically* with his code either (though
I presented a more idiomatic version, it is functionally equivalent), so
my guess is that it's a problem with the db, or the statement. Which is
why I suggested the COUNT(*) test. :}

[Kris Wempa]

Re: problem with $dbh-&gt;execute in a For loop

I no longer have the earlier threads so I'm not sure what I thought was
wrong in your comment. I also see your point about combining 3 statements
into one. I must have misunderstood what you were saying. I don't see how
the while loop is a potential "time bomb", however. As long as it evaluates
to a false condition when there are no more rows, there shouldn't be a
problem. Sorry about all the confusion.

"David Frauzel" <net.weatherson gATnemo> wrote in message
news:e040a1880c 961eec68b82e929 df20d32@news.te ranews.com...[color=blue]
> "Kris Wempa" <calmincents(NO _SPAM)@yahoo.co m> wrote in
> news:bksehd$ma9 6@kcweb01.netne ws.att.com:
>[color=green][color=darkred]
> >> Like I said, then, there's no need for the while loop. It's redundant
> >> at best, and a potential time bomb at worst. You may want to look up
> >> selectrow_array instead of fetchrow_array, since you only need it for
> >> one row, and selectrow_array reduces three statements into one. It
> >> allows for param binding.
> >>[/color]
> >
> > This is incorrect. Here is the description of fetchrow_array from
> > CPAN:[/color]
>
> I'm not sure which part of my statement was incorrect? Please let me
> know, as I'm doing a lot of work with mysql myself, and I'd rather know
> if some of my knowledge is faulty. I've read the CPAN.org doc you quoted
> several times, if that makes a difference. :}
>[color=green]
> > I've written a lot of Perl code to interface to mysql and the loop he
> > is using looks good. Each time fetchrow_array is called, it gets the
> > NEXT row in the result set and assigns each field to the corresponding
> > array elements: row[0], row[1], ....[/color]
>
> I mentioned that the while loop was redundant because Ravi specifically
> stated he only "expects" one row to be returned. When this is the case,
> you don't need a while loop, and using a "bare" fetchrow_array (or
> selectrow_array ) works as needed: it fetches the "next" row, which is the
> first row, and that's all you want, so that's all you need. This is how
> my own mysql code has operated in dozens of places, so I have no reason
> to believe otherwise.
>
> I was recommending selectrow_array as an alternative to fetchrow_array,
> because it combines the three statements into one, making three lines of
> code into one. The only reason I've ever found to not use selectrow_array
> is when you're working on a server with an older version of DBI, and your
> only option is to use fetchrow_array. (After prepare and execute.)
>
> I don't see anything wrong, *syntactically* with his code either (though
> I presented a more idiomatic version, it is functionally equivalent), so
> my guess is that it's a problem with the db, or the statement. Which is
> why I suggested the COUNT(*) test. :}[/color]