FireDAC映射规则不适用于参数吗? [英] FireDAC mapping rules do not apply to parameters?

问题描述

我在要应用TFDConnection"rel =" nofollow noreferrer>数据类型映射，用于与以前的数据访问技术(SQLDirect)向后兼容:

I have a TFDConnection to a FireBird database for which I apply Data type mapping for backward compatibility with a previous data access technology (SQLDirect):

with FormatOptions.MapRules.Add do     // TIMESTAMP will be ftDateTime instead of ftTimeStamp
begin
   SourceDataType := dtDateTimeStamp;
   TargetDataType := dtDateTime;
end;
with FormatOptions.MapRules.Add do     // FLOAT will be ftFloat instead of ftSingle
begin
   SourceDataType := dtSingle;
   TargetDataType := dtDouble;
end;
FormatOptions.OwnMapRules := true;

在运行时，我创建一个链接到该TFDConnection的TFDQuery.
我可以看到它继承了映射规则:FormatOptions.MapRules.count=2

At runtime I create a TFDQuery that I link to that TFDConnection.
I can see that it inherits the mapping rules: FormatOptions.MapRules.count=2

我将INSERT查询分配给它的SQL.Text:

I assign an INSERT query to its SQL.Text:

insert into TT_ACT (TT_ACT_ID,TT_PARENT_ID,TT_FROMDATE,TT_TODATE,TT_NAME,TT_NR,TT_CODE,TT_GROUP...)
values (:TT_ACT_ID,:TT_PARENT_ID,:TT_FROMDATE,:TT_TODATE,:TT_NAME,:TT_NR,:TT_CODE,:TT_GROUP,...)

这给我params.count=42带有数据类型ftUnknown的参数(当然).

This gives me params.count=42 with parameters with datatype ftUnknown (of course).

然后我致电为查询做准备.

I then call Prepare for the query.

如果现在检查已知的datetime参数，则会看到params[x].datatype = ftTimeStamp，而不是ftDateTime. 因此，当查询返回数据库以查看字段时，在设置参数时似乎没有监听数据映射规则.

If I now inspect a known datetime parameter, I see params[x].datatype = ftTimeStamp, not ftDateTime. So when the query goes back to the database to look at the fields, it does not seem to listen to the data mapping rules when setting up the parameters.

这是一个错误吗?

在我的代码的后期，这使我陷入麻烦，并导致了著名的338错误:

In a later stage in my code this got me into trouble, resulting in the famous 338 error:

[FireDac][Phys][IB]-338 Param [TT_FROMDATE] type changed from [ftSQLTimeStamp] to [ftDateTime]. Query must be reprepared. 

我设法解决该错误，所以这不是问题的一部分.但是我希望Params也遵循数据类型映射规则，这会使所有这些事情变得更加容易.

I managed to work around that error, so that is not part of the question. But I would expect the Params to follow data type mapping rules as well, that would have made all this easier.

推荐答案

您只是错误地定义了映射规则定义.对于参数，它是目标到源的转换. 数据类型映射 主题表示好吧:

You just misdefined the mapping rule definitions. For parameters, it is transformation of target into source. The Data Type Mapping topic says that as well:

在使用命令参数的情况下，规则定义了 由应用程序指定的目标数据类型转换为源数据 类型，由驱动程序支持.

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.

因此要从 时间戳 到

So to map command parameters from TIMESTAMP to dtDateTime and FLOAT to dtDouble just swap source with target in your definition:

{ FLOAT → dtDouble in parameters }
with FormatOptions.MapRules.Add do
begin
  SourceDataType := dtDouble; { TFDParam.DataType }
  TargetDataType := dtSingle; { Firebird FLOAT }
end;
{ TIMESTAMP → dtDateTime in parameters }
with FormatOptions.MapRules.Add do
begin
  SourceDataType := dtDateTime; { TFDParam.DataType }
  TargetDataType := dtDateTimeStamp; { Firebird TIMESTAMP }
end;
{ enable custom map rules }
FormatOptions.OwnMapRules := True;

值得一提的是，参数映射规则是唯一要做的事情.它们仅在准备命令时映射参数的数据类型(数据类型必须是可确定的).他们没有在将参数值传递给驱动程序时对其进行转换.考虑以下代码:

It's worth adding that mapping rules for parameters do the only thing. They only map data types for parameters when command is being prepared (data types must be determinable for them). They're not converting parameter values as they are passed to the driver. Consider this code:

{ Firebird FLOAT equals to dtSingle data type, map it to dtDouble }
with FDQuery1.FormatOptions.MapRules.Add do
begin
  SourceDataType := dtDouble;
  TargetDataType := dtSingle;
end;
FDQuery1.FormatOptions.OwnMapRules := True;
{ setup the command; MyFloat field is Firebird FLOAT }
FDQuery1.SQL.Text := 'INSERT INTO MyTable (MyFloat) VALUES (:MyFloat)';
{ rules are applied when preparing command, so let's prepare it }
FDQuery1.Prepare;
{ now the parameter data type should be dtDouble instead of dtSingle }
if FDQuery1.ParamByName('MyFloat').DataType = dtDouble then
  ShowMessage('Parameter is of dtDouble data type');
{ but you can easily change the parameter data type to another, e.g. by mistake;
  this will change data type to dtSingle, so the whole mapping effort is lost }
FDQuery1.ParamByName('MyFloat').AsSingle := 1.2345;
{ if this would execute (which does not because the parameter data type has been
  changed since the command preparation), parameter map rules would still not be
  involved in converting parameter value for the driver }
FDQuery1.ExecSQL;

因此，正如您所看到的，几乎什么都不做(将确定的参数数据类型仅更改为另一种)非常费力.无论映射规则如何，参数值都会自动转换.因此，即使您的参数数据类型与DBMS数据类型不匹配但可以转换，FireDAC仍将为您进行转换，无论如何(此魔术位于

So as you can see, it's quite a lot of effort for almost nothing (changing determined parameter data type to another only). Parameter values are converted automatically regardless mapping rules. So, even if your parameter data type won't match DBMS data type but will be convertible, FireDAC will simply convert it for you no matter what (this magic is inside ConvertRawData method):

{ assume MyFloat FLOAT, MyTimeStamp TIMESTAMP in Firebird }
FDQuery1.SQL.Text := 'INSERT INTO MyTable (MyFloat, MyTimeStamp) VALUES (:MyFloat, :MyTimeStamp)';
{ setup parameter data types badly to be dtDouble and dtDateTime }
FDQuery1.ParamByName('MyFloat').AsFloat := 1.2345;
FDQuery1.ParamByName('MyTimeStamp').AsDateTime := Now;
{ and execute; parameter values will be converted automatically to DBMS data types
  dtDouble → dtSingle and dtDateTime → dtDateTimeStamp }
FDQuery1.ExecSQL;

因此，即使在这里我要重复一遍，参数集合也应该手动定义，而不是由DBMS从准备好的命令中定义(开发人员必须知道哪些值填充了哪些字段).

So even here I would repeat, that parameter collections should be defined manually rather than by the DBMS from a prepared command (developer must know what values fill into which fields).

这篇关于FireDAC映射规则不适用于参数吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！