Re: Error while returning auto-generated values

Hi Craig,
Thanks for your quick response. I got some other questions to be clarified.

On Thu, Nov 8, 2012 at 1:42 PM, Craig Ringer <craig(at)2ndquadrant(dot)com> wrote:

> On 11/08/2012 04:00 PM, Amila De Silva wrote:
> >
> > Simply by changing the case of SUBSCRIBER_ID to lower case, in return
> > column array ,this error can be avoided. But the problem is that
> > change is not feasible.
>
> Yes, that's how it works. It's nothing to do with JDBC, you'll get the
> same result if you run the query directly in psql.
>
> "DOUBLE QUOTED" identifiers are case-preserving, while unquoted
> identifiers are downcased. This means that you must quote consistently -
> everywhere, or nowhere.
>

Actually there aren't any place we are using a mix of cases. In any of the
places, double quotes haven't been used to preserve the case of the
identifiers.

The detailed description of the problem is like this;
We have used uppercase for our ddl queries (without doublequotes).
According to the explanation, these are downcasted, and column names should
be created in lower case (which is correct of course).

In the same way our insert queries should be down casted, since quotes
aren't used at all. But the problem arises when defining return columns. In
the way it have behaved, the return column (which is the SUBSCRIBER_ID )
should also be downcasted since there hasn't been any double quote used to
preserve the case. But the problem is in return queries, case is preserved.

The failure occurs since it looks for a column name in uppercase where as
the actual return column is in lower case.

I've used Postgre 9.2.1 with the postgresql-9.2-1001.jdbc4 driver.

> PgJDBC does not have a parameter to control this behaviour because
> PostgreSQL does not have a parameter to control it. PgJDBC can hardly
> parse all your SQL and add or remove quoting - in fact, attempting to do
> so could be very dangerous and even lead to security vulnerabilities.
>
> > Is it possible to solve this problem by changing the DDL statement or
> > any connection parameters?
> Yes, you need to change the DDL so that it also "DOUBLE QUOTES"
> identifiers if they are "DOUBLE QUOTED" when used in queries.
>
> If your queries use a mix of unquoted and quoted values, or mix of
> cases, then you're going to have go to through and fix them all to be
> consistent.
>
> --
> Craig Ringer
>
>