DBI (incorrectly) bound variables

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>
[download]

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;
[download]

So what I want to do after that is:

  $sth->execute(
    $self->param('catalog_id'),
    $start_item,
    20
  );
[download]

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;
[download]

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
  );
[download]

which should absolutely not be necessary. Any ideas?

Comment on DBI (incorrectly) bound variables Select or Download Code

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;` [download] Out of curiousity, which database are you using? Seeking Green geeks in Minnesota	[reply] [d/l] [select]
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 );` [download] 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();` [download] 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?	[reply] [d/l] [select]
Re^3: DBI (incorrectly) bound variables by diotalevi (Canon) on Jan 31, 2003 at 22:05 UTC
Here are some good references DBI: when 1 != '1' and DBD::mysql Unusual Behavior which discuss the problem. Seeking Green geeks in Minnesota	[reply]
Re: Re^3: DBI (incorrectly) bound variables by Xaositect (Friar) on Feb 03, 2003 at 17:17 UTC
Re^3: DBI (incorrectly) bound variables by diotalevi (Canon) on Jan 31, 2003 at 20:55 UTC
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	[reply]
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.	[reply]


The stupid question is the question not asked
	PerlMonks