[Melbourne-pm] Strange things about '0'
sgc294 at internode.on.net
Mon Oct 27 21:08:07 PDT 2008
Mathew Robertson wrote:
>> Now, if $query_var is a 0 then the ? in the above sql there are no
>> results returned from the query, but the Perl code doesn't break. The
>> Ingres db system must get passed a false value.
>> If I change $query_var to be '0' to try and get the right string into
>> the query in the $report->execute statement it doesn't work. Again
>> the Perl code doesn't break, but I still don't get any results.
>> As the I see the Camel book mentions, Perl equates 0 and '0' both to
>> be false. I think this is why there ar no results of both 0 and '0'.
> Its not Perl's fault -> it (aka DBI) will be passing the literal value
> to the database - the database should automatically quote numeric
> values. What you can do is try a simple SQL statement directly to
> your db, eg:
> SELECT * FROM some_table WHERE some_column = 0;
> If this doesn't fail with a bad-statement error, then your db install
> (like most databases), automatically quotes numeric values.
Yep, I knew it wouldn't be Perl's fault. But I wasn't sure if it was my
own or not. :)
The above SQL does fail with an error. It is on an old Ingres 2.6
installation. The later versions might handle numeric character to
integer conversion better.
* select * from valid_disp where disp = 0 \g
Executing . . .
E_US1130 illegal character to integer conversion or non-numeric character
in money field.
(Tue Oct 28 11:55:48 2008)
>> The workaround is to use the ---> if ($query_var... <--- block to
>> modify the SQL code in $query and replace the ? with the string '0'.
>> This is fine at the moment, but if in the future I have SQL code in
>> $query that has more than one ? and I want only one of them to be '0'
>> I'll be in trouble.
> This approach is dubious... you should be using the bind_param()
> feature of DBI, which internally uses the corresponding database's
> specific implementation or the generic DBI implementation. You
> shouldn't be doing your own placeholder transpositions (unless you are
> a uber geek).
Oh, I suspected it was dubious. :) but I couldn't find another way to
make it work. I'll give the bind_param() feature a try.
> I'd suggest not passing in the 'array_hash' argument (or any other
> variation). Either:
> - use the returned $sth by calling the appropriate method.
> - try using a contextual return as shown here.
Thanks. That code and those ideas look much better.
More information about the Melbourne-pm