Re: Synthesize support for Statement.getGeneratedKeys()?

On Wed, 5 Dec 2007, Ken Johanson wrote:

> I opted to use the Quoting mechanism I already had in executeUpdate for now,
> since the string validation (no 0x00 && no nested quotes) is being done in
> needsQuoted (in the same loop that validates quotes and scans for
> whitespace).
>
> -is whitespace the sole determinator for needing quoting? And other chars?

Any keywords would need quoting:  If you had a column named "user" it must
be quoted.

jurka=# create temp table zz(a int, "user" text);
CREATE TABLE

jurka=# insert into zz values(1,'a') returning a, user, "user";
  a | current_user | user
---+--------------+------
  1 | jurka        | a

> -is it fine to leave the string un-quoted if it contains no ws, vs always
> quoting it (my feeling is yes).

I was thinking about this some more and I think we should quote everything
regardless of whether it needs it or not.  This forces the caller to
provide the column in the correct case because it won't be folded any
more, but that's something we're already doing in DatabaseMetaData.  If we
don't do this there will be no way for the user to indicate that he has
case-sensitive column names.  (Unless of course we implemented
getGeneratedKeys with column names similar to how we might implemented it
for interger column indexes.  If we used RETURNING * and only did the
extraction once it got back to the driver, then we have some more
flexibility in handling names.)

> -is '"' the only legal quoting chars? (I cant remember for having dabbled
> with too many non-spec databases)

Yes.

> -my needsQuoted method throws if the identifier contains nested quotes
> (foo"bar or "foo"bar"); is there a legal quote-escaping mechanism similar to
> apostrophe doubling? eg: how or would one pass foo"bar (I imagine quotes are
> never allowed in identifiers but don't have an SQL spec handy)

Nested quotes are legal and escaped just like apostrophe doubling:

create table "abc""def" ( """" int);


> PS - Kris, I recall you said the backslashes in the patch were troublesome;
> did you find any fix for your patch tool aside from translating them to '/'?
> If not I will translate them from hereto forth.
>

I haven't looked at it since then.  Let's get another draft or two and
I'll see what needs to be done.

Kris Jurka