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

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.

This is not a definitive solution, see the last remarks before the code blocks. It still feels like a hack. I'm not adding it to the question (as 'attempts') because ultimately this would work.

There were two things going on, and they can both be worked around with the changes below:

1. The Param type changed error on the Params value assignment
   
2. Field definitions and the FieldByName().AsString retrieval/assigning not working

Note that I am restricted by design-time field definitions throughout the application that has to handle all three database types, specifically the DataFormSettingsTT_VIEWDATA persistent field being a TMemoField.

With the table definitions mentioned at the bottom of the question, if you set up a TFDConnection -> TFDQuery -> TDataSetProvider -> TClientDataSet and you add the field definitions with Add all fields, DataFormSettingsTT_VIEWDATA will be of type:

• TMemoField with BlobType=ftMemoField for FireBird

• TMemoField with BlobType=ftWideMemoField for MSSQL

• TWideMemoField with BlobType=ftWideMemoField for Oracle.

Manually editing the .DFM and .PAS to set the Oracle TWideMemoField back to TMemoField works (well, I don't have to change it, it's legacy code) if I also:

• force BlobType=ftWideMemoField for the design time TMemoFields at run time (I can do that in the OnCreate in a parent that all my datamodules descend from);

• handle the string retrieval for Oracle only as TEncoding.Unicode.GetString(FieldByName(SFormSettingsViewData).AsBytes).

But this is still not optimal. My client code with the TClientDataSet will now have to know what kind of database it is. I have means in the client app to query the server for that.

Here is a sample app with these changes:

uFireDacOracleBlob.pas file:

