Raphael Hertzog wrote:
> Extract :
> | I am getting erratic behaviour when I use a placeholder as an argument to
> | LIMIT. For instance, if I do
> |
> | $sth = $dbh->prepare("SELECT foo, bar FROM baz LIMIT ?, ?");
> | $sth->execute($start, $length) or die $dbh->errstr;
> |
> | it works most of the time. However, sometimes it gets confused about the
> | encoding of the parameters, and I get an error message complaining
> | about a syntax error "near '20', 10", for instance. It looks like
> | the $sth got confused as to the arg type while trying to bind it.
First of all, you should not use placeholders for the LIMIT
option. While this may work for the Msql-Mysql-modules, it
will definitely not work for most other drivers and/or
databases. In other words: You loose portability.
Second, the problem is not a bug, but a missing feature in
MySQL itself. The database does not yet support placeholders.
In other words: The driver has no possibility to guess what
data type is expected at this place. It attempts to guess,
but it may be wrong, for example the following will fail:
$start = "10"; # String looking like a decimal number,
$sth->execute($start);
$start = $cgi->param("startparam");
$sth->execute($start);
The solution is that you either convert the parameter or you
explicitly tell the driver that you want a number:
$start = int("10"); # String being converted to a number
$sth->execute($start);
$sth->bind_param(1, $start, DBI::SQL_INTEGER);
$sth->execute($start);
