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: 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. 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 ==
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: 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
FW: DBD::ODBC fetch is returning string for integer
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' ] ];