| Subject | Re: Any UDF or Built in Finction to handle "escaping" single quotes in strings |
|---|---|
| Author | albreingan |
| Post date | 2008-08-25T23:05:50Z |
--- In firebird-support@yahoogroups.com, Helen Borrie <helebor@...> wrote:
*arrive* at the server with apostrophes pre-processed. It's not a UDF
or any server-side function you want, it's a function on the client
side. Think about it. ;-)
as you assign it to the parameter and it will take care of doubling
the apostrophes. If you're using some other client interface, look for
a function with similar effects.
Sasha your "replace" was exactly what I was looking for Why didn't I
think of that (please don't answer (:-)>) thank you.
To explain the problem in more detail (though it is now solved)
I was writing a procedure as a generic error handler so that (with a
capable client) triggers may replace the normal single exception at a
time with multiple error messages delivered at the end in response to
a generic exception.
Thus this procedure needs to be callable from within triggers and
procedures and must take two parameters (the exception code and
optionally the exception text message)
With a capable client all is (fairly) simple, but in the fall back
position exceptions must be sent, and the only way found (thank you
Dmitri) was to imbed a "exception" statement within a "execute block"
within a "execute statement" (a bit twisted but it works)
If the exception text contains a quote then it must be past to the
procedure "escaped" but the duplicates are stripped (in the parameter
handling?) and need to be re-escaped before the exception statement
string is made up.
>procedures, like substitutable params for other statements, have to
> At 18:35 25/08/2008, you wrote:
> >Is anyone aware of a simple way of escaping imbedded single quotes in
> >strings within a PSQL procedure / trigger (short of writing a UDF)? I
> >need to receive a string parameter which then will be processed via
> >an "execute statement" and "execute block" combination and so any
> >imbedded single quotes need to be duplicated.
>
> Execute Block doesn't belong in a procedure or trigger.
>
> >(version is Firebird 2.1)
> >
> >I haven't been able to find anything obvious though it's not the
> >easiest thing to search for.
>
> And you won't find such a thing. Input parameters for stored
*arrive* at the server with apostrophes pre-processed. It's not a UDF
or any server-side function you want, it's a function on the client
side. Think about it. ;-)
>If you're using Delphi, apply the QuotedStr() function to the string
> Escaping apostrophes embedded in strings is a standard SQL thing.
as you assign it to the parameter and it will take care of doubling
the apostrophes. If you're using some other client interface, look for
a function with similar effects.
>Thank you Sasha, Martijn and Helen for your replies.
> ./heLen
>
Sasha your "replace" was exactly what I was looking for Why didn't I
think of that (please don't answer (:-)>) thank you.
To explain the problem in more detail (though it is now solved)
I was writing a procedure as a generic error handler so that (with a
capable client) triggers may replace the normal single exception at a
time with multiple error messages delivered at the end in response to
a generic exception.
Thus this procedure needs to be callable from within triggers and
procedures and must take two parameters (the exception code and
optionally the exception text message)
With a capable client all is (fairly) simple, but in the fall back
position exceptions must be sent, and the only way found (thank you
Dmitri) was to imbed a "exception" statement within a "execute block"
within a "execute statement" (a bit twisted but it works)
If the exception text contains a quote then it must be past to the
procedure "escaped" but the duplicates are stripped (in the parameter
handling?) and need to be re-escaped before the exception statement
string is made up.