Beefy Boxes and Bandwidth Generously Provided by pair Networks
"be consistent"

DBI (incorrectly) bound variables

by Xaositect (Friar)
on Jan 31, 2003 at 19:36 UTC ( #231691=perlquestion: print w/replies, xml ) Need Help??
Xaositect has asked for the wisdom of the Perl Monks concerning the following question:

I'm hoping someone will just recognize this and know what the problem is, because if not it means a lot more work on my part to figure it out. I've run into a problem where it appears DBI is incorrectly binding a number as a string in the limit on a query.

I'm using DBI and CGI::Application in a website, part of which is a catalog / shopping cart / purchase system. Products in the catalog are broken into pages of 20 items each. This breaking of pages is done by encoding the number of prizes into a link, and limiting my select based on that number. So I have a link like:

<A href="catalog.cgi?startitem=21">Next</A>

where the startitem is set dynamicly based on the current page. (there are other params passed here too, but anyway) The actual query looks something like:

$sth = $self->param('dbh_shopping')->prepare(qq{ SELECT ... a somewhat large query ... WHERE catalogId = ? LIMIT ?, ? }); my $start_item = ($self->query->param('startitem') > 0) ? $self->query->param('startitem') - 1 : 0;
So what I want to do after that is:
$sth->execute( $self->param('catalog_id'), $start_item, 20 );

However, DBI binds $start_item as a string, and quotes it. This causes an error, as limit doesn't take a string. Here's where it gets bizzare. I tried playing with $start_item in all sorts of ways, trying to figure out why DBI was turning it into a string. Some examples of things that didn't work: (DBI still quoted the param)

$start_item + 0 int($start_item) my $start_item2 = $start_item;

As other tests, I tried putting a hardcoded number in the call to $sth->execute(), and it worked. I tried assigning the $start_item in the second part of the limit (third bound param instead of second) and DBI quoted it. So far, the only way I've been able to get it working is the insane:

my $this_stupid_counter = 0; for (my $x=0; $x<$self->query->param('startitem'); $x++) { $this_stupid_counter++; } $sth->execute( $self->param('catalog_id'), $this_stupid_counter, 20 );

which should absolutely not be necessary. Any ideas?

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

    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

      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?

        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

Re: DBI (incorrectly) bound variables
by perrin (Chancellor) on Jan 31, 2003 at 20:47 UTC
    If you use DBI's tracing features, it may tell you what's wrong.

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://231691]
Approved by Nitrox
Front-paged by diotalevi
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others musing on the Monastery: (4)
As of 2019-02-16 23:36 GMT
Find Nodes?
    Voting Booth?
    I use postfix dereferencing ...

    Results (95 votes). Check out past polls.