MySQL LIMIT statement

[Dan Rowles]

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.





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

#! /usr/bin/perl -w

use strict;

use DBI;

unless(@ARGV == 2) {
    die "Usage: offset limit\n";
}

my $offset = int($ARGV[0]) + 0;
my $limit  = int($ARGV[1]) + 0;

print "Offset num: ", DBI::looks_like_number($offset), " +OK\n";
print "Limit num:  ", DBI::looks_like_number($limit), " +OK\n";

my $dbh = DBI->connect("DBI:mysql:test", "test", "test", 
		       { PrintError => 1, ShowErrorStatement => 1});

my $sth = $dbh->prepare("SELECT email FROM users " . 
			"ORDER BY email LIMIT ?, ?");
$sth->execute($offset, $limit);

if($sth->errstr) {
    die "Can't list users: ", $sth->errstr;
}

while(my $tmp = $sth->fetchrow_arrayref) {
    print "Email:", $tmp->[0], " +OK\n";
}

print "DONE\n";

exit;