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

Out of curiousity, which database are you using?

Seeking Green geeks in Minnesota

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

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 );` [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
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.	[reply]
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]