I need to code a function that returns the value of the next id field
in a table(something like sequence.nextVal does in Oracle).
This might look pretty easy but there are many different ways to do it.
I'll list bellow some I've thought of:
1) I create for each table that has an autoinc field another table with one field that stores the actual id value(somehow stupid simulation of a sequence)
2) I create a table and for each table that has an autoinc field I add a field that stores the actual id value to that table
This must be accompanied by a class that provides the functionality
(like getVal, getNextVal and why not setVal).
Multiple ways of doing the things as well:
1) The value is updated/read directly from the table(on getVal a SELECT, on getNextVal an UPDATE and a SELECT will be executed)
2) I use a private member that stores the value. That member is set within the contructor to the maximum value of the autoinc field in the associated table and the methods will read/update that value. In this case there is no requirement for a table to store the value but some problems might arise.
Hmm, I can't say that I am decided, so maybe some of you can point me to a better solution or tell me what's wrong with any of the ideas presented above or just give me some advice.
PS: I need this because there is no standard solution in db engines for such a thing and I need my app run on many db engines.