[CDBI] CDBI::Sweet->page() not quite working with mysql?

Rhesa Rozendaal perl at rhesa.com
Fri Sep 23 02:29:30 BST 2005


Someone please tell me I'm not completely stupid here...

I'm trying paging with mysql:

   ($p, $i) = CD->page({}, { rows=>5, page=>1 });

and it dies on me with an error looking like:

   DBD::mysql::st bind_param failed: Binding non-numeric field 1, value
   undef as a numeric! [for Statement "SELECT me.id, me.title
   FROM   cd me
   WHERE  1=1  LIMIT ?, ?
   "] at /usr/lib/perl5/site_perl/5.8.5/Class/DBI.pm line 658.

I can get it to work by setting "disable_sql_paging", of course, but 
that's no fun.

Digging deeper, it turns out that it's a problem in DBD::mysql (I'm 
using 3.002 by the way): it passes the limit values as strings, and the 
database doesn't accept that:

   $db = DBI->connect('dbi:mysql:mysql','','');
   $st = $db->prepare('select user from user limit ?,?');
   $st->execute(3,5);

   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 ''3','5'' at line 1 at - line 3.

And this does work:

   $st = $db->prepare('select user from user limit ?,?');
   $st->bind_param(1, 3, SQL_INTEGER);
   $st->bind_param(2, 5, SQL_INTEGER);
   $st->execute;

Darn.

Okay, reading through the source of CDBI::Sweet, it looks like you guys 
have been down this road already, as I'm seeing references to this data 
type, and bind_param and stuff. Which suggests to me it actually should 
be working. But it doesn't :(

Where am I going wrong? By still using CDBI v0.96?

Rhesa




More information about the ClassDBI mailing list