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

[Martin J. Evans]

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: mjev...@cpan.org
Cc: dbi-us...@perl.org
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