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

On 19/05/2012 11:05, Martin J. Evans wrote:
> Tim, if you see this could you take a quick look at my comment below 
> re sql_type_case - thanks.
>
> On 18/05/2012 19:38, Manikantan, Madhunapanthula_Naaga wrote:
>> Forwarding this to DBI dev as well. Any help is very much appreciated !!
>>
>> Thanks
>>
>> _____________________________________________
>> From: Manikantan, Madhunapanthula_Naaga
>> Sent: Friday, May 18, 2012 7:11 PM
>> To: [email protected]
>> Cc: [email protected]
>> Subject: DBD::ODBC fetch is returning string for integer
>>
>>
>> Hello Evans/DBi-users,
>>
>> 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'.
>>
>> I am using EasySoft Driver via DBD::ODBC to connect to Microsoft SQL 
>> Server 2008 R2 from Linux RHEL 6.2.
>>
>> Version information
>> ---
>> Perl  : 5.10.1
>> DBI   : 1.609
>> DBD::ODBC : 1.30_5
>>
>> Please use the below code to reproduce the issue and let me know if I 
>> you need more information.
>>
>> Help much appreciated !!
>>
>> Thanks
>>
>>
>>
>> # Create temp_check and inserted one row with values (100, 10.234 and 
>> 'test')
>>
>> CREATE TABLE temp_check
>> ( a int,
>>    b float,
>>    c varchar (100)
>> )
>> INSERT INTO temp_check VALUES (100, 10.234000, 'test')
>>
>>
>> PERL snippet
>> ----------------------
>>
>> use DBI;
>> use Data::Dumper;
>>
>> $dbh = DBI->connect('dbi:ODBC:DSN=SERVER1','***','***');
>> $dbh->{TraceLevel}=15;
>>
>> $sth = $dbh->prepare('select * from sandbox..temp_check where a=100');
>> #$sth = $dbh->prepare('select * from sandbox..temp_check where 
>> a=100', {odbc_describe_parameters =>  0});
>>
>> $sth->execute();
>>
>> for ($i = 1; $i<= $sth->{NUM_OF_FIELDS}; $i++) {
>>      print "Column $sth->{NAME}->[$i -1] is of type $sth->{TYPE}->[$i 
>> -1]\n";
>> }
>>
>> $rows = $sth->fetchall_arrayref();
>> print Dumper($rows);
>>
>> Column a is of type 4
>> Column b is of type 6
>> Column c is of type 12
>>
>> $VAR1 = [
>>            [
>>              '100',
>>              '10.234',
>>              'test'
>>            ]
>>          ];
> Manikantan, sorry if this looks like I'm ignoring your question but to 
> provide the right answer I may need to fix something in DBD::ODBC.
>
> My first answer to this was to use the DiscardString attribute when 
> you bind the column. However, when I tried it, it did not work and I 
> reported https://rt.cpan.org/Public/Bug/Display.html?id=77283 to 
> myself. Having looked in to this now I can see why it is happening and 
> Tim, I could use a quick bit of feedback on 2 points:
>
> 1. Assuming you ask DBD::ODBC to bind the type as SQL_INTEGER ODBC 
> returns a 4 byte integer and in this case DBD::ODBC was treating it as 
> a string by calling sv_setpvn but passing in a pointer to that 4 byte 
> integer. I fixed this to use sv_setiv but the code to call 
> sql_type_cast_svpv is still executed. When sql_type_cast_svpv is 
> called it seg faults because the pv is not set (is the line I added ok 
> just to safe guard against this?):
>
>     case SQL_INTEGER:
>         /* sv_2iv is liberal, may return SvIV, SvUV, or SvNV */
>         if (!SvPOK(sv)) return 2;   /* MJE ADDED THIS LINE TO STOP 
> segfault */
>         sv_2iv(sv);
>         /* SvNOK will be set if value is out of range for IV/UV.
>          * SvIOK should be set but won't if sv is not numeric (in which
>          * case perl would have warn'd already if -w or warnings are 
> in effect)
>          */
>         cast_ok = (SvIOK(sv) && !SvNOK(sv));
>         break;
>
> Obviously, if the above is ok, I would not add the line there as it 
> applies to nvs as well.
>
An alternative, (although I think DBI needs this protection anyway) is 
that DBD::ODBC does not call sql_type_cast_svpv when the column is bound 
as an integer (as it simply calls sv_setiv so it knows DiscardString is 
meaningless).

> 2. is simply setting the iv via sv_setiv sufficient in this case so 
> there is no point in calling sql_type_cast_svpv?
>
> Martin
subversion trunk contains fixes for this and a change in behaviour:

=head2 Changes in DBD::ODBC 1.38_1 May 19 2012

   [BUG FIXES]

   Fixed rt 77283. If you overrode the bind type as SQL_INTEGER in a 
bind_col
   call AFTER previously binding as another type (or not specifying a type)
   you would not get the right value back. This also fixes the DiscardString
   bind_col attribute for SQL_INTEGER binds.

   Fixed some format specifiers in trace calls.

   [CHANGE IN BEHAVIOUR]

   DBD::ODBC allowed you to change the bound column type in bind_col
   after the column was already bound. It now does not allow this now
   and issues a warning. This could lead to problems - see above.

   [OTHER]

   Added some missing SQL_C_xxx types to S_SqlCTypeToString internal
   function. This only affects tracing.

   Some tests in 08bind were skipped when they did not need to be.

   sql_type_cast tests rewritten due to fixes above.

Please test this.

If you cannot apply the change to DBI I mention above then you can 
change DBD::OBDC's dbdimp.c as follows:

Index: dbdimp.c
===================================================================
--- dbdimp.c    (revision 15306)
+++ dbdimp.c    (working copy)
@@ -3308,7 +3308,8 @@
  #if DBIXS_REVISION > 13590
          /* If a bind type was specified we use DBI's sql_type_cast
             to cast it - currently only number types are handled */
-        if ((fbh->req_type == SQL_INTEGER) ||
+        if (
+            /*(fbh->req_type == SQL_INTEGER) || not reqd we already do 
sv_setiv */
              (fbh->req_type == SQL_NUMERIC) ||
              (fbh->req_type == SQL_DECIMAL)) {
              int sts;

Let me know if you have any problems.

Martin