Incrementing the primary key?

[georgh]

Hi, As I can't use in my client app Oracle sequencies, I decided to use max(ID) + 1 method to increment the primary key (ID). My problem is where to do it. In desktop DB I used to simply add an AfterInsert event where I inrcemented the previous max(ID), but in an multiuser environment I thought it would be best to test the max(ID) just before posting, eg. in BeforePost event. But doing so Oracle generates an error about missing ID (prim key that is). What would be the best method to increment the primary key ID? I use OracleDataset.

Any suggestions really appreciated, thank you

georgh

[This message has been edited by georgh (edited 18 May 2001).]

 

[georgh]

Just to add one generic question to my problem: How do other DB users see the record that I have inserted before them, but which I have not yet posted. Do they see the primary key value in their max() tests, if I have defined one for my record just after my insert (before my post)?

 

[MarkR]

If you can't use sequences, you can setup a table which has a column of type NUMBER in which you can store the last used or next ID. Run a SELECT ... FOR UPDATE query to retrieve the value (use FOR UPDATE to lock the record), then run an UPDATE query to change the value and commit. Do this as a separate transaction from your real DML statement(s) so that other users do not have to wait to get an ID. Use the value from the SELECT statement for your ID (adding 1 to it if what you store in the table is the last used ID).

 

[georgh]

Thanks, I guess I can use a simple variable instead of a table to store the SELECTed max(ID).