Beefy Boxes and Bandwidth Generously Provided by pair Networks
Perl Monk, Perl Meditation
 
PerlMonks  

Re: DBI (incorrectly) bound variables

by diotalevi (Canon)
on Jan 31, 2003 at 20:14 UTC ( #231703=note: print w/replies, xml ) Need Help??


in reply to DBI (incorrectly) bound variables

This is an unusual problem. It looks like you're being hit by perl's ambiguous data typing. I'd attack this one of three ways. First explicitly set your variable's type, second specify the variable type when binding it, third figure out why your database is so lame-brained.

Here's what the first two look like. If I wanted to ensure your catalog_id value was a number I'd write it as  0 + $self -> param('catalog_id'). If I wanted to affect DBI's binding then you use the bind_param function something like this. Refer to your DBI documentation in the "Data Types for Placeholders" section.

# Alter the data type of the value as it's being passed $sth -> execute( 0 + $self -> param('catalog_id'), $start_item, 20 ); # Specify the data type explicitly $sth -> bind_param( 1, $self->param('catalog_id'), SQL_INTEGER); $sth -> bind_param( 2, $start_item ); $sth -> bind_param( 3, 20 ); $sth -> execute;

Out of curiousity, which database are you using?


Seeking Green geeks in Minnesota

Replies are listed 'Best First'.
Re: Re: DBI (incorrectly) bound variables
by Xaositect (Friar) on Jan 31, 2003 at 20:50 UTC

    Well, the variable being bound incorrectly is the second ($start_item) not the first ($self->param('catalog_id')). However, the ideas hold true. I did try:

    $sth -> execute( $self -> param('catalog_id'), 0 + $start_item, 20 );

    without success. Also $start_item + 0 and int($start_item). I just tried the specific type binding you recommended, and that did work:

    $sth->bind_param(1, $self->param('catalog_id'), SQL_INTEGER); $sth->bind_param(2, $start_item, SQL_INTEGER); $sth->bind_param(3, 20, SQL_INTEGER); $sth->execute();

    This leads me to believe I am experiencing a bug in DBI. (My database is MySQL.) However, this still begs the question, how is DBI evaluating the result of int($start_item) differently than the $this_stupid_counter above?

        Great stuff, thanks. I do intend to look into this in more detail in a week or two, unfortunately I'm a little under the gun to get this done right now, and the specific parameter binding works fine until then.

      I've seen this discussed regarding MySQL. It's just not tolerant of perl's data typing the way other databases are. If you want a better test then involve Devel::Peek and Dump() the values. Let me know what output you come up with, I can imagine I'll eventually have the misfortune of using MySQL as well and it'll be useful for to know then. My sympathies.


      Seeking Green geeks in Minnesota

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://231703]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others meditating upon the Monastery: (5)
As of 2019-10-16 16:45 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?
    Notices?