I experienced a strange behaviour while using DBI binding for MySQL query
with LIMIT clause. My CGI program behaved so strange and the result was
always unpredictable and it took me several hours before I finally detected
the problem.
In my program I use binding as in the example below:
# limit should be like "10, 20".
my $limit = "$offset" . ', ' . "$number_rows";
my Sth = $dbh->prepare(qq{Select fname, lname, dob, substr(desc, 1, 200)
from user left join personal_data on user.id = personal_data.id where gender
= ? and position = ? order by lname limit ?});
$sth->execute($gender, $role, $limit);
When above query is executed, I expected DBI to pass a value containing
offset and number to rows to MySQL (2 values separated by a comma and space,
I,e. 10, 20). This mean LIMIT should be like LIMIT 10, 20. Instead, BDI pass
only one (the value for offset) and this made my program to not behaviour.
Because I did not expect this behaviour from the DBI, I did not enable MYSQL
query logging to help debug the problem. Rather, I rewrote my whole code
from scratch and it did not help. After several hours I decided to enable
MYSQL query logging and voila, I found the error.
Does anyone know why DBI behaves this way?
Mimi
