Why does [FireDAC][Phys][ODBC]-345 say that Max len is 256 characters?

user_80901 0
Viewed 823

In question How to properly access a VARCHAR(MAX) parameter value of a FireDAC dataset when getting "data too large for variable" error? the error message says Max len = [8002] for a VARCHAR(MAX) field.

In my case however, using a MS SQL 2017 database and an ODBC driver version 13, the message says that the maximum length is 256 for a VARCHAR(1600) field:

Exception raised with message [FireDAC][Phys][ODBC]-345. Data too large for variable [MY_PARAM]. Max len = [256], actual len = [300] Hint: set the TFDParam.Size to a greater value.

Are there any configuration options which can cause this lower value for Max len?

firedac odbc varchar delphi sql-server
1 Answers

Could it be related to this?

A common misconception is to think that CHAR(n) and VARCHAR(n), the n defines the number of characters. But in CHAR(n) and VARCHAR(n) the n defines the string length in bytes (0-8,000). n never defines numbers of characters that can be stored.

The misconception happens because when using single-byte encoding, the storage size of CHAR and VARCHAR is n bytes and the number of characters is also n. However, for multi-byte encoding such as UTF-8, higher Unicode ranges (128-1,114,111) result in one character using two or more bytes. For example, in a column defined as CHAR(10), the Database Engine can store 10 characters that use single-byte encoding (Unicode range 0-127), but less than 10 characters when using multi-byte encoding (Unicode range 128-1,114,111).

https://docs.microsoft.com/en-us/sql/t-sql/data-types/char-and-varchar-transact-sql?view=sql-server-2017#remarks

user_30233950
Related