mysql "LIMIT ?"

I created a quick-fix to get rid of the error about the quoted
LIMIT-value, but I assume this must have been solved in better ways. The
fix below is easy to enhance to also cover the "LIMIT ?,?" variant, but
I would rather see a cleaner approach. Does it exist already?

#!/usr/bin/perl
use strict;
use warnings;

use DBD::mysql;

### You can activate the code below, to get rid of
### the quoted LIMIT-value error.

# BEGIN {
# $DBD::mysql::st::__execute_org = \&DBD::mysql::st::execute;
# }
#
# { no warnings 'redefine';
#
# sub DBD::mysql::st::execute {
# for ($_[0]->{Database}{Statement}) {
# $_ = $1 . pop @_
# if m/(.* \s LIMIT \s+ ) \? \s* \z/msxi;
# }
# $DBD::mysql::st::__execute_org->(@_);
# }
# }

my $dbh = DBI->connect( qw/ dbi:mysql:MyDB user password / )
or die "Database connection failed";

my @href_rows = $dbh->selectall_hashref(
q/SELECT id, name
FROM MyTable
LIMIT ?/
, 'id'
, undef
, 42
) or die "\nError";

$dbh->disconnect();

--
Affijn, Ruud

"Gewoon is een tijger."

Re: mysql "LIMIT ?"

Binding the value using an explicit numeric type, like this:

use DBI qw(:sql_types);
....
$sth->bind_param(1, $limit, SQL_INTEGER);

should work. If it doesn't then it's a bug/limitation in DBD::mysql.

I'm sure patches would be welcome.

Tim.

On Tue, Feb 20, 2007 at 12:01:09AM +0100, Dr.Ruud wrote:
> I created a quick-fix to get rid of the error about the quoted
> LIMIT-value, but I assume this must have been solved in better ways. The
> fix below is easy to enhance to also cover the "LIMIT ?,?" variant, but
> I would rather see a cleaner approach. Does it exist already?
>
> #!/usr/bin/perl
> use strict;
> use warnings;
>
> use DBD::mysql;
>
> ### You can activate the code below, to get rid of
> ### the quoted LIMIT-value error.
>
> # BEGIN {
> # $DBD::mysql::st::__execute_org = \&DBD::mysql::st::execute;
> # }
> #
> # { no warnings 'redefine';
> #
> # sub DBD::mysql::st::execute {
> # for ($_[0]->{Database}{Statement}) {
> # $_ = $1 . pop @_
> # if m/(.* \s LIMIT \s+ ) \? \s* \z/msxi;
> # }
> # $DBD::mysql::st::__execute_org->(@_);
> # }
> # }
>
> my $dbh = DBI->connect( qw/ dbi:mysql:MyDB user password / )
> or die "Database connection failed";
>
> my @href_rows = $dbh->selectall_hashref(
> q/SELECT id, name
> FROM MyTable
> LIMIT ?/
> , 'id'
> , undef
> , 42
> ) or die "\nError";
>
> $dbh->disconnect();
>
> --
> Affijn, Ruud
>
> "Gewoon is een tijger."