If you check "Use regional settings when outputting currency, numbers, dates and times" in a MS SQL Server data source and then retrieve numeric or currency fields as chars (SQL_CHAR) you will encounter two problems:
You can reproduce this with a simple bit of perl:
my $sth = $dbh->prepare("select count(*) from table"); $sth->execute; my @row = $sth->fetchrow_array); print "\"$row\" length of ". length($row). "\n"; print "unpacked - |". unpack("H*", $row), "|\n";
which when there are 1000 rows prints:
"1,000.00" length of 9 unpacked - |312c3030302e303000|
Notice the length of 9 (one too many) and the last character is hex 00 here but in actual fact seems pretty random. If you run this code under the perl debugger and use the 'x' command on $row you'll see something like this:
DB<1> x $row 0 "1,000.00\c@"
To reproduce in the ODBC API simply:
connect etc SQLExecDirect("select count(*) from table"); SQLFetch SQLGetData(SQL_CHAR, buffer, buffer_size=20, StrLen_or_IndPtr)
and you'll get 9 back in StrLen_or_IndPtr when the returned data is "1,000.00" (one too many).
If you cannot change the type retrieved from SQL_CHAR to SQL_INTEGER then you'll need to uncheck the regional settings in the data source.
Oracle is a registered trademark of Oracle Corporation and/or its affiliates.