How to properly access a VARCHAR(MAX) parameter value of a FireDAC dataset when getting "data too large for variable" error?

4
votes

Our application updates and accesses data using SQL Server 2014.

I have a table of which the last column ('Contents') is created as VARCHAR(MAX).

We are using Delphi XE8, and are using a FireDAC TFDQuery component to update this column. 

.....
FDquery.ParamByName('Contents').AsString:=Contents;
FDquery.ExecSQL;

When running this update, I get the following error:

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

'Contents' can be string of varying length.

Browsing the web, the only reasonably simple solution that I found is changing the query as follows:

 FDquery.ParamByName('Contents').AsWideMemo:=Contents;
 FDquery.ExecSQL;

Is this acceptable, or should I be handling this differently?

There is nothing fancy with 'Contents', as I mentioned, it is simply a long string.

1
sql-serverdelphidelphi-xe8firedac
Possible solution. stackoverflow.com/questions/12097670/… - SS_DBA
As far as I understand it, it's a FireDAC issue ... - Alex
It's not an issue. Accessing value by AsString you claim that the parameter is of data type ftString or ftWideString and values are send to the DBMS accordingly. And because ft(Wide)String is for SQL server mapped to data types that can be at most 8k chars long, you receive such error. If you access that parameter by As(Wide)Memo, it is handled as ftMemo field. The whole point is that As<> access modifies parameter DataType and the value is send by that to DBMS. - Victoria

1 Answers

3
votes

Is it acceptable to access a VARCHAR(MAX) type field parameter value by the AsWideMemo property?

Not especially. For VARCHAR(MAX) field parameter use AsMemo access. It is because you could be sending to your DBMS Unicode values to a non Unicode field. From the reference:

The Unicode encoded parameter value is converted to a Unicode character set, which is supported by the DBMS, and sent to the DBMS. This does not depend on a client character set or on a Delphi version.

If your field would be NVARCHAR(MAX), using AsWideMemo access to the parameter value would be the right choice.

Why am I getting “data too large for variable” error when having assigned a more than 8k chars long string by the AsString property?

Some background to why this happens. By accessing a certain parameter value by As<T> property, the engine also sets the parameter DataType, if you don't explicitly do that before. In this particular case you hinted the engine that it's fine for you if it sets the parameter data type to ftWideString or ftString just by accessing parameter value by the AsString property.

And thanks to data type mapping such parameter is treated as the VARCHAR[n] or NVARCHAR[n] data type including its limits (hence you got a string length limit error here).

Similar, just more specific data type hint is used when you access the parameter value by AsMemo property, it defaults to ftMemo data type which maps to VARCHAR(MAX). And as you may predict, AsWideMemo access defaults to ftWideMemo which maps to NVARCHAR(MAX) data type. If you don't want to explicitly set the parameter data types but use this hinting, consult the manual to see how each used access property sets the default parameter data type.