At 06:17 a.m. 16/10/2013, russell@... wrote:

>running inside Delphi if I use the query
>
>
>select * from person where email = :email
>
>if the email address is a@... then it is fine, but if the email contains an apostrophe a'oneill@b,com then it fails to locate the record. Even if I use a string replace rather than a parameter it fails.
>
>In FlameRobin
>
>select * from person where email = 'a''oneill@...'
>
>is fine.

Strange - I thought apostrophes and other diacritics were illegal characters in email addresses. Well, well!

If it's working in FR (which uses IBPP) then it is obviously Delphi that is mangling the apostrophe - possibly taking the first of your doubled apostrophes as the end of the string and so, for your example, passing

where email = 'a'

In Delphi I've always found that using QuotedStr to populate parameters and variables gets past these little temperamentals in the Delphi parser. What QuotedStr does is to treat everything inside the outer pair of apostrophes as literals, which is what you need here.

emailvar := QuotedStr ('a''oneill@...');

selectstr := 'select * from person where email = ' + emailvar;

Better still is to use parameters and a prepared SELECT statement, which your post suggests is what you are actually doing. Assign the value of emailvar to your 'EMAIL' parameter.


Helen Borrie, Support Consultant, IBPhoenix (Pacific)
Author of "The Firebird Book" and "The Firebird Book Second Edition"
http://www.firebird-books.net
__________________________________________________________________