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

Easysoft ODBC-ODBC Bridge

Why are my numeric and currency values returned too long with an additional random character from Microsoft SQL Server?

Article:
00991
Last Reviewed:
26th January 2024
Revision:
2

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 will encounter two problems:

  1. For numeric data, Microsoft SQL Server will convert the number using your regional settings. E.g. 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," Microsoft SQL Server will actually say this string is 9 characters long when in fact it is 8.

    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[0]\"  length of ". length($row[0]). "\n";
print "unpacked - |". unpack("H*", $row[0]), "|\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[0], you'll see something like this:

    DB<1> x $row[0]
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).

  2. The second problem is that when regional settings are turned on, numeric and currency data are longer than with regional settings off, but SQLDescribeCol does not return larger column sizes. This is best documented in "Why do I get "String data, right truncation" retrieving result-sets from Microsoft SQL Server?"

    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.

Applies To

Knowledge Base Feedback

* Did this content help you?
* Please select one option based on your first choice:

(* Required Fields)