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: [email protected] Telefon: 08-761 06 55 Mobil: 0733-44 24 64 [Non-text portions of this message have been removed] ------------------------------------ ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ Visit http://www.firebirdsql.org and click the Resources item on the main (top) menu. Try Knowledgebase and FAQ links ! Also search the knowledgebases at http://www.ibphoenix.com ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ Yahoo! Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ <*> Your email settings: Individual Email | Traditional <*> To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) <*> To change settings via email: [email protected] [email protected] <*> To unsubscribe from this group, send an email to: [email protected] <*> Your use of Yahoo! Groups is subject to: http://docs.yahoo.com/info/terms/
