| SQLServer IDENTITY_INSERT fails --SOLVED [message #1072710] |
Tue, 23 July 2013 10:40  |
 Hannes Kühtreiber
Messages: 21
Registered: June 2013 |
Junior Member |
|
|
Hello Everybody,
I am dealing with a SQLServer legacy DB which has an Identity column which auto-increments. Mapping this with hibernate was straightforward with no changes to the DB:
<id name="id" type="int">
<column name="conIdContact" />
<generator class="assigned" />
</id>
However, I am unable to get this to work with EclipseLink.
Whatever I try results in an SQLServer Exception complaining that the ID cannot be inserted because IDENTITY_INSERT is set to OFF (sorry its german but should be clear.)
com.microsoft.sqlserver.jdbc.SQLServerException: Ein expliziter Wert für die Identitätsspalte kann in der tContacts-Tabelle nicht eingefügt werden, wenn IDENTITY_INSERT auf OFF festgelegt ist.
Error Code: 544
The disturbing thing is that I have set IDENTITY_INSERT to *ON*, and following SQL works flawlessly:
INSERT tcontacts (conidcontact, concontactname)
VALUES (12246, 'test_insert')
Wikibooks recommends to use IDENTITY
<orm:id name="id">
<generated-value strategy="IDENTITY" />
<orm:column name="conIdContact" updatable="false" />
</orm:id>
which does not work.
Omitting the strategy does not work either, nor does using AUTO.
In desperation I thought about setting
but then I get (not at all unexpectedly)
Exception Description: There should be one non-read-only mapping defined for the primary key field [tContacts.conIdContact].
I am stuck now and would be *very* grateful if someone could explain what is going wrong here!
Hannes
[Updated on: Tue, 13 August 2013 14:35] Report message to a moderator |
|
|
|
|
| Re: SQLServer IDENTITY_INSERT fails [message #1077302 is a reply to message #1076509] |
Thu, 01 August 2013 14:05  |
Hannes Kühtreiber
Messages: 21
Registered: June 2013 |
Junior Member |
|
|
Chris,
reordering the tags makes no difference, also no warnings, but one error on FINEST:
[EL Finer]: Could not initialize Validation Factory. Encountered following exception: java.lang.NoClassDefFoundError: javax/validation/Validation
Strange thing is that in my little test project Eclipselink tries to insert '0' into the ID column while in the original project it tried to insert 'NULL'.
Sorry can't reproduce this now as I shot it and have to rebuild first.
Can't say anything about other tags because I used mapping files and not annotations. In the xml
id, basic, column, name, attribute-type, many-to-one, target-entity, join-column, cascade
worked as expected. Have not tried anything else so far (except embeddedables, which did not work as expected)
I filed a bug at
https://bugs.eclipse.org/bugs/show_bug.cgi?id=414203
Hannes
|
|
|
| Re: SQLServer IDENTITY_INSERT fails [message #1082420 is a reply to message #1077302] |
Thu, 08 August 2013 14:44 |
|
How are you creating the tables? To use IDENTITY you must use a IDENTITY column when creating the table. Include the DDL for the table def.
Are you sure your orm.xml is being used and is valid? Try changing it and see if the changes are picked up.
It is odd you are using the namespace, <orm:id name="id">, normally the namespace is not used, but defaulted.
Can you try TABLE sequencing from your orm.xml file, does it work? (in general I would not recommend IDENTITY sequencing, as it does not support preallocation).
Also ensure you have set our target-database correctly.
James : Wiki : Book : Blog : Twitter
|
|
|
| Re: SQLServer IDENTITY_INSERT fails [message #1085120 is a reply to message #1082420] |
Mon, 12 August 2013 13:30 |
Hannes Kühtreiber
Messages: 21
Registered: June 2013 |
Junior Member |
|
|
Hello James,
here is the SQL for the table:CREATE TABLE [dbo].[tContacts] (
[conIdContact] [int] IDENTITY (1, 1) NOT NULL ,
[conContactName] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
...
As for the <orm:id name="id">, as its a legacy DB the column names are different from the attribute names, as in name:
<orm:basic name="name"><orm:column name="conContactName"/></orm:basic>
so I did the same for the id.
Will try without.
The DB so far is also used by an older application, so I would prefer to leave Identity generation as is, but I agree it causes its own rat-tail of problems.
Thanks for looking into it!
Hannes
[Updated on: Mon, 12 August 2013 13:35] Report message to a moderator |
|
|
|
|
|
Powered by
FUDForum. Page generated in 0.04327 seconds
] 
Search
Help
Register
Login
Home
