DB2 insert error with generator=identity

roystonmonteiro · **Joined:** Wed May 05, 2010 2:48 am **Posts:** 3

Hi Folks,
I am inserting a new row into a DB2 table with the following definition:

Code:

CREATE TABLE DB.GROUP(
ID decimal(15) generated always as identity(
start with 1, increment by 1
, cache 20
, maxvalue 99999999999999
, minvalue 1
, no cycle),

name varchar(250)
)

The mapping file is as follows:

Code:

<hibernate-mapping>
  <class name="com.db.birt.data.GroupD" table="DB.GROUP" lazy="false" >
    <id column="ID" name="Gruppe_ID" type="java.lang.Integer" >
        <generator class="identity" />
    </id>
    <property column="name" name="Gruppe_Name" type="string"/>
  </class>
</hibernate-mapping>

The com.db.birt.data.GroupD class is:

Code:

public class GroupD {
    private Integer Gruppe_ID;
    private String Gruppe_Name;

    public GroupD(String Gruppe_Name) {        
        this.Gruppe_Name = Gruppe_Name;
    }

    public String toString(){
        return "Group Id : " + Gruppe_ID + " \nGroup Name : " + Gruppe_Name;
    }

    public GroupD() {
    }    

    public Integer getGruppe_ID() {
        return Gruppe_ID;
    }

    public void setGruppe_ID(Integer Gruppe_ID) {
        this.Gruppe_ID = Gruppe_ID;
    }

    public String getGruppe_Name() {
        return Gruppe_Name;
    }

    public void setGruppe_Name(String Gruppe_Name) {
        this.Gruppe_Name = Gruppe_Name;
    }
}

When I try to do this:

Code:

org.springframework.orm.hibernate3.support.HibernateDaoSupport.getHibernateTemplate().save(new GroupD('royston'));

I get the error:

Code:

Hibernate: insert into DB.XMABGT (GRUPPE_ID, GRUPPE_NAME) values (null, ?)
[05-May-2010:07:17:33] [http-8080-Processor21] [WARN ][   ][  ]
logExceptions() ==> SQL Error: -798, SQLState: 428C9

[05-May-2010:07:17:33] [http-8080-Processor21] [ERROR][   ][  ]
logExceptions() ==> DB2 SQL error: SQLCODE: -798, SQLSTATE: 428C9, SQLERRMC: GRUPPE_ID

[05-May-2010:07:17:33] [http-8080-Processor21] [WARN ][   ][  ]
logExceptions() ==> SQL Error: -516, SQLState: 26501

[05-May-2010:07:17:33] [http-8080-Processor21] [ERROR][   ][  ]
logExceptions() ==> DB2 SQL error: SQLCODE: -516, SQLSTATE: 26501, SQLERRMC: null

[05-May-2010:07:17:33] [http-8080-Processor21] [WARN ][   ][  ]
logExceptions() ==> SQL Error: -518, SQLState: 07003

From the DB2 error codes, the SQLCODE: -798 is :A ROWID or IDENTITY column cannot be specified as the target column of an INSERT or UPDATE.

Can you please help

roystonmonteiro · **Joined:** Wed May 05, 2010 2:48 am **Posts:** 3

Hi Folks,
Any help please.

jonas123 · **Joined:** Wed Apr 21, 2010 8:41 am **Posts:** 12

Hello,

the instert statement is wrong

insert into DB.XMABGT (GRUPPE_ID, GRUPPE_NAME) values (null, ?)

In DB2 the insert must be like this

insert into DB.XMABGT (GRUPPE_ID, GRUPPE_NAME) values (default, ?)

Regards,
Jonas

roystonmonteiro · **Joined:** Wed May 05, 2010 2:48 am **Posts:** 3

jonas123 wrote:

Hello,

the instert statement is wrong

insert into DB.XMABGT (GRUPPE_ID, GRUPPE_NAME) values (null, ?)

In DB2 the insert must be like this

insert into DB.XMABGT (GRUPPE_ID, GRUPPE_NAME) values (default, ?)

Regards,
Jonas

Thanks for pointing that out.
I had specified the wrong dialect in the configuration, which is why the insert was null instead of default.

Problem solved now :)