Easysoft ODBC-ODBC Bridge

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

Article:
00991
Last Reviewed:
27th November 2008
Revision:
2

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:

  1. For numeric data MS 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 length returned by the SQLGetData or SQLFetch (with SQLBindCol) is one more than the actual data returned. For the returned string "1,000.00" MS 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 MS 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)


Oracle is a registered trademark of Oracle Corporation and/or its affiliates.