At 02:52 AM 11/05/2007, you wrote:

>Hi
>
>V 1.5.3 dialect 1
>
>I have an SQL statement like
>
> --> select surname from patient where surname containing ''''
>
>works fine - returns the O'Rielly types.
>
>but in a stored procedure
>
>sql=' select surname from patient where surname containing ' ' ';
>(one pair of 's) (spaces inserted for clarity)
>or
>sql=' select surname from patient where surname containing ' ' ' ' ' ;
>(two pairs of 's)
>or
>sql=' select surname from patient where surname containing ' ' ' ' ' ' '
>; (three pairs of 's)
>or
>sql=' select surname from patient where surname containing " ' ' " ';
>(double ", pair of 's, double ")
>or
>sql=" select surname from patient where surname containing ' ' "; (whole
>thing using double " with single 's inside)
>
>for execute statement sql do . . . fails because non of the above are
>equivalent to -->
>
>is there a combination that should work?

Well, it's a curly one. The SQL string itself is a string, so you
have this "outer layer" pair to delimit it. Then, inside, you have
to escape not just the literal apostrophe in the search criteria, but
the string delimiters for the search argument itself. In your
example, you would need 7 apostrophes at the end:

sql=' select surname from patient where surname containing ''''''' ;

So, your third example should work (theoretically!). If it doesn't,
try assembling the SQL by concatenating the escaped sequences
individually, e.g.

sql=' select surname from patient where surname containing ' ;
sql = sql || '''' ; -- the opening single-quote delimiter for the argument
sql = sql || '''' ; -- the argument containing the apostrophe
sql = sql || '''' ; -- the closing single-quote delimiter for the argument

But, then, you might need to concatenate one more, in case the count
is left one short after the implicit closing of the outer delimiter
pair after the final concatenation (which I think would indicate a
hole in the implementation of EXECUTE STATEMENT...but one can try):
sql=sql || '''';

Helen