Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » EclipseLink » SQLServer IDENTITY_INSERT fails --SOLVED(despite having set IDENTITY_INSERT to ON)
SQLServer IDENTITY_INSERT fails --SOLVED [message #1072710] Tue, 23 July 2013 10:40 Go to next message
Hannes Kühtreiber is currently offline 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')
Doing the same via EclipseLink also produces above error.

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
         insertable="false"

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

icon3.gif  Re: SQLServer IDENTITY_INSERT fails [message #1076468 is a reply to message #1072710] Wed, 31 July 2013 12:47 Go to previous messageGo to next message
Hannes Kühtreiber is currently offline Hannes Kühtreiber
Messages: 21
Registered: June 2013
Junior Member
Just found out that IDENTITY works with annotations, but not with xml mapping file. I suspect this is a bug.
Chris, James, would you agree?
Hannes
Re: SQLServer IDENTITY_INSERT fails [message #1076509 is a reply to message #1076468] Wed, 31 July 2013 13:57 Go to previous messageGo to next message
Chris Delahunt is currently offline Chris Delahunt
Messages: 1034
Registered: July 2009
Senior Member
That would be surprising as the annotation and xml processing shares similar code, and there are some tests for it - but something is going wrong, and if it is recognizing the annotation but not the XML you should file a bug with a test case.

Are there any other tags that are being ignored, and is the SQL generated for the insert including a null value for the conIdContact field? Try turning on logging to finest and check if there are warnings printed off during the deploy phase, and you might also try putting the column tag before the sequence tag in case there is an issue with ordering.

Best Regards,
Chris

Re: SQLServer IDENTITY_INSERT fails [message #1077302 is a reply to message #1076509] Thu, 01 August 2013 14:05 Go to previous messageGo to next message
Hannes Kühtreiber is currently offline 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 Go to previous messageGo to next message
James Sutherland is currently offline James Sutherland
Messages: 1939
Registered: July 2009
Location: Ottawa, Canada
Senior Member

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 Go to previous messageGo to next message
Hannes Kühtreiber is currently offline 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 ,
...
I am also positive the orm.xml is beeing read, every typo triggers an exception.
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

Re: SQLServer IDENTITY_INSERT fails [message #1085154 is a reply to message #1085120] Mon, 12 August 2013 14:26 Go to previous messageGo to next message
Hannes Kühtreiber is currently offline Hannes Kühtreiber
Messages: 21
Registered: June 2013
Junior Member
James, I took the example of how to map the Id from
http://wiki.eclipse.org/EclipseLink/Examples/JPA/EmployeeXML
how would I default the namespace?
confused now ...
Hannes
Re: SQLServer IDENTITY_INSERT fails [message #1085882 is a reply to message #1085154] Tue, 13 August 2013 13:47 Go to previous messageGo to next message
James Sutherland is currently offline James Sutherland
Messages: 1939
Registered: July 2009
Location: Ottawa, Canada
Senior Member

The example you referenced is defaulting the namespace, it does not use <orm:

Try to run the example changing it to use IDENTITY, does it work?


James : Wiki : Book : Blog : Twitter
Re: SQLServer IDENTITY_INSERT fails --SOLVED [message #1085911 is a reply to message #1085882] Tue, 13 August 2013 14:34 Go to previous message
Hannes Kühtreiber is currently offline Hannes Kühtreiber
Messages: 21
Registered: June 2013
Junior Member
James, you are right. Just tried this half an hour ago. Works!
I will edit/close the bug report first thing tomorrow morning.

Fussed around to find the example where I picked up the orm: prefix. I see now that Texo generated the orm.xml like that. Thought it was necessary ...

Thanks!
Hannes
Previous Topic:commonj.sdo plugin is unsigned (moved from platform forum)
Next Topic:Changing Tablename changes on-the-fly?
Goto Forum:
  


Current Time: Sat Oct 25 06:27:45 GMT 2014

Powered by FUDForum. Page generated in 0.01785 seconds
.:: Contact :: Home ::.

Powered by: FUDforum 3.0.2.
Copyright ©2001-2010 FUDforum Bulletin Board Software