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.
Top DevX Stories
Easy Web Services with SQL Server 2005 HTTP Endpoints
JavaOne 2005: Java Platform Roadmap Focuses on Ease of Development, Sun Focuses on the "Free" in F.O.S.S.
Wed Yourself to UML with the Power of Associations
Microsoft to Add AJAX Capabilities to ASP.NET
IBM's Cloudscape Versus MySQL