>>>>> "MC" == Mimi Cafe <mimic...@googlemail.com> writes:
MC> I experienced a strange behaviour while using DBI binding for MySQL query
MC> with LIMIT clause. My CGI program behaved so strange and the result was
MC> always unpredictable and it took me several hours before I finally
detected
MC> the problem.
MC> # limit should be like "10, 20".
MC> my $limit = "$offset" . ', ' . "$number_rows";
why are you quoting scalar variables? that should be one simpler string:
my $limit = "$offset, $number_rows";
MC> my Sth = $dbh->prepare(qq{Select fname, lname, dob, substr(desc, 1, 200)
what is Sth? that isn't legal perl. please copy/paste real
code. obviously it should be $sth.
MC> from user left join personal_data on user.id = personal_data.id where
gender
MC> = ? and position = ? order by lname limit ?});
also you can format sql strings to be readable. do that. i like here
docs for long multiline strings:
my $sth = $dbh->prepare( <<SQL ) ;
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 ?
SQL
);
MC> $sth->execute($gender, $role, $limit);
MC> When above query is executed, I expected DBI to pass a value
MC> containing offset and number to rows to MySQL (2 values separated
MC> by a comma and space, I,e. 10, 20). This mean LIMIT should be like
MC> LIMIT 10, 20. Instead, BDI pass only one (the value for offset)
MC> and this made my program to not behaviour. Because I did not
MC> expect this behaviour from the DBI, I did not enable MYSQL query
MC> logging to help debug the problem. Rather, I rewrote my whole code
MC> from scratch and it did not help. After several hours I decided to
MC> enable MYSQL query logging and voila, I found the error.
try removing the space after the comma in your limit. the mysql docs show
SELECT * FROM tbl LIMIT 5,10
it makes sense as white space would end the argument list to
limit. otherwise how could the sql parser know the 10 was for limit when
it is optional.
uri
--
Uri Guttman ------ u...@stemsystems.com -------- http://www.sysarch.com --
----- Perl Code Review , Architecture, Development, Training, Support ------
--------- Gourmet Hot Cocoa Mix ---- http://bestfriendscocoa.com ---------
--
To unsubscribe, e-mail: beginners-unsubscr...@perl.org
For additional commands, e-mail: beginners-h...@perl.org
http://learn.perl.org/
