Subject Re: [IBO] Re: problem on record with ' sign
Author Helen Borrie
At 08:38 AM 9/05/2004 +0800, you wrote:

>This is what I got when posting a record with >'<
>ISC Error Code:335544569
>Dynamic SSQL Error
>SQL error code = -104
>Token unknown - line 3, char 19 %

James, show us the actual SQL that is failing. It looks more like your
error is coming from a wrong 'LIKE' search or perhaps from not escaping the
'%' sign.

To be clear, here is exactly how you escape an apostrophe. Let's say you
have a string like O'Flaherty's Pub for a column named HOSTELRY:

Update atable
set HOSTELRY = 'O''Flaherty''s Pub'

Those '' are pairs of ascii 39, not double-quotes.

Let's say instead you have a parameterised update:

Update atable

When assigning parameters, Delphi can actually help you:

Myquery.ParamByName('HOSTELRY').AsString := QuotedStr('O'Flaherty's Pub');

That is, Delphi will take care of "doubling" the apostrophes. It will pass
the correct string (exactly like the first example).

>Iam also using collate = NONE.

Don't use collate = NONE. It doesn't mean anything. Character set NONE
doesn't have any alternative collations, and other character sets don't
have a collate sequence called NONE.

>I have done a lot of testing. Not all
>have the same problem. There are some ib_dataset this problems occurs on
>all fields, but some it works fine. So I think there must be a problem
>but I don't have any idea where to start with it? Are there some
>settings i miss?

Can I suggest that you experiment using IB_SQL or isql. Make a table with
a varchar column into which you can insert a lot of experimental
apostrophised strings. Once you get the hang of it, you'll understand
exactly what you need to do in your application.

Also look up the Delphi help for QuotedStr. It will be helpful too.