Error while returning auto-generated values
От | Amila De Silva |
---|---|
Тема | Error while returning auto-generated values |
Дата | |
Msg-id | CAJcWjJHmimJDfirCsLaYrkxtO0FCxNAE25AW+4-LR7fOrA8u4Q@mail.gmail.com обсуждение исходный текст |
Ответы |
Re: Error while returning auto-generated values
|
Список | 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
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
В списке pgsql-jdbc по дате отправления: