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

[prev] [thread] [next] [Date index for 2005/09/23]

I did try adding your warnings, and using DBI_TRACE=2. Lots of data I 
won't actually have to bother you with; read on...

Matt S Trout wrote:
> On Fri, Sep 23, 2005 at 12:24:54PM +0200, Rhesa Rozendaal wrote:
> 
> sub _bind_param { # this bit's in Class::DBI
>         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);

Hey look, there's an undef there.
I posted this snippet before:

    use DBI qw/:sql_types/;
    $db = DBI->connect('dbi:mysql:mysql','','');
    $st = $db->prepare('select user from user limit ?,?');
    $st->bind_param(1, 3, SQL_INTEGER);
    $st->bind_param(2, 5, SQL_INTEGER);
    $st->execute;

That works; As you can see, I'm passing the actual values into bind_param.

Now, let's try it the way it's done in CDBI:

    use DBI qw/:sql_types/;
    $db = DBI->connect('dbi:mysql:mysql','','', {RaiseError => 1} );
    $st = $db->prepare('select user from user limit ?,?');
    $st->bind_param(1, undef, SQL_INTEGER);
    $st->bind_param(2, undef, SQL_INTEGER);
    $st->execute(3,5);

This gives me the same errors:

DBD::mysql::st bind_param failed: Binding non-numeric field 1, value 
undef as a numeric! at - line 4.
DBD::mysql::st bind_param failed: Binding non-numeric field 2, value 
undef as a numeric! at - line 5.

And it dies on it because of RaiseErrors => 1. If I turn that off, it 
simply warns and proceeds with giving me the results I asked for.

So, there are two ways out of this:
1. patch CDBI to pass the actual value into bind_param (not likely!)
2. turn off RaiseError and PrintError around it (not likely either)
3. Forget about it and use the workaround of disable_sql_paging

I think I'm going to go with 3. I _could_ override _bind_param (in fact, 
I've tried and that works for the limit bits), but that may have 
unforseen consequences. And then there's the maintenance issues with it, 
since I'd likely have to deal with multiple installations and applications.

If anyone is still following this, here's my override:

sub _bind_param { # this "fixes" limit params in mysql 
 

     my ($class, $sth, $keys) = @_; 
 

     local $sth->{RaiseError}; 
 

     local $sth->{PrintError}; 
 

     return $class->SUPER::_bind_param($sth, $keys); 
 

} 
 


All in all, I feel this is somewhat of an issue with dbd::mysql. It does 
support binding limit params, but then it doesn't really want to do it. 
In any case, Sweet is innocent :)

> Thanks for the page and object caching stuff should be directed to
> Christian Hansen, the original author of Sweet. My only contribution was
> to destroy the order and beauty of his code in the process of hacking join
> support into it :)
> 

I like the page() method, but your joins are the real sugar :)
Thanks for bearing with me sofar :)

Rhesa

_______________________________________________
ClassDBI mailing list
ClassDBI@xxxxx.xxxxxxxxxxxxxxxx.xxx
http://lists.digitalcraftsmen.net/mailman/listinfo/classdbi