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

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.

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