[guest - Wed Jul 23 13:28:09 2003]:
I've tested this with SuSE 9.0, MySQL 4.1.1 and Redhat 7.2, MySQL
3.23.49. With MySQL 4.1.1, it fails using both versions of the DBD
(2.9003 and 2.1028), but passes on MySQL 3.23.49 using both versions of
the DBD.
It fails on 4.1.1 because the "LIMIT" is passed to MySQL via the DBD as:
$sth->prepare("SELECT * FROM foo LIMIT ?, ?");
my ($lower, $upper) = (0,10);
$sth->execute($lower, $upper);
Ends up exectuting in the server as:
"SELECT * FROM foo LIMIT '0','10'"
Which causes an SQL error.
Upon further inspection, the same happens with any value, whether or not
it's an int or varchar
(id being an 'int')
$sth->prepare("SELECT * FROM foo LIMIT WHERE id = ?");
my $id = 1;
$sth->execute($id);
Ends up exectuting in the server as:
"SELECT * FROM foo LIMIT WHERE id = '1'"
This doesn't cause an error becuase MySQL allows INTs to be quoted.
When I test this with MySQL 3.23.49, the resulting SQL is properly
quoted or unquoted depending upon data type.
I suspect this is a change in the C API, which I am looking into. The
one thing the DBD should do internally is determine the datatype of each
column that each placeholder references, then set the appropriate type
for the column that each member of the MYSQL_BIND structure prior to C
API call mysql_bind_param(), and then when mysql_execute() is called,
the resulting SQL will have the proper quoting (quotes or absence of
quotes) for each parameter passed.
Show quoted text> mysql 3.23 / dbd::mysql 2.9002 / linux x86
>
> statement : SELECT Prelude_CreateTime.parent_ident FROM
> Prelude_CreateTime WHERE Prelude_CreateTime.parent_type='A' AND
> (((Prelude_CreateTime.time>='2003-07-22 00:00:00'))) GROUP BY
> Prelude_CreateTime.parent_ident ORDER BY
> Prelude_CreateTime.parent_ident DESC LIMIT ?,?;
>
> so, 2 placeholders, the 2 LIMIT parameters
>
> was using for example xxx->execute(0,30);
>
> it did :
>
> You have an error in your SQL syntax near ''0','30'' at line 1
>
> looking at changelog, i tried to replace this execute(x,y) by
> bind_param(1,x) and bind_param(2,y) => same bug
