Обсуждение: Error while returning auto-generated values

Hello,
I'm trying to execute an insert statement against the following table, in which subscriber_id is auto incremented.

subscriber_id |  user_id   | tenant_id | email_address | date_subscribed
---------------+------------+-----------+---------------+-----------------
             1 | admin1234  |     -1234 |               | 1970-01-01
             3 | admin12345 |     -1234 |               | 1970-01-01

Table was created using the following statement:

CREATE TABLE AM_SUBSCRIBER2 (
    SUBSCRIBER_ID INTEGER DEFAULT nextval('am_subscriber_sequence'),
    USER_ID VARCHAR(50) NOT NULL,
    TENANT_ID INTEGER NOT NULL,
    EMAIL_ADDRESS VARCHAR(256) NULL,
    DATE_SUBSCRIBED DATE NOT NULL,
    PRIMARY KEY (SUBSCRIBER_ID),
    UNIQUE (TENANT_ID,USER_ID));

The insert query gets executed through a PreparedStatement, which returns the assigned subscriber_id for the newly inserted row.

String query = "INSERT" +
                " INTO AM_SUBSCRIBER (USER_ID, TENANT_ID, EMAIL_ADDRESS, DATE_SUBSCRIBED)" +
                " VALUES (?,?,?,?)";
        PreparedStatement ps = conn.prepareStatement(query, new String[] {"SUBSCRIBER_ID"});
        ps.setString(1, "admin1");
        ps.setInt(2, -1234);
        ps.setString(3, "");
        ps.setTimestamp(4, new Timestamp(0));
        ps.executeUpdate();

When executing the above cord segment I'm getting an SQLException saying that the "SUBSCRIBER_ID" doesn't exist.

org.postgresql.util.PSQLException: ERROR: column "SUBSCRIBER_ID" does not exist
  Position: 128
    at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2157)
    at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1886)
    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:255)
    at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:555)
    at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:417)
    at org.postgresql.jdbc2.AbstractJdbc2Statement.executeUpdate(AbstractJdbc2Statement.java:363)
    at ConnectionTest.TestAddSubscriber(ConnectionTest.java:48).

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.
This problem occurred when trying to use a Postgre db where initially it had been using a MySql db.

Is it possible to solve this problem by changing the DDL statement or any connection parameters?

Regards,
Amila

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.

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

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

On Tue, 13 Nov 2012, Amila De Silva wrote:

> 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 problem is that the quoting and downcasing rules are clear for SQL
identifiers in SQL strings, but they are not the same as the JDBC API.
For the JDBC API, there are many calls that take table and column names
that do not have the same rules.  For example, DatabaseMetaData.getTables.


http://docs.oracle.com/javase/6/docs/api/java/sql/DatabaseMetaData.html#getTables%28java.lang.String,%20java.lang.String,%20java.lang.String,%20java.lang.String[]%29

getTables' Javadoc says, "tableNamePattern - a table name pattern; must
match the table name as it is stored in the database"  Which implies that
it must be provided to getTables downcased to match the downcasing
Postgres did to an unquoted identifier.

I understand what you want to happen, but it is not simple in the general
case.  Consider the following table:

kjurka=# create table trouble (subscriber_id int, "SUBSCRIBER_ID"
int, """SUBSCRIBER_ID""" int);
CREATE TABLE
kjurka=# \d trouble
       Table "public.trouble"
     Column      |  Type   | Modifiers
-----------------+---------+-----------
 subscriber_id   | integer |
 SUBSCRIBER_ID   | integer |
 "SUBSCRIBER_ID" | integer |

Now you've got a real problem and you have to have a clear rule for what
values must be provided to the JDBC API to get which columns returned.
What the postgresql JDBC driver does is match the getTables API, you must
provide a value to the API matching that stored in the database.


Kris Jurka