德尔福 Firedac 甲骨文 : Raises Exception When Locating Primary key (VARCHAR or VARCHAR2)

Question

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) 示例

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;

当我调用 GetVal1 函数内部“Locate”时，会引发带有消息的异常

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'.

我尝试使用其他数据库(SQLite)重现此测试，没有像 Oracle 那样引发异常。

这是错误或我的代码中缺少某些内容。有人可以解释一下吗？

我做了一项研究并尝试追踪这个错误我进入了 FireDac.Comp.Client.pas 的可疑区域(德尔福东京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;

我该如何解决这个问题。

Answer 1

我们可以称其为错误。 FireDAC 可以在附加 % 字符时增加参数的限制大小。但是，在您的情况下，您似乎希望通过确切的术语而不是部分来查找记录。如果是这样，只需排除 loPartialKey来自您的旗帜 Locate方法调用。如果没有，您可以自己将搜索词修剪一个字符，作为一种快速的肮脏解决方法。