Den 2012-02-10 21:20 skrev Woody s�h�r:

>
> > As Ann has pointed out, generators/sequences are a much better approach.
>
> Of course they are a better idea, but not all databases support them.
> I was
> merely offering a solution that is independent of any database design.
> Just
> a different way of solving the problem.
>
>

If you want to avoid sequences that perhaps have different syntax for
different servers, may I suggest that you have a separate table:

create table "PK" (
"TableName" varchar(30) not null primary key,
"LastUsedId" int not null default 0
)

Enter one row in this table for each table you need a PK "generator"
for. To aquire a key, start your transaction with:

update "PK"
set "LastUsedId" = "LastUsedId" + 1
where "TableName" = :TableName

Then

select "LastUsedId"
from "PK"
where "TableName" = :TableName

The update makes sure the record is locked from updates from other
transactions. You can then safely read the new key value with a select.
You can encapsulate the update + select in a sp if you like, but I guess
you don't want that.

This will work on and DB with proper transaction handling, and provided
you don't use some unappropriate transaction isolation level for the key
fetching transaction.

Furthermore, you can add more than one if you want to acquire more than
one key at a time.

Regards,
Kjell

--
--------------------------------------
Kjell Rilbe
DataDIA AB
E-post: kjell@...
Telefon: 08-761 06 55
Mobil: 0733-44 24 64



[Non-text portions of this message have been removed]