Skip site navigation (1) Skip section navigation (2)

Re: Synthesize support for Statement.getGeneratedKeys()?

From: Kris Jurka <books(at)ejurka(dot)com>
To: Ken Johanson <pg-user(at)kensystem(dot)com>
Cc: Kris Jurka <jurka(at)ejurka(dot)com>, pgsql-jdbc(at)postgresql(dot)org, JP Liska <jp(at)stsa(dot)info>
Subject: Re: Synthesize support for Statement.getGeneratedKeys()?
Date: 2007-12-13 16:49:18
Message-ID: Pine.BSO.4.64.0712131139540.28251@leary.csoft.net (view raw or flat)
Thread:
Lists: pgsql-generalpgsql-jdbc

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

In response to

Responses

pgsql-jdbc by date

Next:From: dmpDate: 2007-12-13 18:11:46
Subject: Re: how to set a PreparedStatement column of XML type in 8.3?
Previous:From: Kris JurkaDate: 2007-12-13 16:36:18
Subject: Re: JDBC causing the connection to close after executing a COPY command

pgsql-general by date

Next:From: Richard HuxtonDate: 2007-12-13 16:49:19
Subject: Re: UNION not working... why?
Previous:From: Stefan SchwarzerDate: 2007-12-13 16:41:40
Subject: UNION not working... why?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group