[Melbourne-pm] Strange things about '0'

[Andrew Dent]

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.

Cheers

Andrew Dent