Error while returning auto-generated values

From: Amila De Silva <amilad(at)wso2(dot)com>
To: pgsql-jdbc(at)postgresql(dot)org
Subject: Error while returning auto-generated values
Date: 2012-11-08 08:00:24
Message-ID: CAJcWjJHmimJDfirCsLaYrkxtO0FCxNAE25AW+4-LR7fOrA8u4Q@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-jdbc

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

Responses

Browse pgsql-jdbc by date

  From Date Subject
Next Message Craig Ringer 2012-11-08 08:12:59 Re: Error while returning auto-generated values
Previous Message dmp 2012-11-08 01:04:57 Re: Possible bug / regression from generated keys