[OJB-77] PlatformMsSQLServerImpl.getLastInsertIdentityQuery creates incorrect SQL statement to pull the identity from the last inserted row

• Type: Bug
• Status: Resolved
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 1.0.3
• Fix Version/s: 1.0.4
• Component/s: PB-API
• Environment: Microsoft SQL 2000 server
• Assignee: Unassigned
• Reporter: Charles N. Harvey
• Created: 04/Nov/05 08:02
• Updated: 12/Nov/05 01:29
• Resolved: 12/Nov/05 01:29

Description

org.apache.ojb.broker.platforms.PlatformMsSQLServerImpl.getLastInsertIdentityQuery(java.lang.String tableName)

Returns a string that says:
"SELECT @@IDENTITY FROM " + tableName;

This, is in fact, incorrect.

It should be:
"SELECT @@IDENTITY";

Depending on table size, this query can run for MINUTES. As it was doing for me. Try it out. Open up a query browser and
run this:

INSERT INTO TABLE (...) VALUES (...) SELECT @@IDENTITY FROM TABLE

And then compare to this:

INSERT INTO TABLE (...) VALUES (...) SELECT @@IDENTITY

Its silly, but the difference is huge. The first one scans every row in the database, printing out 'null' for every row, then spits
out the last identity at the end. In a table of 9 million rows, this ran for 15 minutes. Take the "FROM TABLE" off and it pulls the
identity value from a system table and returns in .0001 ms.