Hiberate problems, jdbc IDENTITY_INSERT is set to OFF

Question

I am getting JDBC error when I attempt a commit through hibernate to SQL Server

Cannot insert explicit value for identity column in table 'Report' when IDENTITY_INSERT is set to OFF

I am using mappings generated by netbeans that contain,

<class name="orm.generated.Report" table="Report" schema="dbo" catalog="DatabaseName">
    <id name="id" type="int">
        <column name="ID" />
        <generator class="assigned" />
    </id>

Which looks to me like it should be doing the identity insert properly.

Any idea on how to fix this?

EDIT:
Some links to documentation, for posterity,
http://www.hibernate.org/hib_docs/v3/reference/en-US/html/mapping.html#mapping-declaration-id-generator
http://www.roseindia.net/hibernate/hibernateidgeneratorelement.shtml

score 12 · Accepted Answer · answered Jan 17 '09 at 05:39

12

You cannot insert into an identity column in SQL Server unless "IDENTITY_INSERT" is set to "ON". Since your generator class is "assigned", Hibernate is assuming that you are setting an explicit value for "id" in Java before saving the object and that Hibernate can directly insert the value into the database. You need to either:

Pick a different generator class, such as "native"
Set IDENTITY_INSERT to "ON"

answered Jan 17 '09 at 05:39

cliff.meyers

17,666
5
51
66

1

This works, leads me into another error, but it works, Thank you. – James McMahon Jan 20 '09 at 16:37
1

Just to clarify, I meant, by fixing this problem, I uncovered another one. – James McMahon Jan 20 '09 at 16:37

score 2 · Answer 2 · answered Oct 02 '13 at 15:58

2

try to change the type of the generator class from 'assigned' to 'identity', it worked for me

answered Oct 02 '13 at 15:58

lila

21
1

Thanks. After changing from @GeneratedValue(strategy = GenerationType.AUTO) to @GeneratedValue(strategy = GenerationType.IDENTITY) solved my problem. – Debmalya Jash Jun 29 '20 at 05:30

Nikos Stais · Answer 3 · 2016-10-06T15:55:03.933

1

It would be best to use wrapper classes like Integer instead of primitive int.

Taking your code as an example

<class name="orm.generated.Report" table="Report" schema="dbo" catalog="DatabaseName">
<id name="id" type="java.lang.Integer">
    <column name="ID" />
    <generator class="assigned" />
</id>

edited Oct 06 '16 at 15:55

answered Jul 12 '12 at 20:45

Nikos Stais

81
5

It is a good practice on SO to add as much relevant details as you can in your post. Thus, here you could add code sample for example. That would make your answer way more valuable ;) – ForceMagic Oct 11 '12 at 06:27

score 1 · Answer 4 · answered Apr 04 '13 at 14:20

Here's something that worked for me. Adapt as needed.

@SuppressWarnings("deprecation")
public static void saveWithOverwrittenId(Session session, Object entity) {
    String tableName = entity.getClass().getSimpleName();
    boolean identityInsertSetToOn = false;
    try {
        session.connection().createStatement().execute("SET IDENTITY_INSERT "+tableName+" ON");
        identityInsertSetToOn = true;
        session.beginTransaction();
        session.saveOrUpdate(entity);
        session.getTransaction().commit();
    } catch (SQLException e) {
        session.getTransaction().rollback();
        throw new RuntimeException(e);
    } finally {
        if (identityInsertSetToOn) {
            try {
                session.connection().createStatement().execute("SET IDENTITY_INSERT "+tableName+" OFF");
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
    }
}

In my case, the SQL Server table had the same name as the Entity class. For you, this is probably not true. One solution, then, would be to ask the table name as a parameter.

I have the same need but i have to do it via JPA. Its not working via JPA for some reason. I want to run this query via JPA. Can you help please? — muasif80, Mar 09 '15 at 15:02
@muasif80 I suggest you create a new question, asking specifically that (may citing this code and asking for a JPA equivalent). You'd have greater chances of having your problem solved that way. — acdcjunior, Mar 09 '15 at 17:43
Yes thanks i had actually done that and i found the solution. Then i answered my own question. You can find that question under my profile. — muasif80, Mar 09 '15 at 18:07
http://stackoverflow.com/questions/28945204/jpa-set-identity-insert-not-working/28945642#28945642 — muasif80, Mar 09 '15 at 18:29

score 1 · Answer 5 · answered Apr 10 '13 at 09:42

Change the type of the generator class

BEFORE

<id name="id" type="long">
        <column name="Id" />
        <generator class="assigned" />
    </id>

AFTER

<id name="id" type="long">
        <column name="Id" />
        <generator class="native" />
    </id>

Now this will work!

Hiberate problems, jdbc IDENTITY_INSERT is set to OFF

5 Answers5

Linked