how can I get the just-inserted pk ID in a DB agnostic way?
1 posts in topic
Flat View  Flat View
TOPIC ACTIONS:
 

Posted By:   neal_ravindran
Posted On:   Monday, April 7, 2003 01:57 PM

how can I get the just-inserted pk ID in a DB agnostic way?
(instead of using @@IDENTITY thing for example in SQLServer)

Re: how can I get the just-inserted pk ID in a DB agnostic way?

Posted By:   Roger_Hand  
Posted On:   Tuesday, April 8, 2003 01:07 AM

There are a couple of approaches, but unfortunately no single best, or painless, method.


Method 1: Different method depending on database

Have a static wrapper method in a utility class that you can call to get the most recently used sequence number. This calls private methods depending on the type of database you have. Obviously, there's a lot of code left out here, but maybe enough for you to get the idea:

public static long GetCurrent(Connection Conn, String tableName) {
switch (MiscSettings.databaseType) {
case DBConnect.DBTYPE_ORACLE:
return GetCurrent_Oracle(Conn, tableName + "_seq");
case DBConnect.DBTYPE_POSTGRES:
return GetCurrent_Postgres(Conn, tableName);
case DBConnect.DBTYPE_MSSQL:
return GetCurrent_MSSQL(Conn);
default:
logger.error("Unknown database type MiscSettings.databaseType " + MiscSettings.databaseType);
}
/* Error! */
return -1;
}

For Oracle you'd do a SELECT _SEQ.CurrVal AS SEQNUM FROM DUAL. For Postgres you'd do SELECT currval(_seq). For MS SQL, of course, you can just select @@IDENTITY, without needing to specify the table/sequence name. In any case, in your code you'd just make a call such as
long id = SequenceUtils.GetCurrent(Conn, "TableName");
without worrying about what type of db you've got.
BTW, the MiscSettings.databaseType above could easily come from a properties file instead.


Method 2: Use sequences, even for MS SQL

This may require too many code changes, but you can use this proc in MS SQL to mimic the way sequences work in Oracle and Postgres:

CREATE PROCEDURE up_Sequence

@SequenceName varchar(30),
@@NextID int OUTPUT

AS
BEGIN TRANSACTION

UPDATE Sequence
SET @@NextID = CurrentVal = CurrentVal + 1
WHERE SequenceName = @SequenceName;
COMMIT TRANSACTION

Then you no longer define any columns as being auto-number, but instead call the proc to get the next sequence from a table called Sequence (SequenceName varchar(30), CurrentVal int). You make a wrapper class, similar to the GetCurrent() above, and call it to GetNext(). Again there's a switch depending on db type. The point is that this way you have the id before you do the insert.

Disclaimer: I did a lot of copy/pasting above, and could have easily made a mistake someplace!

About | Sitemap | Contact