Christian wrote
In my applications (non ACS, DB independent) I did choose to generate the ID's in one specific procedure for the whole application as not all DB's used do have sequences.
For those of you who are scratching your heads over this one...You cannot do "
create sequence foo;" in SQL Server, and maybe others. The SQL Server way is to use something called "autonumber columns". The idea is that the column generates its own values from an internal, otherwise hidden sequence. When inserting, you simply omit the magical column from the list of columns to insert, and it gets automatically filled with the next sequence value. The sequence value is then retrievable from a session variable called
@@IDENTITY that works like
sequence.currval.
This can complicate things when you want to write code that works with the two types of databases, because the steps are different. The best approach I have seen is to mimic sequences by creating a sequence generator table like so...
create table foo_id_sequence (
foo_id bigint identity(1,1),
c char(1)
);
To get new sequence values, you insert a value for
c and then query for
@@IDENTITY.
This can all be hidden inside a multipurpose proc like
db_nextval
It still feels a little crufty to me, but is a lot simpler than trying to re-implement sequences or supporting both approaches in the application layer.
