在基础查询中使用REPLACE打开TClientDataSet时内存不足 [英] Insufficient memory when opening TClientDataSet with a REPLACE in the underlying query

问题描述

我的Delphi代码打开TFDQuery(FireDAC)，然后打开通过TDataSetProvider连接到它的TClientDataSet:

My Delphi code opens a TFDQuery (FireDAC), then opens the TClientDataSet connected to it via a TDataSetProvider:

ClientDataSetData.Close;
with QueryData do
begin
  Close;
  SQL.Clear;
  SQL.Add(ASelectSQL);
  Open;
end;    
ClientDataSetData.Open;

ASelectSQL包含以下SQL:

SELECT TT_NIV_ID,
TT_NIV,
REPLACE(TT_NIV_NAME, '|', '!') as TT_NIV_NAME2
FROM TT_SYS_PRJ_NIV

ClientDataSetData.Open在具有42200条记录的数据集上给出的内存错误不足.

The ClientDataSetData.Open gives an insufficient memory error on a dataset with 42200 records.

如果检查结果数据(在Delphi代码中)，则会发现TT_NIV_NAME2是长度为8000的字符串！
从 REPLACE()文档:

If I inspect the result data (in the Delphi code) I see that TT_NIV_NAME2 is a string of length 8000!
From the REPLACE() documentation:

如果string_expression不是varchar(max)或nvarchar(max)类型， REPLACE将返回值截断为8,000个字节

If string_expression is not of type varchar(max) or nvarchar(max), REPLACE truncates the return value at 8,000 bytes

...所以这似乎正在发生；不仅可以截断，还可以设置结果类型.

... so this seems to be happening; not just truncating but also setting the result type.

TT_NIV_NAME是VARCHAR(50)，因此快速解决方案是将SQL更改为

TT_NIV_NAME is a VARCHAR(50), so a quick fix is to change the SQL to

SELECT TT_NIV_ID,
TT_NIV,
CAST(REPLACE(TT_NIV_NAME, '|', '!') as VARCHAR(50))  as TT_NIV_NAME2
FROM TT_SYS_PRJ_NIV

但是这是我无法控制的用户定义的SQL.下次，他可能会在其他内容上使用REPLACE运行...我希望使用通用解决方案.

but this is with user defined SQL that I have no control over. Next time (s)he may run a with a REPLACE on something else... I'd prefer a generic solution.

我可以对Delphi代码做任何事情(TClientDataset或TFDQuery或TFDConnection设置?)来防止这种高内存使用吗?

Is there anything I can do to the Delphi code (TClientDataset or TFDQuery or TFDConnection settings?) that prevents this high memory usage?

坦率地说，当TT_NIV_NAME是VARCHAR(50)时，为什么REPLACE会得出字符串表达式不是varchar(max)类型"的结论?

And frankly, why would REPLACE conclude that "string_expression is not of type varchar(max)", when TT_NIV_NAME is a VARCHAR(50)?

注意:

• 已使用多个SQL Server驱动程序进行了测试-这不是驱动程序问题.
• TFDConnection具有FetchOptions.Mode := fmAll和FetchOptions.Items := FetchOptions.Items - [fiMeta];
  fmAll必须保持 ^* ，并且不减去fiMeta没什么区别.
• 相关 [1] ，但在这里string_expression大于8000，或

• Tested with several SQL server drivers - it's not a driver issue.
• The TFDConnection has FetchOptions.Mode := fmAll and FetchOptions.Items := FetchOptions.Items - [fiMeta];
  fmAll must stay^*, and not subtracting fiMeta makes no difference.
• Related [1] but here string_expression is longer than 8000, or [2] where string_expression is not of type varchar(max) but both do not apply to my SQL data.
• Delphi Tokyo 10.2.3 using FireDAC against SQL Server 2012, Win32 app running under Win7 or Win10

^{* ..以防止在仅安装了非常基本的SQL Server驱动程序的环境中发生著名的hstmt错误；而未设置它在我的测试应用中仍然没有影响.}

推荐答案

我们已通过专门为此8000个字符的ANSIstring应用映射规则来解决"此问题.我知道，它很脏，但是对于旧版应用程序却可以工作(特别是因为之前我们使用 SQLDirect 切换到FireDAC，并且已经将大于256个字节的varchars转换为备忘录了.

We have 'solved' this by applying a mapping rule specifically for this 8000 character ANSIstring. I know, it's dirty, but for the legacy app it works (especially since we were using SQLDirect before switching to FireDAC, and there varchars > 256 bytes were translated to memo already).

with FDConnection.FormatOptions.MapRules.Add do
begin
  SourceDataType := dtAnsiString;
  SizeMin := 8000;
  SizeMax := 8000;
  TargetDataType := dtMemo;
end;

此映射特定于SQL Server，因为我们支持的其他两种数据库类型(Oracle，FireBird)中的REPLACE函数没有SQL Server行为.

This mapping is specific to SQL Server, because the REPLACE function in the other two database types we support (Oracle, FireBird) does not have the SQL Server behavior.

这篇关于在基础查询中使用REPLACE打开TClientDataSet时内存不足的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！