delphi - Snappy DBExpress SQL 执行器方法

Question

鉴于以下情况

{------------------------------------------------------------------------------}
function TTestClass.NewQuery(const ASql : String) : TSqlQuery;
begin
  Result := TSqlQuery.Create(FConn);
  Result.SQLConnection := FConn;
  Result.Sql.Text := ASql;
  Result.Prepared := True;
end;

{------------------------------------------------------------------------------}
procedure TTestClass.ExecuteSql(const ASql : String);
begin
  with NewQuery(ASql) do
  try
    ExecSql();
  finally
    Free;
  end;
end;

如何创建一个 ExecSql 方法来填充查询的参数？

我尝试了这个重载方法:

{------------------------------------------------------------------------------}
procedure TTestClass.ExecuteSql(const ASql : String; const ParamVals : Array Of Variant);
var
  i : integer;
  Qry : TSqlQuery;
begin
  Qry := NewQuery(ASql);
  with Qry do
  try
    for i := Low(ParamVals) to High(ParamVals) do
      Qry.Params[i].Value := ParamVals[i];
    ExecSql();
  finally
    Free;
  end;
end;

但我收到错误消息:

Project MyProj.exe raised exception class EDatabaseError with message 'No value for parameter 'SomeParam''.

观察 Parameter[0] 显然显示该值已设置 - 并且参数名称正如我所期望的那样。谁能建议我做错了什么？

我过去因使用“Array Of Variant”而受到批评 - 我想知道是否有更好的方法。

谢谢大家。

我发现了一些有趣的事情:

ParamByName('SomeParam').Value := 1234567;

产生相同的错误消息，而

ParamByName('SomeParam').AsInteger := 1234567;

没有。

我已经使用 DBExpress 多年了 - 我是否忘记了什么？

编辑

我想出了一个可行的方法，但我对此不满意；通过检查 VALUE 的变体类型，我设法得到了一些结果

{------------------------------------------------------------------------------}
procedure TMyTestCase.SetParamValues(const AQuery : TSqlQuery; const ParamVals : Array Of Variant);
var
  i : Integer;
begin
  for i := 0 to AQuery.Params.Count - 1 do begin
    case VarType(ParamVals[i]) of
      varEmpty  :    AQuery.Params[i].AsInteger  := VarNull;       //The variant is Unassigned.
      varNull  :     AQuery.Params[i].AsInteger  := VarNull;       //The variant is Null.
      varAny  :      AQuery.Params[i].AsInteger  := VarNull;       //Represents a Variant that can hold any value.
      varSmallint  : AQuery.Params[i].AsInteger  := ParamVals[i];  //16-bit signed integer (type Smallint in Delphi, short in C++).
      varInteger  :  AQuery.Params[i].AsInteger  := ParamVals[i];  //32-bit signed integer (type Integer in Delphi, int in C++).
      varSingle  :   AQuery.Params[i].AsFloat    := ParamVals[i];  //Single-precision floating-point value (type Single in Delphi, float in C++).
      varDouble  :   AQuery.Params[i].AsFloat    := ParamVals[i];  //Double-precision floating-point value (type double).
      varCurrency  : AQuery.Params[i].AsFloat    := ParamVals[i];  //Currency floating-point value (type Currency).
      varDate  :     AQuery.Params[i].AsDateTime := ParamVals[i];  //Date and time value (type TDateTime).
      varOleStr  :   AQuery.Params[i].AsString   := ParamVals[i];  //Reference to a dynamically allocated UNICODE string.
      varDispatch  : AQuery.Params[i].AsInteger  := VarNull;       //Reference to an Automation object (an IDispatch interface pointer).
      varError  :    AQuery.Params[i].AsInteger  := VarNull;       //Operating system error code.
      varBoolean  :  AQuery.Params[i].AsBoolean  := ParamVals[i];  //16-bit Boolean (type WordBool).
      varVariant  :  AQuery.Params[i].AsInteger  := VarNull;       //Indicates another variant.
      varUnknown  :  AQuery.Params[i].AsInteger  := VarNull;       //Reference to an unknown object (an IInterface or IUnknown interface pointer).
      varShortInt  : AQuery.Params[i].AsInteger  := ParamVals[i];  //8-bit signed integer (type ShortInt in Delphi or signed char in C++).
      varByte  :     AQuery.Params[i].AsInteger  := ParamVals[i];  //A Byte.
      varWord  :     AQuery.Params[i].AsInteger  := ParamVals[i];  //Unsigned 16-bit value (Word).
      varLongWord  : AQuery.Params[i].AsInteger  := ParamVals[i];  //Unsigned 32-bit value (type LongWord in Delphi or unsigned long in C++).
      varInt64  :    AQuery.Params[i].AsInteger  := ParamVals[i];  //64-bit signed integer (Int64 in Delphi or __int64 in C++).
      varStrArg  :   AQuery.Params[i].AsString   := ParamVals[i];  //COM-compatible string.
      varString  :   AQuery.Params[i].AsString   := ParamVals[i];  //Reference to a dynamically allocated string (not COM-compatible).
      varArray  :    AQuery.Params[i].AsInteger  := VarNull;       //Indicates a Variant array.
      varByRef  :    AQuery.Params[i].AsInteger  := VarNull;       //Indicates that the variant contains a reference as opposed to a value.
      varTypeMask:   AQuery.Params[i].AsInteger  := VarNull;       //
    end;
  end;
