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

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

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')@
>

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

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

Thanks a lot. That works !!

Dov
"Ian" <ianbjor@mobileaudio.com> a écrit dans le message de
news:4074aa53_1@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! =-----