Skip to main content


Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » EclipseLink » Insert using identity column and trigger returns wrong primary key(When inserting into a table which has a trigger which does an insert into another table returns the id of the record inserted into the other table instead)
Insert using identity column and trigger returns wrong primary key [message #1810028] Mon, 29 July 2019 14:18 Go to next message
Francois Thirion is currently offline Francois ThirionFriend
Messages: 4
Registered: July 2019
Junior Member
This is the exact same issue as an unanswered question posted in 2014: https://www.eclipse.org/forums/index.php/m/1462652/?srch=scope_identity#msg_1462652

When persisting an entity that has an identity primary key it returns the value as expected after doing a flush.
If the table has a trigger defined that inserts into another table, then the primary key assigned after the flush is that of the other table instead of this one.

This is easily tested by doing an insert, flush and then comparing the primary key returned to select max(id) of the table.

If I change the persistence provider to OpenJPA then I get the correct value as expected.

This is apparently MS SQL specific and if we were not using a persistence provider the advice would be to use scope_identity() instead of @@Identity to get the correct generated id. I would love to try and fix this myself but I have not idea where to even start.

Is there anything I can do in my project to get the expected behaviour? Unfortunately I can't change or disable the trigger as I'm inserting into a 3rd party database.

Any help would be appreciated.
Re: Insert using identity column and trigger returns wrong primary key [message #1811163 is a reply to message #1810028] Wed, 28 August 2019 03:33 Go to previous messageGo to next message
Chris Delahunt is currently offline Chris DelahuntFriend
Messages: 1388
Registered: July 2009
Senior Member
Turn on logging to see what exactly is issued, and show the DatabasePlatform class you've configured EclpseLink to use.

The DatabasePlatform method buildSelectQueryForIdentity returns the query used for getting the assigned identity. The MySQLPlatform class is set to use "SELECT LAST_INSERT_ID()", while other platform classes (SQLServerPlatform,SybasePlatform etc) are using "SELECT @@IDENTITY". If you need to use something different, you would extend the appropriate class and overwrite this method, and then tell EclipseLink to use your target DatabasePlatform implementation using the eclipselink.target-database persistence property

Best Regards,
Chris
Re: Insert using identity column and trigger returns wrong primary key [message #1811183 is a reply to message #1811163] Wed, 28 August 2019 10:16 Go to previous messageGo to next message
Francois Thirion is currently offline Francois ThirionFriend
Messages: 4
Registered: July 2019
Junior Member
Hi Chris,

Thanks for the reply. I downloaded the Eclipselink source from Git and changed the buildSelectQueryForIdentity method to return "scope_identity()" instead of "SELECT @@IDENTITY" but it then always returns null.
I've found another post on the internet where someone else created their DatabasePlatform as you suggested and did the same change I did and they were also always getting a null result.

I think this is because the scope of the sql body of work has already been closed, which is why the scope_identity() doesn't return a result.
So, for this to work we probably need to call buildSelectQueryForIdentity while still in the correct scope in addition to changing it to return scope_identity() instead of @@IDENTITY.
I just don't know where that has to happen.
Re: Insert using identity column and trigger returns wrong primary key [message #1822872 is a reply to message #1811183] Mon, 16 March 2020 09:13 Go to previous message
Francois Thirion is currently offline Francois ThirionFriend
Messages: 4
Registered: July 2019
Junior Member
OpenJPA is giving me an issue which brought me back to trying to fix this issue in EclipseLink again.

Here is a simple explanation of the problem and how to fix it.

If you have a table with an identity primary key, which has a create trigger defined on it that inserts into another table then it returns the primary key of the second table instead of the initial one.

-- main table we want to insert and get id back
CREATE TABLE TABLE_A(int identity not null primary key, name varchar(50));

-- create log table with a higher starting id
create table table_b(int identity(10000,1) not null primary key, username varchar(50), created_date datetime);

-- trigger code
ALTER TRIGGER SomeTriggerName ON TABLE_A
FOR INSERT, UPDATE, DELETE
AS
...
-- log the record just inserted
insert into table_b ... 
end


Here is some Java code to show the problem using EclipseLink:
EntityManagerFactory emf = Persistence.createEntityManagerFactory("EclipseLinkPkTestPU");
EntityManager em = emf.createEntityManager();

TableA tableA = new TableA();
tableA.setName("Testing123");

em.getTransaction().begin();
em.persist(tableA);
em.flush();
em.getTransaction().commit();

System.out.println("PK: " + tableA.getId());

Long maxPk = (Long) em.createNativeQuery("select max(id) from table_a").getSingleResult();
System.out.println("Max PK: " + maxPk);

em.close();
emf.close();


This prints "PK: " with the id from table_b and "Max PK: " with the id we expected.

Here is the same issue using JDBC. It shows why just replacing @@IDENTITY with SCOPE_IDENTITY() in the database platform doesn't just work as well as how it could be done.

Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
Connection con = DriverManager.getConnection("jdbc:sqlserver://localhost;databaseName=SomeDatabase", "someUser", "somePass");
PreparedStatement stmnt = con.prepareStatement("INSERT INTO TABLE_A (name) VALUES (?); SELECT SCOPE_IDENTITY()"); //f this works with SCOPE_IDENTITY as well as @@IDENTITY

//f this obviously works as it's the generic JDBC way of getting the pk after an insert statement:
//f PreparedStatement stmnt = con.prepareStatement("INSERT INTO TABLE_A (name) VALUES (?)", Statement.RETURN_GENERATED_KEYS);

stmnt.setString(1, "Just testing");
stmnt.executeUpdate();

long pk = -1L;
try (ResultSet rsKey = stmnt.getGeneratedKeys()) {
    if (rsKey != null && rsKey.next()) {
        pk = rsKey.getLong(1);
    } else {
        System.err.println("!! no key returned");
    }
}

System.out.println("GeneratedKeys PK: " + pk);

//f this is the default EclipseLink behaviour that calls @@IDENTITY
try (ResultSet rsSelectAtIdentity = con.createStatement().executeQuery("select @@IDENTITY")) {
    if (rsSelectAtIdentity.next()) {
        Long scopePk = (Long) rsSelectAtIdentity.getLong(1);
        System.out.println("@@IDENTITY PK: " + scopePk); //f this prints the incorrect pk
    } else {
        System.err.println("NO result for @@IDENTITY");
    }
}

//f this is the suggested fix, but doesn't work as it is called outside of the initial statement scope.
try (ResultSet rsSelectScopeIdentity = con.createStatement().executeQuery("select SCOPE_IDENTITY()")) {
    if (rsSelectScopeIdentity.next()) {
        Long scopePk = (Long) rsSelectScopeIdentity.getLong(1);
        System.out.println("Scope PK: " + scopePk); //f this prints 0, which is obviously incorrect
    } else {
        System.err.println("NO result for SCOPE_IDENTITY()");
    }
}

Long maxPk;
try (ResultSet rsSelectPk = con.createStatement().executeQuery("select max(id) from table_a")) {
    rsSelectPk.next();
    maxPk = (Long) rsSelectPk.getLong(1);
}
System.out.println("Max PK: " + maxPk); //f this is the pk we expect


So, instead of doing one call for the insert and a separate one for the id, I think we need to find out if it's an identity insert and use the generic JDBC way of adding Statement.RETURN_GENERATED_KEYS on the statement object. That also means that the platform's buildSelectQueryForIdentity method isn't necessary.

I've been stepping through the code in debug mode for 3 days trying to figure out how it all works and where to make this change, but I think it's best to ask someone who knows the EclipseLink code.

Previous Topic:Multitenant per table and native query
Next Topic:How to use MOXy as default JAXB implementation?
Goto Forum:
  


Current Time: Fri Jun 25 14:21:43 GMT 2021

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

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

Back to the top