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
===================================================================
--- 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
