Win a copy of Programmer's Guide to Java SE 8 Oracle Certified Associate (OCA) this week in the OCAJP forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

SQLSyntaxErrorException: Attempt to modify an identity column 'ID'

 
Karnati Sudhakar
Ranch Hand
Posts: 270
Eclipse IDE Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi All,

I am trying to understand @ManyToOne relation ship between Employee4 and Department1 entities.

Employee4.java




Department1.java




code to persist Department1 and Employee4 entites:




But the error i am getting is :

Local Exception Stack:
Exception [TOPLINK-4002] (Oracle TopLink Essentials - 2.0.1 (Build b04-fcs (04/11/2008))): oracle.toplink.essentials.exceptions.DatabaseException
Internal Exception: java.sql.SQLSyntaxErrorException: Attempt to modify an identity column 'ID'.
Error Code: -1
Call: INSERT INTO DEPARTMENT1 (ID, DEPARTMENTNAME) VALUES (?, ?)
bind => [45, Army]


Please advice me whats wrong above?

Regards
Sudhakar
 
Treimin Clark
Ranch Hand
Posts: 757
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
The ID field is the identity which the JPA can identify an entity, and to map it to the database. Attempting to change the ID would cause the above exception therefore.

Correct me if I'm wrong.
 
Karnati Sudhakar
Ranch Hand
Posts: 270
Eclipse IDE Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Treimin,

I am making sure that there are no records in the Department1 table and same in Employee4 tables.Why cant we set primary keys programmetically rather than letting persistance provider doing it?

Regards
Sudhakar
 
Raf Szczypiorski
Ranch Hand
Posts: 383
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi. What database are you using, and how have you created the db schema?
I might be guessing here, but for me it looks like the id column is a kind of "identity" in db2, or maybe "auto_increment" in mysql. The exception is a JDBC exc, not JPA. Your id is specified not to be GenerationType.IDENTITY. Maybe this is the problem?
Please paste the ddl for the DEPARTAMET1 table, right now I think we have too little info.
 
Amandeep Singh
Ranch Hand
Posts: 850
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
try to comment out 23 line, then see you can persist. if it is successful, then it means in your second persist, you are overwriting the primary id.
 
Karnati Sudhakar
Ranch Hand
Posts: 270
Eclipse IDE Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Raf,

Hi. What database are you using, and how have you created the db schema?


I am using Derby Database & Glassfish server bundled with the Netbeans 6.5.I just created a new Database with the name practice so is the scheme name.

Your id is specified not to be GenerationType.IDENTITY. Maybe this is the problem?


I specified id not to be of GenerationType.IDENTITY because to test ManyToOne relationship.

Please paste the ddl for the DEPARTAMET1 table, right now I think we have too little info.


I dont know where to look for the ddl of DEPARTAMET1 table.Here is the persistence.xml for this





Aman,

I tried that still i am getting the same error.

Regards
Sudhakar

 
Christophe Verré
Sheriff
Posts: 14691
16
Eclipse IDE Ubuntu VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
<property name="toplink.ddl-generation" value="create-tables"/>

I think Raf is on the good track. I think that an identity column will be generated for your ID fields.
Try to check the DDL of the created table. It might look like something like this :

In that case, you cannot set the ID manually. What you can do is either :
- Use the @GeneratedValue annotation with GenerationType.IDENTITY
- or Do not let the container create tables automatically, and make them yourself, without using "GENERATED ALWAYS AS IDENTITY"
 
Christophe Verré
Sheriff
Posts: 14691
16
Eclipse IDE Ubuntu VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
In Glassfish, you can use the "ij" command line (under javadb/bin), log in your database, and check the DEPARTMENT1 DDL with "describe DEPARTMENT1;"
 
Karnati Sudhakar
Ranch Hand
Posts: 270
Eclipse IDE Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
chris,

Thanks for your reply..
Here is the output in ij..

ij> CONNECT 'jdbc:derby://localhost:1527/practice;user=practice;password=practic
e';
ij> describe DEPARTMENT1;
COLUMN_NAME |TYPE_NAME|DEC&|NUM&|COLUM&|COLUMN_DEF|CHAR_OCTE&|IS_NULL&
------------------------------------------------------------------------------
ID |BIGINT |0 |10 |19 |AUTOINCRE&|NULL |NO
DEPARTMENTNAME |VARCHAR |NULL|NULL|255 |NULL |510 |YES

2 rows selected


I wanted to test ManyToOne entity relationship.That is the reason i am trying to set id programmetically.

Please advice me how to test ManyToOne relationships.

Regards
Sudhakar
 
Christophe Verré
Sheriff
Posts: 14691
16
Eclipse IDE Ubuntu VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I wanted to test ManyToOne entity relationship.That is the reason i am trying to set id programmetically.

Actually, you don't need to bother about the autogenerated primary key. Doesn't it work if you don't set the primary key ?
 
Raf Szczypiorski
Ranch Hand
Posts: 383
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
As Christphe says, you should just get rid of these setId() setter methods, and make it work with no other changes to the code. Have you tried this before?
 
Karnati Sudhakar
Ranch Hand
Posts: 270
Eclipse IDE Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
As Christphe says, you should just get rid of these setId() setter methods, and make it work with no other changes to the code. Have you tried this before?


Raf,

Thanks at last i was able to test persist Department1 entity and was able to ManyToOne relation ship successfully.What i did is i manually deleted tables using ij and then persisted again that solved the problem.

Regards
Sudhakar
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic