Next identity value

[ejgb]

I have a table with an identity column and would like to get the next value that it will use programmable before inserting into the table.

 

[DSummZZZ]

As vague as this question is, all I can say is do something like:

STORE identity + whatever TO NewValue
WAIT WINDOW TRANSFORM(NewValue)

Then you can do:
REPLACE identity WITH NewValue

Unless it's an auto increment. Then you'll just have to check the current value and assume it's going to be one more.


-Dave Summers-

Even more Fox stuff at:

http://www.davesummers.net/foxprolinks.htm

 

[ejgb]

Thanks, the column is an auto increment so you have to make the assumption that the next ID is going to be (1 + max(ID column))?

 

[JRB-Bldr]

If is true that merely using the MAX() function will result in the last used ID and that value can indeed be incremented for subsequent use.

BUT - If there were to be a likelihood of multiple individuals 'simultaneously' adding records then you need to be cautious.

If 2 individuals were to calculate the next ID off of the last used one, they could possibly calculate the same value.

However when they finally hit the COMMIT button, the other person might have already done so and 'consumed' that next ID - resulting in the need for yet another ID.

The best way might be to 'reserve' a record and its associated ID by using an APPEND BLANK - possibly adding a temporary field value such as "RESERVED"

Then with that record created, the user's application would read back the newly created ID and use it for subsequent work.

Good Luck,
JRB-Bldr

 

[Mike Lewis]

Hi Ejgb,

You can't know what the next value will be before you've inserted the record. There's no guarantee that another user or another application won't grab the value before you do your insert. (Unless you lock the table or open it exclusively of course.)

Generally, you insert the record first, and then use GETAUTOINCVALUE() to find the ID that was generated.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro tips, advice, training, consultancy
Custom software for your business

 

[Olaf Doschke]

I'll second Mike,

there really is no use in guessing. Really add the record and work with the really generated ID is the only way you can be sure you'll have no flaw.

Especially if you need the "estimated" ID as foreign key, you'd just spread a probable error. Work with -1,-2,-3 etc as temporary IDs instead and do a cascading update, when you finally save and the database creates the real key.

The only situation where you can be sure the next id will be max(ID)+1 is, when you do a single user app.

Bye, Olaf.

 

[JRB-Bldr]

Since both Mike & Olaf are saying what I said above but in a slightly different manner, I'll 'ditto' them.

Good Luck,
JRB-Bldr