Re: MySQL LIMIT statement
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)
Re: MySQL LIMIT statement
Hi Paul, Thanks for your input. My problem is that I have a large code-base already installed that uses the $sth-execute(@args) way of parsing in placeholder values. This used to work fine. To go through and change all of these methods to do a $sth-bind_param(n, $val, SQL_INTEGER) is a lot of work! I suppose there are really a couple of reasons for me posting:- 1) The $sth-execute(@args) way of running statements is *very* nice, and it's a shame that this has been broken (hint to DBI developers :). 2) Does anyone know if there's a way of getting the DBI to revert to it's old behaviour of not automatically quoting placeholder-values? 3) Is this a feature that's likely to remain with us permanently - or do the DBI people think that this is a bug to be fixed??? Thank you for your reply. I'm sorry I was not clearer in my original post. Dan On Tue, 2003-07-29 at 17:56, Paul DuBois wrote: 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)
Re: MySQL LIMIT statement
On 29 Jul 2003 19:08:23 +0100 Dan Rowles [EMAIL PROTECTED] wrote: My problem is that I have a large code-base already installed that uses the $sth-execute(@args) way of parsing in placeholder values. This used to work fine. To go through and change all of these methods to do a $sth-bind_param(n, $val, SQL_INTEGER) is a lot of work! You don't have to do it for all statements or for all parameters even in the statements where it is necessary. Add it after the prepare() call for just the placeholders that need the hint. -- Mac :}) ** I usually forward private questions to the appropriate mail list. ** Ask Smarter: http://www.catb.org/~esr/faqs/smart-questions.html Give a hobbit a fish and he eats fish for a day. Give a hobbit a ring and he eats fish for an age.
Re: MySQL LIMIT statement
On 29 Jul 2003, Dan Rowles wrote:
My problem is that I have a large code-base already installed that uses
the $sth-execute(@args) way of parsing in placeholder values. This
used to work fine. To go through and change all of these methods to do a
$sth-bind_param(n, $val, SQL_INTEGER) is a lot of work!
That might have worked fine but if you had something like:
$args[3] = 'test';
.
.
.
for (1..9) {
print HI if $args[$_] 1;
}
$sth-execute(@args);
It would fail because DBD::mysql would not quote any of your arguemnts
because they were used in numeric context.
I suppose there are really a couple of reasons for me posting:-
1) The $sth-execute(@args) way of running statements is *very* nice,
and it's a shame that this has been broken (hint to DBI developers :).
The guessing was broken before, so now it quotes on the safe side
defaulting to quoting as a varchar() see the ChangeLog (btw, I just
noticed a typo in there -- bind_param(colum_id, SQL_INTEGER) should be
bind_param(column_id, undef, SQL_INTEGER).
2) Does anyone know if there's a way of getting the DBI to revert to
it's old behaviour of not automatically quoting placeholder-values?
Nope.
Rudy
Re: MySQL LIMIT statement
On 29 Jul 2003 19:08:23 +0100, Dan Rowles wrote: To go through and change all of these methods to do a $sth-bind_param(n, $val, SQL_INTEGER) is a lot of work! Er, you don't have to. It's the first occurrence of using a value for some placeholder that defines how it'll be used further on. So bind a parameter to a type first, and you can go on using the rest of the code as before. Even $sth-bind_param(3, undef, SQL_INTEGER); will do -- which is a NULL, but a field of type integer. -- Bart.
Re: MySQL LIMIT statement
On 29 Jul 2003 19:08:23 +0100, Dan Rowles wrote: 2) Does anyone know if there's a way of getting the DBI to revert to it's old behaviour of not automatically quoting placeholder-values? So MySQL cares now, does it? I'm still on MySQL 3.x, and I know that there you may quote any type of field, so 123 for an inetegr is alright. Good to know if ever we upgrade. Ouch. -- Bart.
Re: MySQL LIMIT statement
On 29 Jul 2003 at 22:44, Bart Lateur wrote: So MySQL cares now, does it? I'm still on MySQL 3.x, and I know that there you may quote any type of field, so 123 for an inetegr is alright. The issue is specific to the values after LIMIT. Strings in most other places are converted to integers automatically as required in MySQL 4 just as they were in 3. Have you tried quoting LIMIT values in 3.23.x? -- Keith C. Ivey [EMAIL PROTECTED] Tobacco Documents Online http://tobaccodocuments.org
