At 16:20 +0100 7/29/03, Dan Rowles wrote:
Hi there,
I'm using MySQL 4.0.13-standard, DBD-mysql 2.9002, DBI 1.37, perl 5.6.1
AND perl 5.8.0.
The attached program causes a crash, with the following error statement
in both version sof perl. It used to work fine using MySQL 3.23.x, and
older versions of the DBI.
bash-2.05a$ ./database.pl 1 3
Offset num: 1 +OK
Limit num: 1 +OK
DBD::mysql::st execute failed: You have an error in your SQL syntax.
Check the manual that corresponds to your MySQL server version for the
right syntax to use near ''1', '3'' at line 1 [for statement ``SELECT
email FROM users ORDER BY email LIMIT ?, ?'']) at ./database.pl line 25.
Can't list users: You have an error in your SQL syntax. Check the
manual that corresponds to your MySQL server version for the right
syntax to use near ''1', '3'' at line 1 at ./database.pl line 28.
LIMIT values must be integer constants. The placeholder binding
is probably adding quotes, so you'll need to supply a DBI SQL_INTEGER
type when you bind. See "Data Types for Placeholders" in the DBI docs,
or p124 of the Cheetah book.
The error message suggests to me that the numeric values are being
passed in as strings to the database. A quick test of MySQL confirms
that passing in the limit parameters as strings does indeed cause the
same error message (e-mail addresses replaced):-
mysql> SELECT email FROM users ORDER BY email LIMIT 0,10;
+--------------+
| email |
+--------------+
| [EMAIL PROTECTED] |
| [EMAIL PROTECTED] |
| [EMAIL PROTECTED] |
+--------------+
3 rows in set (0.16 sec)
mysql> SELECT email FROM users ORDER BY email LIMIT "0","10";
ERROR 1064: You have an error in your SQL syntax. Check the manual that
corresponds to your MySQL server version for the right syntax to use
near '"0","10"' at line 1
I've been trawling through the mailling lists, and I've found this post,
which relates to a problem with the Postgres DBI driver. It sounds to me
like this has caused the problem:-
http://archive.develooper.com/[EMAIL PROTECTED]/msg02313.html
Can anyone comment on this? Has anyone else had a similar problem?
Thanks,
Dan
Attachment converted: ice3:database.pl (TEXT/R*ch) (0001DF53)
