From c16a1bbcf498f0aa053a3e55008f57d7f67357dd Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Thu, 22 Oct 2020 18:29:40 -0400 Subject: Add documentation and tests for quote marks in ECPG literal queries. ECPG's PREPARE ... FROM and EXECUTE IMMEDIATE can optionally take the target query as a simple literal, rather than the more usual string-variable reference. This was previously documented as being a C string literal, but that's a lie in one critical respect: you can't write a data double quote as \" in such literals. That's because the lexer is in SQL mode at this point, so it'll parse double-quoted strings as SQL identifiers, within which backslash is not special, so \" ends the literal. I looked into making this work as documented, but getting the lexer to switch behaviors at just the right point is somewhere between very difficult and impossible. It's not really worth the trouble, because these cases are next to useless: if you have a fixed SQL statement to execute or prepare, you might as well write it as a direct EXEC SQL, saving the messiness of converting it into a string literal and gaining the opportunity for compile-time SQL syntax checking. Instead, let's just document (and test) the workaround of writing a double quote as an octal escape (\042) in such cases. There's no code behavioral change here, so in principle this could be back-patched, but it's such a niche case I doubt it's worth the trouble. Per report from 1250kv. Discussion: https://postgr.es/m/673825.1603223178@sss.pgh.pa.us --- doc/src/sgml/ecpg.sgml | 59 +++++++++++++++++++++++++++++++++++++++++++++----- 1 file changed, 54 insertions(+), 5 deletions(-) (limited to 'doc/src') diff --git a/doc/src/sgml/ecpg.sgml b/doc/src/sgml/ecpg.sgml index 419574e9ea6..14dcbdb4e32 100644 --- a/doc/src/sgml/ecpg.sgml +++ b/doc/src/sgml/ecpg.sgml @@ -7066,7 +7066,7 @@ EXECUTE IMMEDIATE string string - A literal C string or a host variable containing the SQL + A literal string or a host variable containing the SQL statement to be executed. @@ -7074,6 +7074,30 @@ EXECUTE IMMEDIATE string + + Notes + + + In typical usage, the string is a host + variable reference to a string containing a dynamically-constructed + SQL statement. The case of a literal string is not very useful; + you might as well just write the SQL statement directly, without + the extra typing of EXECUTE IMMEDIATE. + + + + If you do use a literal string, keep in mind that any double quotes + you might wish to include in the SQL statement must be written as + octal escapes (\042) not the usual C + idiom \". This is because the string is inside + an EXEC SQL section, so the ECPG lexer parses it + according to SQL rules not C rules. Any embedded backslashes will + later be handled according to C rules; but \" + causes an immediate syntax error because it is seen as ending the + literal. + + + Examples @@ -7388,7 +7412,7 @@ EXEC SQL OPEN :curname1; -PREPARE name FROM string +PREPARE prepared_name FROM string @@ -7421,15 +7445,40 @@ PREPARE name FROM string - A literal C string or a host variable containing a preparable - statement, one of the SELECT, INSERT, UPDATE, or - DELETE. + A literal string or a host variable containing a preparable + SQL statement, one of SELECT, INSERT, UPDATE, or DELETE. + Use question marks (?) for parameter values + to be supplied at execution. + + Notes + + + In typical usage, the string is a host + variable reference to a string containing a dynamically-constructed + SQL statement. The case of a literal string is not very useful; + you might as well just write a direct SQL PREPARE + statement. + + + + If you do use a literal string, keep in mind that any double quotes + you might wish to include in the SQL statement must be written as + octal escapes (\042) not the usual C + idiom \". This is because the string is inside + an EXEC SQL section, so the ECPG lexer parses it + according to SQL rules not C rules. Any embedded backslashes will + later be handled according to C rules; but \" + causes an immediate syntax error because it is seen as ending the + literal. + + + Examples -- cgit v1.2.3