unit uFireDacOracleBlob;interfaceuses  Winapi.Windows, Winapi.Messages, System.SysUtils, System.Variants, System.Classes, Vcl.Graphics,  Vcl.Controls, Vcl.Forms, Vcl.Dialogs, FireDAC.Stan.Intf, FireDAC.Stan.Option,  FireDAC.Stan.Error, FireDAC.UI.Intf, FireDAC.Phys.Intf, FireDAC.Stan.Def,  FireDAC.Stan.Pool, FireDAC.Stan.Async, FireDAC.Phys, FireDAC.Phys.Oracle,  FireDAC.Phys.OracleDef, FireDAC.VCLUI.Wait, FireDAC.Stan.Param, FireDAC.DatS,  FireDAC.DApt.Intf, FireDAC.DApt, Datasnap.DBClient, Datasnap.Provider,  Data.DB, FireDAC.Comp.DataSet, FireDAC.Comp.Client, Vcl.StdCtrls, Vcl.ExtCtrls,  FireDAC.Phys.MSSQL, FireDAC.Phys.MSSQLDef, FireDAC.Phys.IB,  FireDAC.Phys.IBDef, FireDAC.Phys.FBDef, FireDAC.Phys.IBBase, FireDAC.Phys.FB,  FireDAC.Phys.ODBCBase;type  TFrmFireDacOracleBlob = class(TForm)    FDConnection1: TFDConnection;    FDPhysOracleDriverLink1: TFDPhysOracleDriverLink;    FDQuery1: TFDQuery;    DataSetProvider1: TDataSetProvider;    ClientDataSet1: TClientDataSet;    Edit0: TEdit;    Label1: TLabel;    LblPos0: TLabel;    RGpDB: TRadioGroup;    BtnOpen: TButton;    FDConnection2: TFDConnection;    FDQuery2: TFDQuery;    DataSetProvider2: TDataSetProvider;    ClientDataSet2: TClientDataSet;    FDConnection0: TFDConnection;    FDQuery0: TFDQuery;    DataSetProvider0: TDataSetProvider;    ClientDataSet0: TClientDataSet;    FDPhysMSSQLDriverLink1: TFDPhysMSSQLDriverLink;    FDPhysFBDriverLink1: TFDPhysFBDriverLink;    ClientDataSet0TT_FORMSETTINGS_ID: TIntegerField;    ClientDataSet0TT_EMP_ID: TIntegerField;    ClientDataSet0TT_FORM: TStringField;    ClientDataSet0TT_VERSION: TIntegerField;    ClientDataSet0TT_VIEWDATA: TMemoField;    ClientDataSet1TT_FORMSETTINGS_ID: TIntegerField;    ClientDataSet1TT_EMP_ID: TIntegerField;    ClientDataSet1TT_FORM: TStringField;    ClientDataSet1TT_VERSION: TIntegerField;    ClientDataSet1TT_VIEWDATA: TMemoField;    ClientDataSet2TT_FORMSETTINGS_ID: TIntegerField;    ClientDataSet2TT_EMP_ID: TIntegerField;    ClientDataSet2TT_FORM: TStringField;    ClientDataSet2TT_VERSION: TIntegerField;    ClientDataSet2TT_VIEWDATA: TMemoField;    BtnSet: TButton;    Label2: TLabel;    LblPos1: TLabel;    Edit1: TEdit;    Label4: TLabel;    LblPos2: TLabel;    Edit2: TEdit;    BtnParam: TButton;    procedure BtnOpenClick(Sender: TObject);    procedure BtnSetClick(Sender: TObject);    procedure BtnParamClick(Sender: TObject);    procedure FormCreate(Sender: TObject);  private    FStrFirebird,    FStrOracle,    FStrMSSQL   :String;    procedure ShowString(AStr: String; ALbl: TLabel; AEdit: TEdit);  public  end;var  FrmFireDacOracleBlob: TFrmFireDacOracleBlob;implementation{$R *.dfm}const   cSQLText = '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';procedure TFrmFireDacOracleBlob.BtnParamClick(Sender: TObject);begin  case RGpDB.ItemIndex of     0: begin           FDQuery0.SQL.Text := cSQLText;           with ClientDataSet0 do           begin              if Params.Count=0 then FetchParams;              Params.ParamByName('TT_EMP_ID').Asinteger := 1;              Params.ParamByName('TT_FORM').AsString := 'TFORMTILEMENU';              Open;              if (RecordCount>0) then                 FStrFirebird := FieldByName('TT_VIEWDATA').Asstring;              ShowString(FStrFireBird,LblPos0,Edit0);           end;        end;     1: begin           FDQuery1.SQL.Text := cSQLText;           with ClientDataSet1 do           begin              if Params.Count=0 then FetchParams;              Params.ParamByName('TT_EMP_ID').Asinteger := 1;              Params.ParamByName('TT_FORM').AsString := 'TFORMTILEMENU';              Open;              if (RecordCount>0) then                 // FStrOracle := FieldByName('TT_VIEWDATA').Value;                 FStrOracle := TEncoding.Unicode.GetString(FieldByName('tt_viewdata').AsBytes);              ShowString(FStrOracle,LblPos1,Edit1);           end;        end;     2: begin           FDQuery2.SQL.Text := cSQLText;           with ClientDataSet2 do           begin              if Params.Count=0 then FetchParams;              Params.ParamByName('TT_EMP_ID').Asinteger := 1;              Params.ParamByName('TT_FORM').AsString := 'TFORMTILEMENU';              Open;              if (RecordCount>0) then                 FStrMSSQL := FieldByName('TT_VIEWDATA').Asstring;              ShowString(FStrMSSQL,LblPos2,Edit2);           end;        end;  end;end;procedure TFrmFireDacOracleBlob.BtnSetClick(Sender: TObject);begin  case RGpDB.ItemIndex of     0: begin           FStrFirebird := FStrFirebird + #13#10'Added another line';           ClientDataSet0.Edit;           ClientDataSet0.FieldByName('tt_viewdata').Value := FStrFireBird;           ClientDataSet0.ApplyUpdates(0);        end;     1: begin           FStrOracle := FStrOracle + #13#10'Added another line';           ClientDataSet1.Edit;           // ClientDataSet1.FieldByName('tt_viewdata').AsString := FStrOracle; // does not work           // ClientDataSet1.FieldByName('tt_viewdata').Value := FStrOracle;    // does not work           ClientDataSet1.FieldByName('tt_viewdata').Value := TEncoding.Unicode.GetBytes(FStrOracle);           // ClientDataSet1.FieldByName('tt_viewdata').AsBytes := TEncoding.Unicode.GetBytes(FStrOracle);  Also works           ClientDataSet1.ApplyUpdates(0);        end;     2: begin           FStrMSSQL := FStrMSSQL + #13#10'Added another line';           ClientDataSet2.Edit;           ClientDataSet2.FieldByName('tt_viewdata').AsString := FStrFireBird;           ClientDataSet2.ApplyUpdates(0);        end;  end;end;procedure TFrmFireDacOracleBlob.FormCreate(Sender: TObject);var i: integer;begin   for i := 0 to self.ComponentCount-1 do      if (self.Components[i] is TMemoField) then         (self.Components[i] as TMemoField).BlobType := ftWideMemo;end;procedure TFrmFireDacOracleBlob.ShowString(AStr: String; ALbl: TLabel; AEdit: TEdit);begin  ALbl.Caption := IntToStr(Pos(#13#10,AStr));  AEdit.Text := AStr;end;procedure TFrmFireDacOracleBlob.BtnOpenClick(Sender: TObject);begin  case RGpDB.ItemIndex of     0: begin           // SetFireBirdMapRules(FDConnection1);   Design time           ClientDataSet0.Open;           FStrFirebird := ClientDataSet0.FieldByName('tt_viewdata').AsString;           ShowString(FStrFireBird,LblPos0,Edit0);        end;     1: begin           // SetOracleMapRules(FDConnection1);   Design time           ClientDataSet1.Open;           // FStrOracle := ClientDataSet1.FieldByName('tt_viewdata').AsString;           FStrOracle := TEncoding.Unicode.GetString(ClientDataSet1.FieldByName('tt_viewdata').AsBytes);           ShowString(FStrOracle,LblPos1,Edit1);        end;     2: begin           // SetMSSQLMapRules(FDConnection1);   Design time           ClientDataSet2.Open;           FStrMSSQL := ClientDataSet2.FieldByName('tt_viewdata').AsString;           ShowString(FStrMSSQL,LblPos2,Edit2);        end;  end;end;end.

