FireDAC查询将SQL语句中的特殊字符删除到SQL Server [英] FireDAC Query dropping special characters in SQL statement to SQL Server

问题描述

我已经在Delphi 10.3中使用FireDAC和EMS Rad Server反复打开此问题.我没有在Delphi 10.2或更低版本中体验过它，但是除了在Delphi 10.3中，我没有在其他地方使用FireDAC.我遇到的问题是某些特殊字符在到达数据库之前似乎已从SQL语句中删除.

例如，如果我运行:

 更新消息阅读集MessageDeliveredDateTime ='8/11/2020 6:33:45 PM'其中messageread.dts位于('5/7/2020 12:48:20 PM-！+ [[[786'，'5/7/2020 12:47:06 PM-！#[[782'，'5/7/2020 12:43:35 PM-& K [[775'，'5/7/2020 12:41:01 PM-& K [[773') 

在SQL Server上执行的操作是:

  update messageread set MessageDeliveredDateTime ='8/11/2020 6:33:45 PM'其中messageread.dts位于('5/7/2020 12:48:20 PM-+ [[786'，'5/7/2020 12:47:06 PM-[[773') 

它似乎在-"之后删除了2个字符.因为它是2个字符，所以使我成为一些Unicode东西.过去，我通过使用参数化查询来解决此问题，但在这种情况下，它仍然无济于事.我当前正在运行的Delphi代码是:

  fdTemp.SQL.Text:='更新messageread集合MessageDeliveredDateTime ='+ QuotedStr(DateTimeToStr(now))+'where messageread.dts in('+ sUpdateDTS +')';fdTemp.ExecSQL; 

  sUpdateDTS ='5/7/2020 12:48:20 PM-！+ [[786'，'5/7/2020 12:47:06 PM-！#[[782'，'5/7/2020 12:43:35 PM-& K [[775'，'5/7/2020 12:41:01 PM-& K [[773' 

其中fdTemp是TFDQuery，而DTS是表的主键.如果我使用SQL语句并在Mgt Studio中运行它，那么它就可以正常工作.但是，从Delphi运行时，将影响0行，因为没有任何匹配where子句的内容.

有人有什么主意吗?

解决方案

SQL命令中的某些字符在FireDAC中具有特殊含义，因此必须以特殊方式输入.在您的情况下，以&开头的标识符被视为宏.

您可以通过将 ResourceOptions.MacroCreate 设置为false来抑制这种情况.

有关FireDAC中特殊字符处理的更多信息，请参见文档:特殊字符处理

I have hit this issue off and on in Delphi 10.3 using FireDAC and the EMS Rad Server. I have not experienced it in Delphi 10.2 or below, but I am not using FireDAC anywhere but in Delphi 10.3. The issue I am experiencing is some special characters seem to be getting stripped out of the SQL statements before they reach the Database.

For example, if I run:

update messageread set
MessageDeliveredDateTime = '8/11/2020 6:33:45 PM'
where messageread.dts in ('5/7/2020 12:48:20 PM-!+[[786',   '5/7/2020 12:47:06 PM-!#[[782', '5/7/2020 12:43:35 PM-&K[[775', '5/7/2020 12:41:01 PM-&K[[773')

what gets executed on the SQL server is:

update messageread set MessageDeliveredDateTime = '8/11/2020 6:33:45 PM' where messageread.dts in ('5/7/2020 12:48:20 PM-+[[786',   '5/7/2020 12:47:06 PM-[[782',   '5/7/2020 12:43:35 PM-[[775',   '5/7/2020 12:41:01 PM-[[773')

It appears to be dropping 2 characters after the "-". Because it is 2 characters, it makes me thing its some Unicode thing. In the past, I have worked around this by using parameterized queries, but in this case, it still isn't helping. The Delphi code that I am currently running is:

fdTemp.SQL.Text := 'update messageread set MessageDeliveredDateTime = ' + QuotedStr(DateTimeToStr(now)) + ' where messageread.dts in (' + sUpdateDTS + ')';
fdTemp.ExecSQL;

sUpdateDTS = '5/7/2020 12:48:20 PM-!+[[786',    '5/7/2020 12:47:06 PM-!#[[782', '5/7/2020 12:43:35 PM-&K[[775', '5/7/2020 12:41:01 PM-&K[[773'

Where fdTemp is TFDQuery and DTS is the primary key of the table. If I take the SQL statement and run it in Mgt Studio, it works just fine. However when run from Delphi, 0 rows are affected because nothing matches the where clause.

Does anyone have any idea?

解决方案

Some characters in SQL commands have special meaning in FireDAC and thus have to be entered in a special way. In your case an identifier starting with & is treated as a macro.

You can suppress that by setting ResourceOptions.MacroCreate to false.

More info on special character handling in FireDAC can be found in the documentation: Special Character Processing

这篇关于FireDAC查询将SQL语句中的特殊字符删除到SQL Server的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！