Subject Re: [IBO] escaping apostrophes
Author steve_carter_ben
Thanks for your detailed response.
You were right that the problem was elsewhere in my code and not
related to the apostrophe. I had not set the record id parameter
correctly, so, as you suggested, the update was simply not finding
a record to update.

Once I set the id correctly, an update works with an apostrophe in
the value, without my having to escape it.
I'm using an IB_StoredProc component. Does it handle the escaping
for me?

I'm doing something like the following, and, now that I'm setting
the id correctly (duh!), it handles apostrophes:
Sproc := DATA.Data_Module.Tunes_StoredProc;
Sproc.StoredProcName := 'update_tune';
Sproc.ParamByName( 'id').AsInteger := Id;
Sproc.ParamByName( 'title').AsString := Title_Edit.Text;

That is, I do not need to escape a single quote with a second single
quote. It handles Boplicity as well as Billy's Bounce.

If I do something like you suggested:
Your suggestion was:
> If Edit1.Text <> '' then
> begin
> InputString := QuotedStr(Edit1.Text);
> MyDataset.ParamByName('aName').AsString := InputString;
> end
> else
> MyDataset.ParamByName('aName').Clear;

in the Delphi code, it inserts single quotes. So the value in the
dababase becomes 'Boplicity' *with* the quotes! Or 'Billy''s Bounce
Not what I want.

So it appears that the IB_StoredSproc does handle the escaping for
me, and I think I'll use the simpler code, not worrying about
whether a value contains an apostrophe.


--- In, Helen Borrie <helebor@t...> wrote:
> At 09:13 PM 23/06/2005 +0000, you wrote:
> >I've read the posts about escaping apostrophes. But I can't seem
> >get it to work. I'm using Delphi 5. I've tried QuotedStr and even
> >AnsiQuotedStr. I've even written code to insert the two single
> You should insert *one* single quote to escape another. It's not
> here what you tried...
> >I use this in an update storede procedure and it fails silently,
> >exception.
> SP's with invalid statements in them don't fail silently unless
you code
> them that way. It must have "succeeded" somehow in the sense that
the SP
> received a valid argument and didn't find a match...something like
> QuotedStr of course is a Delphi function: SP's don't have a
> function available.
> >I have a last_updated field and trigger, and it shows the
> >record was not updated.
> If, for example, the statement was something like
> ...
> where aColumn = :aName
> and the parameter received was, e.g. 'O' when you intended it to
> 'O'Reilly' then no update would occur. That gives a clue to where
> are going wrong, though, i.e. it must be on the client side, not
at the
> interface.
> For this statement, the following syntax should take care of
> any apostrophes that come through from e.g. an edit box:
> If Edit1.Text <> '' then
> begin
> InputString := QuotedStr(Edit1.Text);
> MyDataset.ParamByName('aName').AsString := InputString;
> end
> else
> MyDataset.ParamByName('aName').Clear;
> >If I do something in IBExpert or some other tool like SET title
> >= 'Joe''s' it works. Why does the Delphi code not work?
> It does work if QuotedStr is used correctly. IBExpert is written
> Delphi! So is IB_SQL, where the tool itself takes care of
> apostrophes for you.
> The trick will be to find out just what is happening in your
> assignment code and correcting that.
> >By the way, I had a similar problem with Delphi, the BDE, and
> >was hoping Firebird and IBObjects would not have this problem, and
> >from what I've read here, it shouldn't! Does the CharSet of the
> >database have anything to do with this, maybe?
> For one-byte charactersets, no. If you are using a one-byte ANSI
> set, it would be wise to use AnsiQuotedStr. For multi-byte, it
might or
> might not work - that depends on the character set itself and
whether the
> mapping of the single-quote character is Ansi-standard. Delphi
> out-of-the-box does multi-byte character sets very poorly. It
does not,
> for example, map Unicode_FSS characters correctly.
> But, before making too many assumptions about the reasons why you
> the same problem in several different contexts, let's take a look
at your
> code, to see what you have tried so far that doesn't seem to work
> Helen