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