uFireDacOracleBlob.dfm file:

object FrmFireDacOracleBlob: TFrmFireDacOracleBlob  Left = 0  Top = 0  Caption = 'FireDac and Oracle Clobs'  ClientHeight = 278  ClientWidth = 577  Color = clBtnFace  Font.Charset = DEFAULT_CHARSET  Font.Color = clWindowText  Font.Height = -11  Font.Name = 'Tahoma'  Font.Style = []  OldCreateOrder = False  Position = poScreenCenter  OnCreate = FormCreate  PixelsPerInch = 96  TextHeight = 13  object Label1: TLabel    Left = 32    Top = 161    Width = 91    Height = 13    Caption = 'Position first CRLF:'  end  object LblPos0: TLabel    Left = 128    Top = 161    Width = 6    Height = 13    Caption = '0'  end  object Label2: TLabel    Left = 32    Top = 203    Width = 91    Height = 13    Caption = 'Position first CRLF:'  end  object LblPos1: TLabel    Left = 128    Top = 203    Width = 6    Height = 13    Caption = '0'  end  object Label4: TLabel    Left = 32    Top = 245    Width = 91    Height = 13    Caption = 'Position first CRLF:'  end  object LblPos2: TLabel    Left = 128    Top = 245    Width = 6    Height = 13    Caption = '0'  end  object Edit0: TEdit    Left = 32    Top = 138    Width = 505    Height = 21    TabOrder = 0  end  object RGpDB: TRadioGroup    Left = 32    Top = 8    Width = 249    Height = 33    Columns = 3    ItemIndex = 0    Items.Strings = (      'FireBird'      'Oracle'      'MSSQL')    TabOrder = 1  end  object BtnOpen: TButton    Left = 32    Top = 56    Width = 75    Height = 25    Caption = 'Open Table'    TabOrder = 2    OnClick = BtnOpenClick  end  object BtnSet: TButton    Left = 120    Top = 56    Width = 75    Height = 25    Caption = 'Update field'    TabOrder = 3    OnClick = BtnSetClick  end  object Edit1: TEdit    Left = 32    Top = 180    Width = 505    Height = 21    TabOrder = 4  end  object Edit2: TEdit    Left = 32    Top = 222    Width = 505    Height = 21    TabOrder = 5  end  object BtnParam: TButton    Left = 32    Top = 96    Width = 104    Height = 25    Caption = 'Open with params'    TabOrder = 6    OnClick = BtnParamClick  end  object FDConnection1: TFDConnection    Params.Strings = (      'User_Name=testv4'      'Password=testv4'      'Database=VS2003-2005-10'      'DriverID=Ora')    FormatOptions.AssignedValues = [fvMapRules]    FormatOptions.OwnMapRules = True    FormatOptions.MapRules = <      item        SourceDataType = dtBCD        TargetDataType = dtInt32      end      item        SourceDataType = dtFmtBCD        TargetDataType = dtDouble      end>    Connected = True    LoginPrompt = False    Left = 312    Top = 72  end  object FDPhysOracleDriverLink1: TFDPhysOracleDriverLink    Left = 368    Top = 72  end  object FDQuery1: TFDQuery    Connection = FDConnection1    SQL.Strings = (      'select * from tt_formsettings')    Left = 416    Top = 72  end  object DataSetProvider1: TDataSetProvider    DataSet = FDQuery1    Left = 464    Top = 72  end  object ClientDataSet1: TClientDataSet    Aggregates = <>    Params = <>    ProviderName = 'DataSetProvider1'    Left = 512    Top = 72    object ClientDataSet1TT_FORMSETTINGS_ID: TIntegerField      FieldName = 'TT_FORMSETTINGS_ID'      Required = True    end    object ClientDataSet1TT_EMP_ID: TIntegerField      FieldName = 'TT_EMP_ID'    end    object ClientDataSet1TT_FORM: TStringField      FieldName = 'TT_FORM'      Size = 50    end    object ClientDataSet1TT_VERSION: TIntegerField      FieldName = 'TT_VERSION'    end    object ClientDataSet1TT_VIEWDATA: TMemoField      FieldName = 'TT_VIEWDATA'      BlobType = ftWideMemo    end  end  object FDConnection2: TFDConnection    Params.Strings = (      'Database=test'      'Password=test'      'User_Name=test'      'Server=VS2003-2008'      'DriverID=MSSQL')    FormatOptions.AssignedValues = [fvMapRules]    FormatOptions.OwnMapRules = True    FormatOptions.MapRules = <      item        SourceDataType = dtDateTimeStamp        TargetDataType = dtDateTime      end>    Connected = True    LoginPrompt = False    Left = 312    Top = 144  end  object FDQuery2: TFDQuery    Connection = FDConnection2    SQL.Strings = (      'select * from tt_formsettings')    Left = 416    Top = 144  end  object DataSetProvider2: TDataSetProvider    DataSet = FDQuery2    Left = 464    Top = 144  end  object ClientDataSet2: TClientDataSet    Aggregates = <>    Params = <>    ProviderName = 'DataSetProvider2'    Left = 512    Top = 144    object ClientDataSet2TT_FORMSETTINGS_ID: TIntegerField      FieldName = 'TT_FORMSETTINGS_ID'      Required = True    end    object ClientDataSet2TT_EMP_ID: TIntegerField      FieldName = 'TT_EMP_ID'    end    object ClientDataSet2TT_FORM: TStringField      FieldName = 'TT_FORM'      Size = 50    end    object ClientDataSet2TT_VERSION: TIntegerField      FieldName = 'TT_VERSION'    end    object ClientDataSet2TT_VIEWDATA: TMemoField      FieldName = 'TT_VIEWDATA'      BlobType = ftMemo    end  end  object FDConnection0: TFDConnection    Params.Strings = (      'Database=D:\Testing\Diverse\FireDacOracleBlob\TIMETELL_DEMO.GDB'      'User_Name=SYSDBA'      'Password=masterkey'      'DriverID=IB')    FormatOptions.AssignedValues = [fvMapRules]    FormatOptions.OwnMapRules = True    FormatOptions.MapRules = <      item        SourceDataType = dtDateTimeStamp        TargetDataType = dtDateTime      end      item        SourceDataType = dtSingle        TargetDataType = dtDouble      end>    Connected = True    LoginPrompt = False    Left = 312    Top = 8  end  object FDQuery0: TFDQuery    Connection = FDConnection0    SQL.Strings = (      'select * from tt_formsettings')    Left = 416    Top = 8  end  object DataSetProvider0: TDataSetProvider    DataSet = FDQuery0    Left = 464    Top = 8  end  object ClientDataSet0: TClientDataSet    Aggregates = <>    Params = <>    ProviderName = 'DataSetProvider0'    Left = 512    Top = 8    object ClientDataSet0TT_FORMSETTINGS_ID: TIntegerField      FieldName = 'TT_FORMSETTINGS_ID'      Required = True    end    object ClientDataSet0TT_EMP_ID: TIntegerField      FieldName = 'TT_EMP_ID'    end    object ClientDataSet0TT_FORM: TStringField      FieldName = 'TT_FORM'      Size = 50    end    object ClientDataSet0TT_VERSION: TIntegerField      FieldName = 'TT_VERSION'    end    object ClientDataSet0TT_VIEWDATA: TMemoField      FieldName = 'TT_VIEWDATA'      BlobType = ftMemo    end  end  object FDPhysMSSQLDriverLink1: TFDPhysMSSQLDriverLink    Left = 368    Top = 144  end  object FDPhysFBDriverLink1: TFDPhysFBDriverLink    Left = 368    Top = 8  endend

Note: The fact that the Parameter assignment now (also) works is in the Data Type Mapping (FireDAC) documentation:

In case of a result set column, each rule defines a transformation of a source data type, returned by a driver, into a target one, preferred by an application. In case of a command parameter, the rule defines a transformation of a target data type, specified by an application, into a source data type, supported by a driver. All rules, excluding the name-based ones, work bidirectionally for both cases.