Ampersand in select statement returns empty dataset

Question

I have a string field in a Firebird database. The data in some rows contains an ampersand. I'm trying to select the rows but the result is an empty dataset.

Using Delphi 12, FireDAC (TFDConnection + TFDQuery) and Firebird.

Example: Field1 in table Table1 contains one row with value "M&T1234". I try to select this row with the statement

select * from Table1 where Field1 = 'M&T1234'

The result is an empty dataset.

I know I can use a parameterised query, and this does work, but for various reasons I need to stick with the query shown.

Interestingly, if there is a space after the ampersand then it works as expected. It also works from the SQL editor in FB Maestro.

I've tried escaping the ampersand without success. What is the correct way to specify the value for a field containing an ampersand?

• da-soft: docwiki.embarcadero.com/RADStudio/Athens/en/…
• user13964273: @da-soft You process them even in delimited strings. What's the purpose?
• Mark Rotteveel: This is not Firebird that does it, so it looks like it is FireDAC
• Mark Rotteveel: @user13964273 da-soft only points to the FireDAC documentation; it isn't their product.
• Tim: Thank you for your responses. As Remy suggested, turning off MacroExpand fixed the problem.

Answer 1

FireDAC preprocesses the raw SQL text before passing it to the DBMS for further processing. Sounds like the macro substitution is not aware of the SQL syntax, thus not affected by string quoting.

If you are not using FireDAC Macros, you can turn off the TFDQuery.ResourceOptions.MacroExpand property. Or disable all preprocessing by turning off the TFDQuery.ResourceOptions.PreprocessCmdText property.

Answer 2

It looks like your SQL tool is scanning for substitution variables. You can usually escape the ampersand with a backslash or turn off scanning. In GoldSqall this can be done in the options or by using a "set scan off;" command in script. Your tool probably has something similar.