end;

我肯定错过了一个步骤 - 为什么查询参数没有类型？

再次编辑

我当前的“最佳”解决方案是依靠程序员提供正确的类型和值数量。我已经在上面发布了完整的 SetParamValues() 方法。这绝不是经过彻底测试的，但希望它会对某人有所帮助。

Answer 1

对此没有简单的解决方案

<小时/>

所有测试均使用 Delphi XE3 和 MySQL 进行

<小时/>

行为解释

在设计时，只要参数依赖于 FROM 表中的表字段且没有别名，您就会获得正确的参数数据类型

SELECT id FROM items WHERE id = :id

但如果不这样做也会失败

SELECT id FROM items WHERE id/2 = :id

这也会失败

SELECT i.* FROM items i WHERE i.id = :id

在运行时，两者都会产生数据类型为 ftUnknown 的参数。

参数在私有(private)方法中设置

// Delphi XE3
Data.SqlExpr.TCustomSQLDataSet.SetParameterFromSQL

在此方法中，仅提取表名

if csDesigning in ComponentState then

创建临时数据集

SELECT * FROM <tablename> WHERE 0 = 1

根据此数据集中的字段名称检查每个参数，如果名称匹配，则设置参数数据类型。

这就是为什么您在运行时得到参数 ftUnknown 的原因。

要解决这个问题，Delphi 会执行与您尝试的解决方案相同的操作，但 dbexpress 有时会失败。参数的 Setter 位于

Data.DB.TParam.SetAsVariant

并且值 1234567 具有变体类型 varLongword，并且参数数据类型将设置为 ftLongword 并导致此错误。

<小时/>

解决方法

作为解决方法，您可以将参数数据类型设置为 ftString/ftWideString，因为这适用于大多数情况。

procedure TTestClass.ExecuteSql(const ASql : String; const ParamVals : Array Of Variant);
var
  i : integer;
  Qry : TSqlQuery;
begin
  Qry := NewQuery(ASql);
  with Qry do
  try
    for i := Low(ParamVals) to High(ParamVals) do
    begin
      Qry.Params[i].DataType := ftWideString;
      Qry.Params[i].Value := ParamVals[i];
    end;
    ExecSql();
  finally
    Free;
  end;
end;

为了获得更好的解决方案，您可以获取一个过程，仅针对关键变体类型将参数数据类型设置为 ftString/ftWidestring(类似于您的方法 SetParamValues，但更通用)

procedure SetParamValues( const AParams : TParams; const AValues : array of Variant );
var
  LIdx : Integer;
  LParam : TParam;
  LValue : Variant;
begin
  for LIdx := 0 to Pred( AParams.Count ) do
  begin
    LParam := AParams[LIdx];
    LValue := AValues[LIdx];
    // only handle the critical parts
    case VarType( LValue ) of
      varByte, varLongword : LParam.DataType := ftWideString;
    end;
    // all other will be set here
    LParam.Value := LValue;
  end;
end;

<小时/>

解决方案:困难的方法

正如我首先指出的那样，没有简单的解决方案。对于完整的功能解决方案，您必须解析整个 WHERE 语句

SELECT a.*, b*
FROM table1 a
JOIN table2 b ON a.id = b.id
WHERE a.id = :id AND b.count / 2 = :halfcount

并据此构建查询

SELECT a.id as Param1, b.count / 2 as Param2
FROM table1 a
JOIN table2 b ON a.id = b.id
WHERE 0 = 1

获取预期的数据类型。

<小时/>

解决方案:漫长的道路

恕我直言，这是一个错误，应该报告给 EMBA ...

<小时/>

解决方案:昂贵的方式

我用UniDAC做了测试，一切都和dbExpress一样。参数数据类型为ftUnknown，设置参数值会将数据类型设置为ftLongword。

但有一种特殊情况:您不会收到错误，并且您的查询将按照您的预期进行处理。