Delphi Firedac Oracle : Raises Exception When Locating Primary key (VARCHAR or VARCHAR2)

Question

example table for oracle

   create table appval (
     id varchar2(4) not null primary key,
     val1 number(38,0) default 0,
     val2 number(5,2) default 0);

   insert into appval (id,val1) values ('1101', 1500000);
   insert into appval (id,val2) values ('1102', 2.5);

delphi (Rad Studio) example

type 
  TValHelper = class (TComponent)
  private
    FDataSet: TFDTable;
  protected
    procedure PrepareTable;
  public 
    constructor CreateHelper(AOwner: TComponent; var ATable: TFDTable); reintroduce;
    function GetVal1(AId: string): Currency;
    function GetVal2(AId: string): Double;
  end;

constructor CreateHelper(AOwner: TComponent; var ATable: TFDTable);
begin
  inherited Create(AOwner);
  if not Assigned(ATable) then
     Raise Exception.Create('Null Parameter passed.');

  FDataSet := ATable;
  PrepareTable;
end;

procedure TValHelper.PrepareTable;
begin
  if not FDataSet.Active then
  begin
    FDataSet.Connection := Dm.FDConnection;
    FDataSet.TableName := 'appval';
    FDataSet.Open;
  end;
end;

function TValHelper.GetVal1 (AId: string): Currency;
begin
  Result := 0; {default value if false}
  if AppVal.Locate('id', AId, 
    [loCaseInsensitive, loPartialKey]) then {<-- Exception}
    Result := AppNumVal.Fields[1].AsCurrency;
end;

function TValHelper.GetVal2 (AId: string): Double;
begin
  Result := 0; {default value if false}
  if AppVal.Locate('id', AId, 
    [loCaseInsensitive, loPartialKey]) then {<-- Exception}
    Result := AppNumVal.Fields[2].AsFloat;
end;

when i call GetVal1 function internal "Locate" raise exception with message

Exception class EFDException with message '[FireDAC][Phys][Ora]-345. 
Data too large for variable 
[:FD__LC_ID]. Max len = [4], actual len = [5] Hint: 
set the TFDParam.Size to a greater value'.

i try to reproduce this test with other DB(SQLite) no Exception raised like oracle do.

this was bug or someting missing in my code. can someone explain.

i do a research and try to trace this error i land into supicious block at FireDac.Comp.Client.pas (Delphi Tokyo 10.2)

{LINE 12690}
procedure TFDTable.FetchWindow
begin 
  {some code}
  {line 12769 i Set Watch at Command.CommandText.Text}
  Command.CommandText.Add(GenerateSQL);
  {Watch value : 
       'SELECT A.*, A.ROWID AS FD__ROWID'
       'FROM APPVAL A'
       'WHERE ({FN UCASE(A.ID)} LIKE {FN UCASE(:FD__LC_ID)})'
       'ORDER BY A.ID ASC'
       '{LIMIT(1)}'
     :F__LC_ID <-- is param with size 4

     until this
     everything goes fine.
  } 

  // check locate params {LINE 12785}
  else if IsPrefixed(oParam.Name, C_FD_CmdGenLocate, sField) then begin
    oParam.AssignFieldValue(FieldByName(sField), GetLocateRow.ValueS[sField]);
    // made LIKE compatible String
    if FTableParams.FLocatePartial and
       (oParam.DataType in [ftString, ftWideString, ftFixedChar, ftFixedWideChar]) then
      oParam.Value := VarToStr(oParam.Value) + '%'; {LINE 12791} 
      {
         something wrong with this oParam.Value Plus 1 char '%' 
         and i assume field has only 4 so if we increase the field size 
         manualy value will be padded with space before '%' so this will
         still produce exception (oParam.Value always greater +1).
      }
  end

end;

how can i resolve this.

Answer 1

We can call it a bug. FireDAC could for case when appending % char increase the limit size of the parameter. However, in your case it seems that you want to locate record by the exact term rather than partial. If that is so, simply exclude the loPartialKey flag from your Locate method call. If not, you can trim the search term by one char by yourself as a quick dirty workaround.