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
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 |