Hello Myles,

>I have a pretty complex stored procedure that handles a variety of
> queries against a table, and the WHERE criteria of the stored procedure
> is passed into it as a varchar argument.
>
> I have variables set for limiting the number of rows returned for
> pagination, along with the WHERE and ORDER BY parts.
>
> I use the FOR EXECUTE STATEMENT command to construct the query like so:
>
> for execute statement
> 'SELECT ' || V_LIMIT_PART || ' DISTINCT
> CONTACT.C_ID,
> CONTACT.FULL_NAME,
> CONTACT.COLLEGE,
> C1.COUNTRY_NAME,
> CONTACT.PHONE1,
> CONTACT.EMAIL_ADDRESS,
> STATUS_TYPE.ST_DESC,
> CONTACT.FK_STATUS_ID
> FROM CONTACT
> LEFT OUTER JOIN STATUS_TYPE ON (STATUS_TYPE.ST_ID =
> CONTACT.FK_STATUS_ID)
> LEFT OUTER JOIN COUNTRY C1 ON (COUNTRY.COUNTRY_ID =
> CONTACT.FK_COUNTRY_ID)
> ' ||
> V_WHERE_PART || '
> ' || V_ORDER_PART
> into
> :R_ID,
> :R_FULLNAME,
> :R_COLLEGE,
> :R_COUNTRY,
> :R_PHONE1,
> :R_EMAIL,
> :R_STATUS,
> :R_STATUS_ID
> do
> begin
> suspend;
> end
>
> Its working perfectly in 99% of all cases. However the problem with it
> is when the user wishes to query on a name containing a quotation (ie.
> O'Reilly). In those cases, I cannot find a simple way to construct the
> incoming query string to include the quotation.
>
> For example, a typical query value passed in, that becomes the
> V_WHERE_PART variable would look like this:
>
> FK_STATUS_ID IS NOT NULL AND (SRCH_LAST_NAME = ''OREILLY'' )

String values should be enclosed in single quotes, in Delphi, you
can easily escape single quotes with two single quotes by using
the QuotedStr function.

O'Reilly then becomes:

'O''Reilly'

perfectly valid (note that the '' = two single quotes).

> Note that I pass in double quotations around the value. This is handled
> fine when concatenated with the EXECUTE STATEMENT string. However when
> I need to include the single quotation mark in the name O'Reilly, then
> it fails. I've tried using double quotations but all that does is
> prematurely end the string.
>
> How can I do this with any value that contains a quotation in the
> content? I've tried replacing it with some unusual string, and doing a
> string replace on the incoming value before passing it to the EXECUTE
> STATEMENT for concatenation, but this doesn't solve anything since the
> problem is the use of quotations in the resulting string that is executed.
>
> If anyone has an idea for dealing with this, I'm all ears. Thanks in
> advance for anyone's time looking at this.

With regards,

Martijn Tonies
Upscene Productions
http://www.upscene.com

Download Database Workbench for Oracle, MS SQL Server, Sybase SQL
Anywhere, MySQL, InterBase, NexusDB and Firebird!

Database questions? Check the forum:
http://www.databasedevelopmentforum.com