3

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?

mjn
  • 36,362
  • 28
  • 176
  • 378
  • Just curious: Why use ODBC driver instead of native MSSQL FireDAC driver? – fpiette Dec 07 '20 at 09:41
  • @fpiette the application supports more database types, would this still work with native drivers? (I am a little afraid it would require more code changes than we can afford) – mjn Dec 07 '20 at 10:26
  • In FireDAC, you may change driver at runtime. So yes, you can use native driver for all database being supported by a native driver and fall back to ODBC for others. – fpiette Dec 07 '20 at 10:32
  • 2
    I don't understand the issue. Both error messages tell you exactly what to do to fix the issue (set the TFDParam.Size to a greater value). The first error message actually tells you that the limit is not 256 characters (it tells you the max len is 8002 and you're trying to assign a length of 13829). – Ken White Dec 07 '20 at 14:08
  • @KenWhite the upper example is taken from the linked question. My question is why the limit is lower in my environment. – mjn Dec 08 '20 at 07:42
  • But did you try to set `TFDParam.Size` something like 16000? Like the error says? – R. Hoek Dec 11 '20 at 22:06
  • @R.Hoek of course it works. I want to avoid the problem in the first place, by increasing the limit to the 'default' of 8002 as shown in the linked question. Then I can leave my sources unchanged. – mjn Dec 12 '20 at 12:47
  • @mjn ok, so you mean your using the components as is (maybe even creating them at runtime) and then the default seems to be 256, even when the field itself is bigger? – R. Hoek Dec 12 '20 at 13:09
  • @R.Hoek yes, the field is bigger, for example varchar(1600), but this is smaller than 8002. Instead of setting the TFDParam.Size everywhere in the code for string fields with more than 256 char length, I would prefer to have the limit of 8002 chars. – mjn Dec 12 '20 at 14:13
  • Maybe you should refrase your question to elaborate this need, as it’s not obvious now. Sadly I don’t known any such setting. – R. Hoek Dec 12 '20 at 16:15
  • @R.Hoek many thanks for your suggestion, I'lll update the question – mjn Dec 17 '20 at 08:25

1 Answers1

0

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://learn.microsoft.com/en-us/sql/t-sql/data-types/char-and-varchar-transact-sql?view=sql-server-2017#remarks

eniel.rod
  • 855
  • 8
  • 12
  • 1
    Thank you, this is really important to know. However it does not explain the behaviour of the database in my case, where the VARCHAR(1600) column does not accept a 300 character value. – mjn Dec 17 '20 at 08:23