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

Asked 6/12, 2017 at 11:17 Answered 20/12, 2017 at 11:28

Solved oracle delphi firedac delphi-10.2-tokyo

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.

Elizbeth answered 6/12, 2017 at 11:17 Comment(7)

Why do you describe parameters (by your FetchParams method)? It happened the same as here (see the middle part of the post). I bet if you remove your FetchParams call, you won't have any trouble. FireDAC automatically converts parameter values for the prepared statement. But it doesn't allow you to prepare the statement (which describes the parameters), change the parameter data types and execute the statement. – Stovall 6/12, 2017 at 16:11

@Stovall ?? If I don't do FetchParams there are no parameters and the Params.ParamByName assignments fail immediately. This is a client/server application with the TClientDataSet in the client and the TFDQuery/TDataSetProvider in the server. How else would the client know the params? – Elizbeth 7/12, 2017 at 7:20

FireDAC parses the query and create parameter objects for markers in the query (by default). I don't know your setup. From your question I just assumed that DataFormsettings is a TFDQuery and that you run commands that you've shown. – Stovall 7/12, 2017 at 7:28

Sorry, updated my question text to say that DataFormsettings is that TClientDataSet mentioned later – Elizbeth 7/12, 2017 at 7:36

Well as I understand this - and I'm not sure if that is the case for FireDAC -, varchar(50) (at least for MSSQL) is non-unicode. string on the Delphi side is unicode, so the assumption seems to be if a parameter is accessed with AsString unicode is used, data type is set to ftWideString. E.g. in Data.DB:

procedure TParam.SetAsString(const Value: string); begin   if FDataType <> ftFixedWideChar then FDataType := ftWideString;   Self.Value := Value; end;

. Does this make sense? Do you really need to have varchar fields? – Conley 7/12, 2017 at 15:36

@Conley Yes I need to have varchar fields - aren't these the most 'common' for MSSQL in a legacy app? – Elizbeth 11/12, 2017 at 11:2

Well that might be, I can't say, as we did switch front- and backend to unicode together. What I wanted to say is that you are accessing the fields and parameters as if they would represent what is the native string in Delphi, but in fact they aren't, as they are not unicode. I feel like you are mixing unicode and non-unicode here. Do the fields and parameters you use support AsAnsiString? – Conley 11/12, 2017 at 12:13

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.

Elizbeth answered 20/12, 2017 at 11:28 Comment(0)

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:

The Param type changed error on the Params value assignment
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;

interface

uses
  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
  end
end

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.

Elizbeth answered 11/12, 2017 at 9:55 Comment(0)

Hot tags

Godot Unity Godot Help Programming Godot 4.X GUI GDScript 3D 2D Physics CSharp Godot 3.X VR XR Projects C++

Recommended topics

Hot tags