Error when inserting into SQL Server table with an Identity Column

2013-08-29
2013-09-09
  • Dirk Coetsee

    Dirk Coetsee - 2013-08-29

    Hi, I'm trying to insert a row into a SQL server table with an identity column defined but on the insert I'm getting an exception:

    Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Cannot insert explicit value for identity column in table 'CL_SCM' when IDENTITY_INSERT is set to OFF

    Either the identity column must not be part of the insert statement
    or
    one should set the Identity_insert on for the table.

    Can I specify that the column should not be part of the insert
    or
    Question for the latter
    how do I inject a
    SET IDENTITY_INSERT "+tableName+" ON statement before the insert and a
    SET IDENTITY_INSERT "+tableName+" OFF after the insert ?

    thanks
    Dirk

     
  • Dirk Coetsee

    Dirk Coetsee - 2013-08-29

    The Identity column is defined as follows:
    @Id
    @GeneratedValue(strategy=GenerationType.IDENTITY)
    @Column(name = "CL_SCM_SK")
    private int classificationSchemeSK;

     
  • Javier Paniza

    Javier Paniza - 2013-09-02

    Hi Dirk,

    did you define the correct dialect in persistence.xml?

    Anyways, use always a wrapper type for the key, in order it could be null. Try this:

    @Id
    @GeneratedValue(strategy=GenerationType.IDENTITY)
    @Column(name = "CL_SCM_SK")
    private Integer classificationSchemeSK; // Integer instead of int
    

    Change the getter and setter too.


    Help others in this forum as I help you.
    Need more help? Get OpenXava professional support.

     
  • Dirk Coetsee

    Dirk Coetsee - 2013-09-03

    Hi Javier:
    Same Error after changing the type to Integer:
    Dialect looks fine as well:

       <persistence-unit name="default">
            <non-jta-data-source>java:comp/env/jdbc/CodeTableDS</non-jta-data-source>
            <class>org.openxava.session.GalleryImage</class>
            <properties>
                <property name="hibernate.dialect" value="org.hibernate.dialect.SQLServerDialect"/>
            </properties>
        </persistence-unit>    
    

    recap of the error message

    Sep 03, 2013 9:55:39 AM org.hibernate.util.JDBCExceptionReporter logExceptions
    WARNING: SQL Error: 544, SQLState: S0001
    Sep 03, 2013 9:55:39 AM org.hibernate.util.JDBCExceptionReporter logExceptions
    SEVERE: Cannot insert explicit value for identity column in table 'CL_SCM' when IDENTITY_INSERT is set to OFF.
    Sep 03, 2013 9:55:39 AM org.openxava.controller.ModuleManager manageRegularException
    SEVERE: org.hibernate.exception.SQLGrammarException: could not insert: [com.bmo.codetablesystem.model.ClassificationScheme]
    javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: could not insert: [com.bmo.codetablesystem.model.ClassificationScheme]
        at org.hibernate.ejb.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1389)
        at org.hibernate.ejb.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1317)
        at org.hibernate.ejb.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1323)
        at org.hibernate.ejb.AbstractEntityManagerImpl.persist(AbstractEntityManagerImpl.java:845)
        at org.openxava.jpa.impl.EntityManagerDecorator.persist(EntityManagerDecorator.java:56)
        at org.openxava.model.impl.JPAPersistenceProvider.persist(JPAPersistenceProvider.java:41)
        at org.openxava.model.impl.POJOPersistenceProviderBase.create(POJOPersistenceProviderBase.java:151)
        at org.openxava.model.impl.MapFacadeBean.create(MapFacadeBean.java:724)
        at org.openxava.model.impl.MapFacadeBean.createReturningKey(MapFacadeBean.java:597)
        at org.openxava.model.impl.MapFacadeBean.createReturningKey(MapFacadeBean.java:255)
    
     
  • Javier Paniza

    Javier Paniza - 2013-09-05

    Hi Dirk,

    if your mark your id as hidden (and do not put it in any @View definition):

    @Id @Hidden // We add @Hidden
    @GeneratedValue(strategy=GenerationType.IDENTITY)
    @Column(name = "CL_SCM_SK")
    private Integer classificationSchemeSK;
    

    Does it work?


    Help others in this forum as I help you.
    Need more help? Get OpenXava professional support.

     
  • Dirk Coetsee

    Dirk Coetsee - 2013-09-06

    Hi Javier, It didn't have any effect.
    Is there a way to execute statements before and after the actual insert statement?
    this way I can please the db server

     
  • Javier Paniza

    Javier Paniza - 2013-09-09

    Hi Dirk,

    It didn't have any effect

    Is classificationSchemeSK present in the user interface?
    If you save data using JPA directly (from a jUnit test or a Java class with main), does it work?

    Is there a way to execute statements before and after the actual insert statement?

    You can use @PrePersist and @PostPersit, but they are intended to write Java code that affect the entity state, not to write directly to the database using JDBC.

    We're talking about saving data using JPA into SQL Servers. It should work with any trick. Did you look for help in Hibernate forum or StackOverflow? Remember that this is not a specific OpenXava issue.

    Another idea. If you create the table using updateSchema, does it work?


    Help others in this forum as I help you.
    Need more help? Get OpenXava professional support.

     

Log in to post a comment.

Get latest updates about Open Source Projects, Conferences and News.

Sign up for the SourceForge newsletter:

JavaScript is required for this form.





No, thanks