Parameter.AsString failing under Oracle/MSSQL - Parameter.Value 2-byte chars under Oracle

Question

After changing to FireDAC I have trouble getting this code to work on MSSQL/Oracle:

with DataFormsettings do
begin
  Close;
  if Params.Count=0 then FetchParams;
  Params.ParamByName('TT_EMP_ID').Asinteger := AEmpID;
  Params.ParamByName('TT_FORM').AString := UpperCase(AKey);  
  Open;
  if (RecordCount>0) then
     S := FieldByName('TT_VIEWDATA').Asstring;     
end;   

AKey and S are both strings.

The Open statement gives an error

[FireDAC][Phys][MSSQL]-338 Param type changed from [ftString] to [ftWidestring]
[FireDAC][Phys][Ora]-338 Param type changed from [ftString] to [ftWidestring]

when connecting to a MSSQL or Oracle database; not when connecting to FireBird.
After the FetchParams, DataFormsettings.params[1].datatype is always an ftString.

If I replace

Params.ParamByName('TT_FORM').AString := UpperCase(AKey);  

with

Params.ParamByName('TT_FORM').Value := UpperCase(AKey);

... there are no errors on the Open statement. I thought that had solved it although I did not really understand the error. After all, this should be all default Delphi String types...
But now the S assigment fails for Oracle (not FireBird or MSSQL) in the sense that I see 2-byte characters getting returned. S contains:

\'#0'S'#0'o'#0'f'#0't'#0'w'#0'a'#0'r'#0'e'#0'\'#0'T'#0'i'#0'm'#0'e'#0'T'#0'e'#0'l'#0'l'#0'...

I can handle that with e.g.

S := TEncoding.Unicode.GetString(FieldByName('TT_VIEWDATA').AsBytes);  

for Oracle, but (of course) when using the other two database types that does not work:

No mapping for the Unicode character exists in the target multi-byte code page

What am I missing here? Specifically, I would like to just get the AsString retrievals/assignments to work.
Note the Setting the AsString property sets the DataType property to ftWideString or ftString remark in the FireDAC TFDParam.AsString documentation. It seems as if the parameter value assignment just switches the type from ftString to ftWideString (as indicated by the original error).

DataFormSettings is a TClientDataSet in a client application, connected to a server application where TDataSetProvider and TFDQuery reside. The query is

select
  TT_FORMSETTINGS_ID,
  TT_EMP_ID,
  TT_FORM,
  TT_VERSION,
  TT_VIEWDATA
from TT_FORMSETTINGS
where TT_EMP_ID=:TT_EMP_ID
and TT_FORM=:TT_FORM

The tables were created as follows:

FireBird:

CREATE TABLE TT_FORMSETTINGS
(
  TT_FORMSETTINGS_ID    INTEGER DEFAULT 0 NOT NULL,
  TT_EMP_ID     INTEGER,
  TT_FORM       VARCHAR(50),
  TT_VERSION        INTEGER,
  TT_VIEWDATA       BLOB SUB_TYPE TEXT SEGMENT SIZE 80,
  TT_TAG    INTEGER,
  TT_TAGTYPE    INTEGER,
  TT_TAGDATE    TIMESTAMP
);

Oracle:

CREATE TABLE TT_FORMSETTINGS
(
  TT_FORMSETTINGS_ID    NUMBER(10,0) DEFAULT 0 NOT NULL,
  TT_EMP_ID     NUMBER(10,0),
  TT_FORM       VARCHAR(50),
  TT_VERSION        NUMBER(10,0),
  TT_VIEWDATA       CLOB,
  TT_TAG    NUMBER(10,0),
  TT_TAGTYPE    NUMBER(10,0),
  TT_TAGDATE    DATE
);

MSSQL:

CREATE TABLE TT_FORMSETTINGS
(
  TT_FORMSETTINGS_ID    INTEGER  NOT NULL CONSTRAINT TT_C0_FORMSETTINGS DEFAULT 0,
  TT_EMP_ID     INTEGER NULL,
  TT_FORM       VARCHAR(50) NULL,
  TT_VERSION        INTEGER NULL,
  TT_VIEWDATA       TEXT NULL,
  TT_TAG    INTEGER NULL,
  TT_TAGTYPE    INTEGER NULL,
  TT_TAGDATE    DATETIME NULL
);

I have checked that TT_VIEWDATA contains correct data in all databases; it is a long string containing CRLFs:

\Software\TimeTell\Demo8\Forms\TFormTileMenu'#$D#$A'Version,1,80502'#$D#$A'\Software\TimeTell\Demo8\Forms\TFormTileMenu\TileControlMenu'#$D#$A'\Software\TimeTell\Demo8\Forms\TFormTileMenu\TileControlMenu\FormTileMenu.TileControlMenu'#$D#$A'Version,4,2'#$D#$A'\Software\TimeTell\Demo8\Forms\TFormTileMenu\TileControlMenu\FormTileMenu.TileControlMenu...

Notes:

• Currently testing on SQL Server 2008 and Oracle 10, but I expect this to be no different for other versions.
• FWIW, select * from NLS_database_PARAMETERS where parameter like '%CHARACTERSET%' returns NLS_CHARACTERSET=WE8MSWIN1252 and NLS_NCHAR_CHARACTERSET=AL16UTF16
  Query SELECT dump(dbms_lob.substr(tt_viewdata,100,1), 1016), tt_viewdata FROM tt_formsettings confirms that the CLOB contains ASCII bytes for the Win1252 codepage:
  Typ=1 Len=100 CharacterSet=WE8MSWIN1252: 5c,53,6f,66,74,77,61,72,65,5c,54,69,6d,65,54,65,6c,6c,5c,44,65,...
• FieldByName().AsANSIString gives the same results as FieldByName().AsString

---

Additional information: This is a legacy application with persistent field definitions on the DataFormsettings TClientDataset. TT_VIEWDATA is defined as a TMemoField:

DataFormsettingsTT_VIEWDATA: TMemoField;

In a small testapp (directly connected to Oracle; not client-server) I let Delphi add the field definitions and then it said:

DataFormsettingsTT_VIEWDATA: TWideMemoField;

If I use that in the main app, Oracle works fine but then I get 'garbage' for MSSQL.

I also experimented with setting up mapping rules for the Oracle connection like (many variations):

with AConnection.FormatOptions.MapRules.Add do
begin
  SourceDataType := dtWideMemo;
  TargetDataType := dtMemo;
end;
AConnection.FormatOptions.OwnMapRules := true;

but that did not help.

Answer 1

Here is the reason it does not work:

In FireDAC.Stan.Option:

procedure TFDFormatOptions.ColumnDef2FieldDef()
...
dtWideHMemo:
  // Here was ftOraClob, but then will be created TMemoField,
  // which does not know anything about Unicode. So, I have
  // changed to ftFmtMemo. But probably may be problems ...
  ADestFieldType := ftWideMemo;

Indeed, probably may be problems.

The solution is to add a mapping rule that converts dtWideHMemo to dtMemo.
After that, reading and writing to the CLOB .AsString works fine.

Reported as RSP-19600 in Embarcadero Quality Portal.

---

For completeness: because the mapping mentioned in my other answer is no longer active, you have to change access to the parameters with .Value instead of .AsString.