Easysoft ODBC-ODBC Bridge

Why do I get "String data, right truncation" retrieving result-sets from MS SQL Server?

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

Please see the question Why do I keep getting data truncated errors in my Perl? first as that is more likely to be the answer you are looking for.

Before going in to the specifics of an issue in the MS SQL Server ODBC driver it should be noted that the most common reason for this error is calling SQLGetData or binding a column and supplying too small a buffer for the column data.

e.g.

SQLExecDirect(select var_char_column from table)
SQLDescribeCol(column=1)
returns a column size of 100
or SQLColAttributes(column=1, SQL_DESC_DISPLAY_SIZE)
     which also returns 100
SQLFetch
SQLGetData(column=1, type=SQL_C_CHAR, buffer, buffer_size = 10)

or

SQLExecDirect(select var_char_column from table)
SQLDescribeCol(column=1)
returns a column size of 100
or SQLColAttributes(column=1, SQL_DESC_DISPLAY_SIZE)
     which also returns 100
SQLBindCol(column=1, type=SQL_C_CHAR, buffer, buffer_size = 10)
SQLFetch

In both cases the buffer supplied for the column data was too small and you will get a SQL_SUCCESS_WITH_INFO return from SQLGetData/SQLFetch and a state of 01004.

However, there is one specific issue with the MS SQL Server ODBC driver which can seriously confuse ODBC applications that bind columns as SQL_C_CHAR. When retrieving column data from a result-set as strings the average ODBC application:

[1] issues the query

[2] calls SQLNumResultCols to find out how many columns in the result-set

[3] calls SQLDescribeCol for each column to find out the type of the column and sometimes SQLColAttribute to get the display size.

[4] for each column calls SQLGetData (or SQLBindCol) passing a buffer based on the reported display size.

Ordinarily this works fine with the MS SQL Server driver but when "Use Regional Settings" is checked in the MS SQL Server ODBC driver DSN setup dialog everything changes. Once "Use Regional Settings" is checked the ODBC driver returns integer fields using the current regional settings and this generally increases the size of the returned data. For UK machines the simple integer 1234, is returned as "1,234.00". The problem is that when "Use Regional Settings" is checked SQLDescribeCol and SQLColAttribute return the same column-size as they do when this setting is unchecked and so for larger numbers the column-size of 10 will be insufficient to hold the number and you will get "string data right truncated". A simple example illustrates the problem:

SQLGetTypeInfo(SQL_LONGVARCHAR)

this returns a result-set describing the SQL_LONGVARCHAR type of which one of the columns (column 3) is the maximum column size that the server supports for this data type. In MS SQL Server the maximum size of a SQL_LONGVARCHAR is 2147483647 bytes.

SQLDescribeCol(column=3)

this returns (Name : COLUMN_SIZE, Type : 4, Size : 10, Decimal Digits : 0, Nullable : 1)

Note the Size=10. Most applications will use this number to allocate a buffer to receive the column data if the column data is being retrieved as SQL_C_CHAR (they add 1 for the terminating NUL as well).

SQLColAttribute(column=3, SQL_DESC_DISPLAY_SIZE)

returns 11

SQLGetData(column=3, buffer, buffer_size=100)

returns "2,147,483,647.00" in the UK which is too big to fit in the previously returned size of 10 or 11.

We have found a number of applications that suffer from this problem but perhaps the most common is Perl's DBD::ODBC which binds integer columns as SQL_C_CHAR (quite naturally really given Perl's text processing features). If "Use Regional Settings" is checked in your MS SQL Server data source and you run make test for DBD::ODBC you are likely to see something like:

# make test
PERL_DL_NONLAZY=1 /usr/local/bin/perl "-MExtUtils::Command::MM"
"-e" "test_harness(0, 'blib/lib', 'blib/arch')" t/*.t
t/01base.........ok
t/02simple.......ok 2/17
DBD::ODBC::st fetchrow failed:
[unixODBC][][Microsoft][ODBC SQL Server Driver]
String data, right truncation (SQL-01004)
(DBD: st_fetch/SQLFetch (long truncated) err=-1) at t/ODBCTEST.pm line 56.
DBD::ODBC::st fetchrow failed:
[unixODBC][][Microsoft][ODBC SQL Server Driver]
String data, right truncation (SQL-01004)
(DBD: st_fetch/SQLFetch (long truncated) err=-1) at t/ODBCTEST.pm line 56.
Unable to find a suitable test type for field COL_C at t/ODBCTEST.pm line 63.
t/02simple.......dubious
        Test returned status 255 (wstat 65280, 0xff00)

There are two possible solutions to this problem:

[1] uncheck "Use Regional Settings" in the MS SQL Server data source.

[2] add Regional=No to your connection string passed to DBI->Connect. e.g. "dbi:ODBC:DSN=mydsn;UID=dbuser;PWD=dbpassword;Regional=No;"

The MS help for the "Use Regional Settings" specifically says "Select this option forapplications that only display data, not for applications that process data".

For DBD::ODBC and perhaps for some other ODBC applications you will find that disabling "Use Regional Settings" is the only way of making them work.

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.