Programming Challenge: Multiple User Definable Sequences

[pbakker]

Hi all,

I've got a programming challenge for you. At least for me it's a challenge, since I'm not quite sure how to do what I want to do.

What I need is multiple user definable sequences stored withing one column of one table. And this sequence also has to be useable from outside Servoy, for external programs inserting records....

Let me elaborate:

I have, for example, a customer table. On the table is a field customerid, which is just an automatic database sequence. I also have the fields CustomerNO and CompanyType.

Now, the CustomerNO field has to be filled with a sequential number, formatted in a custom way (for example: Com1-xxxxxxx-yyyyMMdd, where the Com1 part is just text, the xxxxxxxxx is the value of my sequence and the yyyyMMdd part is a datestamp).

The formatting of the CustomerNo has to have the oppertunity to be different for each companytype and also with a different sequence.

Also, If I would create a new customerrecord in a transaction and cancel the transaction (so actually no customerrecord is created) the customerNO sequence cannot be affected.

And last but not least: this whole logic needs to work from within Servoy, but should also be accessible for other programs creating new records in the customertable.

Now, what I have thought of so far is the following:
=================
Create a table SEQUENCE with for every sequence I want to have one record (fields: SequenceID, Description, Value, Format).

Then create a procedure in the DB (which I can access from Servoy or any other program), which takes as input the SequenceID for which I want a new value,

Then takes the value from the Value field for the matching sequence record, adds 1 to it (Value = Value + 1),

Through some clever SQL-ing I also format the value (nesecarry SQL is stored in the Format field of the record in the SEQUENCE table, and evaluated in the Procedure)

And as output feeds back the new value and the formatted value, with which I then update the CustomerNo with.
=================

Would this be the way to go, or are there other (better) ways to achieve the same?

If this is the way to go, is this way of working foolproof when two different users create/store a new customerrecord at the same time, to the procedure is called at the same time as well?

Any thoughts appreciated....

Paul