[JDBC] PreparedStatement and identity column

[Dov Moryusef]

Hi to all,

I’ve a table with an idendity column :

CREATE TABLE USRDB2.MYTABLE (
"ID" INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 0,
INCREMENT BY 1, NO CACHE),
"CODE" CHARACTER (3),
PRIMARY KEY (ID))IN MYTS01@

I would like to execute the following request (which works in a db2cmd):

db2 insert into usrdb2.mytable (id, code) values (DEFAULT, 'AD') @

with JDBBC:

String REQUEST = "insert into usrdb2.mytable (id, code) values (?, ?)";
PreparedStatement stmt = connection. prepareStatement(REQUEST);
stmt.setInt(1,...);
stmt.setString(2, 'AD');
stmt.execute();

but I don't know how. I got the error SQL0798N : A value cannot be
specified for column "ID" which is defined as GENERATED ALWAYS

Thanks a lot for your answers

Dov

[Knut Stolze]

Dov Moryusef wrote:

Hi to all,

I?ve a table with an idendity column :

CREATE TABLE USRDB2.MYTABLE (
"ID" INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 0,
INCREMENT BY 1, NO CACHE),
"CODE" CHARACTER (3),
PRIMARY KEY (ID))IN MYTS01@

I would like to execute the following request (which works in a db2cmd):

db2 insert into usrdb2.mytable (id, code) values (DEFAULT, 'AD') @

with JDBBC:

String REQUEST = "insert into usrdb2.mytable (id, code) values (?, ?)";
PreparedStatement stmt = connection. prepareStatement(REQUEST);
stmt.setInt(1,...);
stmt.setString(2, 'AD');
stmt.execute();

but I don't know how. I got the error SQL0798N : A value cannot be
specified for column "ID" which is defined as GENERATED ALWAYS

As the message says, you cannot provide any value for the "id" column, not
even DEFAULT. Change your statement to this:

INSERT
INTO usrdb2.mytable(code)
VALUES ('AD')@

--
Knut Stolze
Information Integration
IBM Germany / University of Jena

[Dov Moryusef]

Thanks Knut

As the message says, you cannot provide any value for the "id" column, not
even DEFAULT. Change your statement to this:

INSERT
INTO usrdb2.mytable(code)
VALUES ('AD')@

[Serge Rielau]

Actually DEFAULT is allowed. DEFAULT says:
"Dear DB2, plug in what you believe is right"
For a generated column (identity or expression) that means DB2 produces
the appropriate value.
For any other updatable column it looks up the default value for that
column.
Only for pure expressions as a target does DB2 raise an error today.
The reason for this are two fold:
1. It saves you from listing all the columns on INSERT just to handle
generated columns
2. You can specify:
SET <identity_col> = DEFAULT
in an UPDATE, MERGE statement or a before trigger.
The result is that the identity value will be regenerated.

Cheers
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[Ian]

Dov Moryusef wrote:

Hi to all,

I’ve a table with an idendity column :

CREATE TABLE USRDB2.MYTABLE (
"ID" INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 0,
INCREMENT BY 1, NO CACHE),
"CODE" CHARACTER (3),
PRIMARY KEY (ID))IN MYTS01@

I would like to execute the following request (which works in a db2cmd):

db2 insert into usrdb2.mytable (id, code) values (DEFAULT, 'AD') @

with JDBBC:

String REQUEST = "insert into usrdb2.mytable (id, code) values (?, ?)";
PreparedStatement stmt = connection. prepareStatement(REQUEST);
stmt.setInt(1,...);
stmt.setString(2, 'AD');
stmt.execute();

but I don't know how. I got the error SQL0798N : A value cannot be
specified for column "ID" which is defined as GENERATED ALWAYS

DEFAULT is a key word, so you need to include it in your statement --
it is not a value that you use when you execute a prepared statement.
Therefore,

String REQUEST = "insert into usrdb2.mytable (id, code) values (DEFAULT, ?)";
PreparedStatement stmt = connection. prepareStatement(REQUEST);
stmt.setString(1, 'AD');
stmt.execute();
Or, as Knut suggested, you can just avoid the column 'id' in your
insert statement entirely.
Good luck,

-----= Posted via Newsfeeds.Com, Uncensored Usenet News =-----
http://www.newsfeeds.com - The #1 Newsgroup Service in the World!
-----== Over 100,000 Newsgroups - 19 Different Servers! =-----

[Dov Moryusef]

Thanks a lot. That works !!

Dov
"Ian" <ia*****@mobileaudio.com> a écrit dans le message de
news:40********@corp.newsgroups.com...

Dov Moryusef wrote:

Hi to all,

I’ve a table with an idendity column :

CREATE TABLE USRDB2.MYTABLE (
"ID" INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 0,
INCREMENT BY 1, NO CACHE),
"CODE" CHARACTER (3),
PRIMARY KEY (ID))IN MYTS01@

I would like to execute the following request (which works in a db2cmd):

db2 insert into usrdb2.mytable (id, code) values (DEFAULT, 'AD') @

with JDBBC:

String REQUEST = "insert into usrdb2.mytable (id, code) values (?, ?)";
PreparedStatement stmt = connection. prepareStatement(REQUEST);
stmt.setInt(1,...);
stmt.setString(2, 'AD');
stmt.execute();

but I don't know how. I got the error SQL0798N : A value cannot be
specified for column "ID" which is defined as GENERATED ALWAYS
DEFAULT is a key word, so you need to include it in your statement --
it is not a value that you use when you execute a prepared statement.
Therefore,

String REQUEST = "insert into usrdb2.mytable (id, code) values (DEFAULT,

?)"; PreparedStatement stmt = connection. prepareStatement(REQUEST);
stmt.setString(1, 'AD');
stmt.execute();
Or, as Knut suggested, you can just avoid the column 'id' in your
insert statement entirely.
Good luck,

-----= Posted via Newsfeeds.Com, Uncensored Usenet News =-----
http://www.newsfeeds.com - The #1 Newsgroup Service in the World!
-----== Over 100,000 Newsgroups - 19 Different Servers! =-----