I am using a Generator to create sequential ID numbers for most of my
tables. It works great.

However one table I have to create data in contains records that I want
to be able to keep sequentially contiguous. Its an INVOICE table.

The way I'm doing this is that when a user wants to create an INVOICE, I
create a record in that table and get an ID back from the generator
assigned to it. I then display an invoice form (this is via PHP on the
web) to the user to fill out.

Since I don't want two or more users creating an invoice with the same
ID, I immediately commit the record creation stored procedure that gives
me back the invoice number that I'm using. The record is then updated
with the actual fields entered by the user when they submit their update.

The problem occurs when a user elects to cancel or not complete the full
invoice creation process. In this case I will delete the invoice record
created, however I need to be able to 'recycle' the invoice number that
was given, so that it becomes the next candidate invoice number given to
the next user.

I'm thinking that the best way to do this would be to reset the
Generator to the number that was just recycled. That way when the user
who next wants to create an invoice gets a number, it will come from the
generator with that recycled number.

Is this a good way to do this, or am I going to find myself in problems?
And if it is a reasonable way, how can I change the generator's next
value in a stored procedure?

I tried this:

SET GENERATOR GEN_INVOICE_ID TO :A_RECYCLE_NO;

but it won't accept it, stating that its a parsing error.

What am I doing wrong?

Myles
--
=======================
Myles Wakeham
Director of Engineering
Tech Solutions USA, Inc.
Scottsdale, Arizona USA
http://www.techsolusa.com
Phone +1-480-451-7440