473,562 Members | 2,956 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

problem with $dbh->execute in a For loop

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.
Jul 19 '05 #1
6 4784
ra******@yahoo. com (Ravi) wrote in
news:11******** *************** **@posting.goog le.com:
for($i=0; $i<@bcodes; $i++){ .... while(@row = $sql->fetchrow_array ){
$acodes[$i] = $row[0];
}
}


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?
Jul 19 '05 #2
David Frauzel <net.weatherson gATnemo> wrote in message news:<15******* *************** ********@news.t eranews.com>...
ra******@yahoo. com (Ravi) wrote in
news:11******** *************** **@posting.goog le.com:
for($i=0; $i<@bcodes; $i++){

...
while(@row = $sql->fetchrow_array ){
$acodes[$i] = $row[0];
}
}


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?


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.
Jul 19 '05 #3
ra******@yahoo. com (Ravi) wrote in
news:11******** *************** ***@posting.goo gle.com:
Yes the sql statement will always give me a single row, and no it
still does not work if I remove while loop.
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.
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).


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);
Jul 19 '05 #4
> 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.


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], ....


Jul 19 '05 #5
"Kris Wempa" <calmincents(NO _SPAM)@yahoo.co m> wrote in
news:bk******** *@kcweb01.netne ws.att.com:
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.

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


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. :}
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], ....


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. :}
Jul 19 '05 #6
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:e0******** *************** *******@news.te ranews.com...
"Kris Wempa" <calmincents(NO _SPAM)@yahoo.co m> wrote in
news:bk******** *@kcweb01.netne ws.att.com:
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.


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


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. :}
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], ....


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. :}

Jul 19 '05 #7

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
864
by: Tim::.. | last post by:
Hi I want to convert this piece of asp code into aspx but don't know how to start I am also having a little problem with the following loop, I want to return all the values pulled from the xml file into the response.write in the html but am not sure what type of loop I should use to get this to work... Please help ...:: CODE ::. < 'Load...
2
1583
by: David | last post by:
I am wanting to step through a series of commands to send via the serial port to a target device. The program sets a 100mS ReplyTimer. The timer routine sets the ProcessedRX upon timeout. If data arrives, the timer is reset for another 50mS. This continues until there are no more characters within 50ms . The problem is that the program...
12
1455
by: usa-99 | last post by:
Hi there I have following function which is called on load of page. function checkFieldContent(form) { var field; for(i = 0; i < form.elements.length; i++) { field = form.elements; if (field.type == 'text') { alert(field.name); // checkSearchInput(field);
8
5279
by: Kappadon5 | last post by:
Hello All, I am new to programming and I apologize in advance if I am out of protocol in any way shape or fashion. My problem is that I have a program where you select an option from two different list boxes which will in turn populate a third list box with a numeric number when you click a button. Then I have a calculate button that is supposed...
7
1438
by: Kim | last post by:
Hi, why doesn't this work inside a loop, gives me the wrong "kontrollnummer" (increases it as the loop goes on) <% Dim kontrollnummer Dim raknare Dim Summa Dim referens referens = (rs_faktura.Fields.Item("customer_ID").Value) & (rs_faktura.Fields.Item("bill_ID").Value)
18
2081
by: Vijaykumar Dave | last post by:
I have a program for base X power N as under. The problem is that when the range specified in loop is given it works well, but when any character is pressed, it goes to infinite loop. Second problem is it works fine for smaller value of X or N but not for higher say base 15 power 20. How can I get true value displayed with such higher base...
4
1896
by: Vijaykumar Dave | last post by:
Here is a program of calculator. I have a proble in loop. When key 1-8 is pressed, it works ok but if any character key is pressed, it goes to infinite loop. will any one help me to correct my program? Vijaykumar Dave -------------------------------------------------------------------------------------------- #include<stdio.h>
16
1987
madhoriya22
by: madhoriya22 | last post by:
I am putting this code segment here.......Problem is, This loop is running only for i = 0, I dont know what is the problem.....Thanks in advance... for(int i = 0; i < v.size(); i++) { while(itr.hasNext()) { Map.Entry me = (Map.Entry)itr.next(); System.out.println("i::"+i); ...
2
1514
daniel aristidou
by: daniel aristidou | last post by:
Hi i wrote code to print records off a datagrid.the code works on all but one of my data grids. The problem is that loop continues without stopping, Causing the program to crash. The only diff between all of the grids is the query and the widows form it is on. I basicly just copied the form changing the SQL for the datasource. any way so here...
16
2173
by: littlegreen | last post by:
Hi all, I would like to know how to assign and get the form value when I using it in a loop. I cannot get all the input that I inserted in 'Desc' part and it is from this site. May I know to get this work when I do a loop? Thanks. a.php // this can work <font size="2">Title:</font><br><input type="text" name="Title" size="40" value="<?php...
0
7577
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
0
8101
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
1
7627
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
0
7935
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
1
5477
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
0
5193
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
1
2073
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
1
1191
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
0
903
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.