I have a an web app in development with Firebird on back end for data. It will support multiple clients. The first 2 are related, but insist on having unique order #'s. Normally, I just use a generator and keep track of spoils, but that works when its a single company. The order has a primary key that is unique across the system, though all tables contain a companyid column for segregating the data. i.e. companyid, recordid, OrderNo, field1, field2 etc....

So, I am thiking of implementing a table to hold certain sequences. The Primary key would be companyid and the columns would be the next order #.

What I am thinking I need is a stored proc that will get next value by doing following:

Query the record for update, lock it, increment value, update record release lock and return the incremented value to calling app. The Same storedproc may also log the value returned by having the caller pass in the primary key of record it is requesting an order value for.

Is this even possible to do? Has anyone encountered this problem? I really do not wish to add generators for each client for Order , invoice and other sequence values.

Anyway, I am open to any suggestions. In the meantime, I will experiment.

-L