set_sql, MySQL, Class::DBI and using a SQL LIMIT

steve_sims has asked for the wisdom of the Perl Monks concerning the following question:

Hi gang. I am running into a problem with using Class::DBI's set_sql with a MySQL database. I have a query where I want to give a parameter to limit the number of records I want to receive. A simplified version of my query is as follows:

Foo::Bar->set_sql(withlimit => q{
   select * from table
   where field = ?
   limit ?
});
[download]

The problem with this is when I try to call the query using search_withlimit(20,10) I get an error telling me there's a problem with my SQL syntax. It seems that the parameter for limit is being filled in with quote marks, and MySQL doesn't like that. The above example ends up getting turned into:

select * from table
where field = '20'
limit '10'
[download]

I can't work out how to persuade the limit parameter to be filled in without the quotes. Any ideas?

Comment on set_sql, MySQL, Class::DBI and using a SQL LIMIT Select or Download Code

Replies are listed 'Best First'.
Re: set_sql, MySQL, Class::DBI and using a SQL LIMIT by dragonchild (Archbishop) on May 04, 2005 at 14:34 UTC
This is a known bug in DBD::mysql and has nothing to do with Class::DBI. Check out this conversation for more info and a possible solution. In general, if you think something isn't in Perl, try it out, because it usually is. :-) "What is the sound of Perl? Is it not the sound of a wall that people have stopped banging their heads against?"	[reply]
Re^2: set_sql, MySQL, Class::DBI and using a SQL LIMIT by steve_sims (Initiate) on May 04, 2005 at 16:04 UTC
Thanks for the quick response. The discussion you pointed to was indeed very informative. What I ended up doing was dynamically creating my set_sql string, thus when I call my search_withlimit call I don't need to pass it any parameters, so they can't get filled in incorrectly.	[reply]
Re: set_sql, MySQL, Class::DBI and using a SQL LIMIT by trammell (Priest) on May 04, 2005 at 16:12 UTC
I'm able to interpolate the LIMIT parameter if I explicitly set its type to integer: `use DBI ':sql_types'; ... my $sql = q[ SELECT * FROM test.t1 LIMIT ? ]; my $sth = $dbh->prepare($sql); $sth->bind_param(1, 10, SQL_INTEGER); # limit=10 $sth->execute() \|\| die $sth->errstr; while (my @row = $sth->fetchrow_array) { print "row: @row\n"; }` [download]	[reply] [d/l]