File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes EJB Certification (SCBCD/OCPJBCD) and the fly likes SQLSyntaxErrorException: Attempt to modify an identity column 'ID' Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of EJB 3 in Action this week in the EJB and other Java EE Technologies forum!
JavaRanch » Java Forums » Certification » EJB Certification (SCBCD/OCPJBCD)
Bookmark "SQLSyntaxErrorException: Attempt to modify an identity column Watch "SQLSyntaxErrorException: Attempt to modify an identity column New topic
Author

SQLSyntaxErrorException: Attempt to modify an identity column 'ID'

Karnati Sudhakar
Ranch Hand

Joined: Aug 26, 2008
Posts: 270

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


Regards
Sudhakar
Treimin Clark
Ranch Hand

Joined: Nov 12, 2008
Posts: 757
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

Joined: Aug 26, 2008
Posts: 270

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

Joined: Aug 21, 2008
Posts: 383
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

Joined: Jul 17, 2008
Posts: 844
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.


SCJP 1.4, SCWCD 5, SCBCD 5, OCPJWSD 5,SCEA-1, Started Assignment Part 2
My blog- http://rkydesigns.blogspot.com
Karnati Sudhakar
Ranch Hand

Joined: Aug 26, 2008
Posts: 270

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

Joined: Nov 24, 2005
Posts: 14687
    
  16

<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"


[My Blog]
All roads lead to JavaRanch
Christophe Verré
Sheriff

Joined: Nov 24, 2005
Posts: 14687
    
  16

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

Joined: Aug 26, 2008
Posts: 270

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

Joined: Nov 24, 2005
Posts: 14687
    
  16

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

Joined: Aug 21, 2008
Posts: 383
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

Joined: Aug 26, 2008
Posts: 270

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
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: SQLSyntaxErrorException: Attempt to modify an identity column 'ID'
 
Similar Threads
Unable to understand exception
problem with @FieldResult, mapping does not seem to work
JSF action doesn't call bean method
how can i display my data on jsp page
JPA - Entity Manager Query