Re: DBD::ODBC fetch is returning string for integer

On 19/05/2012 13:33, [email protected] wrote:
>> DBD::ODBC is returning strings for integers.  This results in incorrect values
>> for  bit wise operators.  (for ex:- $e='16'; $f = '32'  print $e&  $f returns
>> 12 instead of zero ). Is there a setting that can help us return integers as
>> 'integers'.
> It's safer (and easier) to make your perl code independent of the returned
> data type:
>
>   $e='16';
>   $f='32';
>   print $e&$f; # prints '12'
>   $e += 0; $f += 0;
>   print $e&$f; # prints '0'
>
>
> --      Peter Vanroose.
>
It is but that can be a really annoying thing to do it you have lots of 
columns you want as integers. The first case where I came across this 
where it was a substantial annoyance was using JSON::XS to convert data 
I'd retrieved from the database to JSON. The columns which are numbers 
are converted to "NNN" in the JSON output but I really wanted NNN (for 
reduced size and because some other languages parsing the JSON 
differentiate between "NNN" and NNN. Adding 0 to all of them was a PITA 
(think selectall_arrayref and then having to iterate over the entire 
result-set adding 0 to each integer field). It turns out JSON::XS 
attempts to spot numbers by looking to see if the pv in the scalar is set.

As a result and with Tim's help we added the DiscardString attribute to 
SQLBindCol and when combined with a TYPE (SQL_INTEGER, SQL_DECIMAL, 
SQL_NUMERIC) and a driver which supports it (DBD::ODBC and DBD::Oracle 
definitely) it will use sql_type_cast_svpv to cast the value to the 
right type and the pv is lost.

I added this support to DBD::ODBC but because rebinding a column in Perl 
on an existing statement does not actually end up calling SQLBindCol 
again it masked a problem in my test code which made it look like 
DiscardString was working when in fact it was not.

Strictly speaking DiscardString and sql_type_cast_svpv is not needed in 
DBD::ODBC when you specify the type as SQL_INTEGER as it should simply 
sv_setiv (which it did not) but it is still required for 
SQL_DECIMAL/SQL_NUMERIC.

I am working on a fix right now.

Martin