Delphi Firedac Oracle :查找主键(VARCHAR 或 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);

德尔福(拉德工作室(示例

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 函数内部"定位"时引发异常并带有消息

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的supicious block中 (德尔福东京 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 可以在附加%char 时增加参数的限制大小。但是，在您的情况下，您似乎希望按确切的术语而不是部分术语查找记录。如果是这样，只需从定位方法调用中排除loPartialKey标志即可。如果没有，您可以自己将搜索词修剪一个字符，作为一种快速的脏解决方法。