At 01:26 AM 8/10/2004 +0000, you wrote:

>Hi, this is a very basic question which I should know the answer to... :-[
>
>A normal insert statement is something like:
> insert into MyTable (SomeString) values ('The house of John')
>
>How do you do the insert when there's an apostrophe in the string (eg
>"John's House")? I can't write:
> ...values ('John's House')
>Because the apostrophe in the middle stuffs things. I think in
>SQL-server you can use a double (two) apostrophe, but that's probably
>a SQL-server hack?

No, for once it's standard. It's what you do in Firebird, too.-
...values('John''s House')

>You can't put the String in double quotes, eg:
> ...values ("Pineapple Drink")
>(ignoring the apostrophe in that case) because Firebird says column
>unknown: "Pineapple Drink".

Correct. Double quotes are reserved as delimiters for identifiers.

>(Is this particular to Firebird, or the explicitly the behaviour
>recommended by the SQL standard?)

The doubled apostrophe is the SQL standard.

>Sorry, did a google search - there are countless sites describing the
>insert statement and syntax, but in none of them did I see
>_explicitly_ mentioned that single quotes only are accepted (although
>they all show single quotes in the syntax).

Single quotes are the SQL standard for delimiting strings.

>This seems like such a newbie question :-)
>[In all current cases where we have single quotes within Strings, we
>just happen to be using prepared statements, and you can simply pass
>any String - including those containing single quotes - to
>PreparedStatement.setString(...)].

I don't know what interface you are using: perhaps it is hiding the
necessary parsing from you.

In ObjectPascal, the function QuotedStr() needs to be used whenever an
application must be prepared to accept literal apostrophes. So, for
example, if I have a statement like this:

select * from pubs where pub_name = :pub_name

and I do this:

ParamByName('pub_name').AsString := 'O'Shaughnessey's';

then I'm going to get an exception. This, however, works:

ParamByName('pub_name').AsString := QuotedStr('O'Shaughnessey's');

Otherwise, I have to do:

ParamByName('pub_name').AsString := 'O''Shaughnessey''s';

Actually, the short answer here would have been simply to refer you to Page
16 of the Firebird Quick Start Guide, to the article entitled "Apostrophes
in Strings". You can download this from the Firebird main website...

./heLen