[Catalyst] Class::DBI::Sweet LIMIT clause with placeholders

[Christian Hansen]

On 2005-06-07, at 23.29, Perrin Harkins wrote:

> FYI, Tim Bunce seems to think that the LIMIT clause with placeholders
> that Class::DBI::Sweet uses will no longer work with recent DBD::mysql
> releases.  Is anyone else experiencing this?

I think you copied to little code from Class::DBI::Sweet.

> On Tue, Jun 07, 2005 at 04:15:16PM -0400, Perrin Harkins wrote:
>> I'm having problems specifying data types.  I'm adding LIMIT clauses 
>> for
>> some queries, using MySQL-specific syntax, and DBI needs to be told 
>> that
>> these are numeric.  I use the following call in my Class::DBI base
>> class:
>>
>> __PACKAGE__->data_type(
>>     '__LIMIT'  => DBI::SQL_INTEGER(),
>>     '__OFFSET' => DBI::SQL_INTEGER(),
>> );
>>
>> When I send in a query with a __LIMIT column and value, I get this
>> error:
>>
>> DBD::mysql::st bind_param failed: Binding non-numeric field 1, value
>> undef as a numeric! [for Statement "SELECT person_id
>> FROM   person
>> WHERE  1=1  ORDER BY first_name LIMIT ?, ?
>> "] at /home/perrin/projects/arcos/lib/Class/DBI.pm line 658.
>>
>> And line 658 is the $sth->bind_param call here:
>>
>> sub _bind_param {
>> 	my ($class, $sth, $keys) = @_;
>> 	my $datatype = $class->__data_type or return;
>> 	for my $i (0 .. $#$keys) {
>> 		if (my $type = $datatype->{ $keys->[$i] }) {
>> 			$sth->bind_param($i + 1, undef, $type);
>> 		}
>> 	}
>> }
>>
>> It does indeed call it with undef, but this same technique is used in
>> Class::DBI::Sweet and seems to work.  Does anyone have a suggestion?
>> I'm using MySQL 4.1.12, DBI 1.48, and DBD-mysql 2.9007.

Use Class::DBI::Sweet ;)

-- 

Christian Hansen
nGmedia
+46 40 660 17 50