Subject Re: [firebird-support] Sequential auto incremental numbering
Author River~~
Post date 2018-09-03T10:00:12Z
Two ideas that should work on any SQL database so long as you never delete invoices

I amNot a guru for this dbms so if a more specific technique is posted it is likely to be faster

Note however that while my method may be inefficient, it is more likely to be portable. You pays your money and you takes your choice


With SQL and code in another language:

Start transaction
Select max (invoice) ...
Calculate next invoice 
Insert...
Commit

Or if you want to do it all in SQL then create a view that selects max invoice plus one, and insert one row at a time from that view into the table. The other columns would then be presented as calculated values 

Either of the above are reasonably efficient if you have an index on the invoice number, as firebird can easily find the first and last entries from the index and iirr if the only column retrieved is the indexed value firebird does not even access the table. Like if the last word in an index to a book is zzxxxly, the index entry

zzxxxly p146

tells you the word appears on p146 but you don't need to turn to that page if you only wanted to know that word.

On Sun, 2 Sep 2018, 22:39 'Christian Giesen' chris@... [firebird-support], <firebird-support@yahoogroups.com> wrote:
 

Good day,

 

Using Firebird 3.0.3.

RAD Studio 10.2.2 (Tokyo)

 

I have a need to generate unique sequential invoice/credit note numbers. I fully understand the usage of generators to produce unique identifiers in tables. However, according to Firebird documentation the use of generators does NOT guarantee sequential numbering. I don’t think that this is too difficult to implement but I need to know how to get the last generated ID value so that after inserting a new invoice I can assign the next sequential invoice number to this record.

 

In my table I have a number of fields but the ones of interest here are:

 

The new way of implementing auto incremental numbering.

ID – INTEGER AUTOINCREMENT IDENTITY PRIMARY KEY

INVOICE_NO – INTEGER NOT NULL

 

The traditional way of implementing auto incremental numbering.

ID – INTEGER AUTOINCREMENT PRIMARY KEY (Using a visible editable generator)

INVOICE_NO – INTEGER NOT NULL

 

My question: I wish to obtain the last generated value for the ID field.

 

1.       The new IDENTITY implementation ‘hides’ the generator so how do I get this value using this method.

2.       With the traditional way I could get this value by querying the tables generator value + 1.

 

I would imagine that I would then update the current invoice by using the AFTYER INSERT trigger to update the INVOICE_NO field by identifying  this record from its last generated ID.

 

So my code would look something like this:

 

DECLARE VARIABLE NEXT_INVOICE_NO INTEGER;

DECLARE VARIABLE LAST_ID INTEGER;

 

<Get the last generated ID and assign it to the LAST_ID variable>

<Get the next invoice number by using MAX(INVOICE_NO) form the invoice table and assign it to the NEXT_INVOICE_NO variable>

<Update the INVOICE table and set the INVOICE_NO field value to the NEXT_INVOICE_NO variable value>

 

A snippet of sample code as to how to implement this would be greatly appreciated.

 

Thank you for your assistance.

 

Kind regards,

 

 

Christian Giesen

Managing Member

Express Talent cc

 

Mobile: +27 82 574 5566

E-Mail:   chris@...

Alt Email: cvgiesen@... or cvgiesen@...

 

This message contains private and confidential Information. If you are not the intended addressee indicated in this message or you are not responsible for the delivery of such messages to the intended recipient, you may not copy or deliver this message to anyone. You may not use any information gleaned from this message for the benefit or promotion of yourself or your organization. If such is the case, please destroy this message immediately and kindly notify the sender by return of E-Mail. Express Talent cc takes no responsibility whatsoever resulting in you misusing information contained in this message and doing so may render you liable for the consequences of misusing said content of this E-Mail message.