Pass long Unicode strings to FireDac TADQuery parameter

Question

I was using AsWideString to pass Unicode string to TADQuery parameter.

ADQuery.Params.ParamByName('MyFld').AsWideString

But when string becomes too long I got error:

[MyFld]. Max len = [8002], actual len = [10522]

Then I decided to use AsMemo property

ADQuery.Params.ParamByName('MyFld').AsMemo

In this case my Unicode string is not displayed correctly.

What is the way to solve both problems?

Girl

@David, FireDAC implements it as you expect, but see e.g. https://mcmap.net/q/813298/-delphi-xe2-dataset-field-type-tstringfield-does-not-support-unicode. Common dataset providers were (and who knows, maybe are) implemented as that AsString getter is used to return ANSI string from the underlying database. Even in Unicode Delphi. It's a trap. – Malissa

Answer 1

The TFDParam type has an .AsWideMemo in XE5 that should accept unicode characters correctly and get around the size limitation you encountered.

ADQuery.Params.ParamByName('MyFld').AsWideMemo := 'Some unicode string';

Danialdaniala

Answer 2

The actual database field has a max character limit assigned to it. You cannot set a value that exceeds that limit. It will either truncate the value, or in your case, raise an error. You cannot use AsMemo to set a non-Memo field. Keep using AsWideString and pay attention to your database layout.

Caddell

I'd say that this is actually not the case. If you have a NVARCHAR(MAX) field and use AsString/AsWideString parameter that is longer than 8002 characters, you get this exact error. The recommended solution is to use AsMemo, but in the OP's situation that was not working with unicode. – Danialdaniala

AsWideString changes parameter type to ftWideString which through the predefined data type mapping results to data type with its limits, like e.g. on SQL Server to nvarchar[n] where n can be at most 4k. So using AsWideString actually decreases the limit of stored string length if there's a column like nvarchar[max] on SQL Server behind. @Jason is correct here. – Vaughn