Download ODBC Drivers for Oracle, SQL Server, Salesforce, MongoDB, Access, Derby, InterBase & DB2.

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:

  1. For numeric data, Microsoft SQL Server converts the number using your regional settings. For example, the number 1000 in the UK is converted to 1,000.00. Unfortunately, the length returned by SQLGetData or SQLFetch (with SQLBindCol) is one more than the actual data returned. For the returned string 1,000.00, SQL Server actually reports that this string is 9 characters long when in fact it is 8.

    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 the x 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 in StrLen_or_IndPtr when the returned data is 1,000.00 (one too many).

  2. 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 to SQL_INTEGER then you'll need to turn off the regional settings in the data source.