List:MySQL and Perl« Previous MessageNext Message »
From:Jochen Wiedmann Date:November 23 2001 4:03pm
Subject:Re: Bug report : placeholder after LIMIT
View as plain text  
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);


Thread
Bug report : placeholder after LIMITRaphael Hertzog23 Nov
  • Re: Bug report : placeholder after LIMITJochen Wiedmann23 Nov