String Literals and Statement Strings
The Dynamic SQL statements prepare and execute immediate both use statement strings that specify an SQL statement. The statement string can be specified by a string literal or character string variable, as in:
exec sql execute immediate 'drop employee';
or:
str := "drop employee";
exec sql execute immediate :str;
As with regular Embedded SQL string literals, the statement string delimiter is the single quote. However, single quotes embedded in statement strings must conform to the runtime rules of SQL when the statement is executed. For example, the following two dynamic insert statements are equivalent:
exec sql prepare s1 from
'insert into t1 values (''single''''double" '');
and:
str := "insert into t1 values ('single'' double"" ')";
exec sql prepare s1 from :str;
In fact, the string literal generated by the Embedded SQL/Ada preprocessor for the first example is identical to the string literal assigned to the variable "str" in the second example.
The runtime evaluation of the above statement string is:
insert into t1 values ('single''double" ')
As a general rule it is best to avoid using a string literal for a statement string whenever it may contain quotes. Instead you should build the statement string using the Ada language rules for string literals together with the SQL rules for the runtime evaluation of the string.