Why are my SQL Server numeric and currency values returned too long with an additional random character
If you check Use regional settings when outputting currency, numbers, dates and times in a Microsoft SQL Server ODBC data source and then retrieve numeric or currency fields as chars (SQL_CHAR
), you'll encounter two problems:
- For numeric data, Microsoft SQL Server converts the number using your regional settings. For example, the number
1000
in the UK is converted to1,000.00
. Unfortunately, the length returned bySQLGetData
orSQLFetch
(withSQLBindCol
) is one more than the actual data returned. For the returned string1,000.00
, SQL Server actually reports that this string is9
characters long when in fact it is8
.You can reproduce this in Perl with:
my $sth = $dbh->prepare("SELECT COUNT(*) FROM table"); $sth->execute; my @row = $sth->fetchrow_array); print "\"$row[0]\" length of ". length($row[0]). "\n"; print "unpacked - |". unpack("H*", $row[0]), "|\n";
When there are 1000 rows, this script prints:
"1,000.00" length of 9 unpacked - |312c3030302e303000|
Notice the length of
9
(one too many) and the last character is hexadecimal (00
), but in actual fact, seems pretty random. If you run this code under the Perl debugger and use thex
command on$row[0]
, you'll get something like this:DB<1> x $row[0] 0 "1,000.00\c@"
To reproduce this in the ODBC API:
/* connect etc. */ SQLExecDirect("SELECT COUNT(*) FROM table"); SQLFetch SQLGetData(SQL_CHAR, buffer, buffer_size=20, StrLen_or_IndPtr)
Again, you get
9
back inStrLen_or_IndPtr
when the returned data is1,000.00
(one too many). - The second problem is that when regional settings are turned on, numeric and currency data is longer than with regional settings off, but
SQLDescribeCol
does not return larger column sizes. This is documented in Why do I get "String data, right truncation" retrieving result-sets from Microsoft SQL Server?If you can't change the type retrieved from
SQL_CHAR
toSQL_INTEGER
then you'll need to turn off the regional settings in the data source.