Re: Generating the next sequential key...

> I have this app which is relatively database independent. I would like
> to serve the smallest scale (MS Access) up to the largest database
> server (Oracle).

No app is truly database independent. Even if you use ANSI-SQL syntax supported on all of your database platforms, the moment you send that SQL statement to the database, you are at the mercy of that specific RDBMS. No two RDBMS's will execute that statement the same. Try running EXISTS/NOT EXISTS and IN/NOT IN on both SQL Server and Oracle to see how the two engines treat these differently.

> I recoginize that I will need to write database specific code for
> Oracle i.e. generate the next number from a sequence.

If you are going to write database specific code, then what are the other options for?

> For the rest I pondered this idea...
>
> a) Have a table with a row per database table and the last number used
> in it.
> pro's: nice sequential number
> con's: contention to get the next number (maybe grab a few at a time
> ugh)
>
> b) Generate a random number between 1 and 1000000000
> pro's: very fast
> con's 1 in a billion chance to create a duplicate (maybe try the
> insert again if it happens?)
>
> Are there other strategies I am missing?

In Oracle, I'd use a sequence. In SQL Server and MS Access, I'd use the autoincrementing column in a table. In SQL Server, you can also use a Globally Unique Identifier (GUID).

You can implement your own solutions, but you would be reinventing the wheel and in many cases, creating an application bottleneck.

HTH,
Brian

-- 
===================================================================

Brian Peasland
oracle_dba_at_nospam.peasland.net
http://www.peasland.net

Remove the "nospam." from the email address to email me.


"I can give it to you cheap, quick, and good.
Now pick two out of the three" - Unknown