Good day Alan,

 

Thanks for your response. As I mentioned the issue is not to generate unique ID 
values. This is taken care of by using generators (as you correctly mention) 
or, now with Firebird 3, I have implemented the IDENTITY method of generating 
unique ID values. The issue that I have is that the generation of ID values via 
generators or the IDENTIY method does NOT guaranty sequential numbering. This 
is identical to MS SQL server’s IDENTITY field type as well as their sequence 
generators.

 

An invoice system HAS TO guarantee sequential numbering! I dug a bit deeper 
into this over the weekend and have successfully implemented this as follows:

 

1.       The INVOICE table contains and ID field (INTEGER, PRIMARY KEY, AUTO 
INCREMENT AS IDENTITY) as well as an INVOICE_NO field. This field stores the 
sequential invoice number.

2.       I have implemented an AFTER INSERT trigger on the INVOICE table as 
follows:

DECLARE VARIABLE NextInvNo INTEGER;

BEGIN

  NextInvNo = (SELECT MAX(INVOICE_NO) FROM INVOICE) + 1;

 

  IF (NextInvNo IS NULL) THEN

    NextInvNo = 1;

 

  UPDATE INVOICE

    SET INVOICE_NO = :NextInvNo

  WHERE

    ID = NEW.ID;

END

 

You definitely have given me another way of implementing this.

 

Thanks again for your input.

 

 

Kind regards,

 

 

Christian Giesen

Managing Member

Express Talent cc

 

Mobile: +27 82 574 5566

E-Mail:    <mailto:ch...@xt.co.za> ch...@xt.co.za

Alt Email:  <mailto:cvgie...@gmail.com> cvgie...@gmail.com or  
<mailto:cvgie...@protonmail.com> cvgie...@protonmail.com

 

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.

 

From: firebird-support@yahoogroups.com <firebird-support@yahoogroups.com> 
Sent: Monday, 03 September 2018 01:03
To: firebird-support@yahoogroups.com
Subject: Re: [firebird-support] Sequential auto incremental numbering

 

  

Hi Christian, this is a snippet from a long-time working version with 
several users creating invoices and no problems experienced. This 
invoice number is then available in Delphi.
.....
InvHeadInsert.ExecProc;
locInv_Nmbr:=InvHeadInsert.Params.ParamValues['new_inv_nmbr'];
.....

There is a trigger and SP as follows:

CREATE OR ALTER trigger tr_inv_nmbr for invhead
active before insert position 0
AS
begin
new.inv_nmbr=gen_id(inv_nmbr_gen,1);
new.inv_date=current_date;
new.del_time=current_time;
end

create or alter procedure INVHEAD_INS (
ACNO type of ACCOUNT_NO,
NO_BOXES type of DECIMALS_0)
returns (
NEW_INV_NMBR type of DECIMALS_0)
as
begin
insert into invhead (
acno,no_boxes)
values (
:acno,:no_boxes)
returning inv_nmbr
into :new_inv_nmbr;
end

I have not tried with FB3, this is 2.5, but I hope it helps.
Alan J Davies
Aldis

On 02/09/2018 16:03, 'Christian Giesen' ch...@xt.co.za <mailto:ch...@xt.co.za>  
[firebird-support] 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: ch...@xt.co.za <mailto:ch...@xt.co.za>  <mailto:ch...@xt.co.za>
> 
> Alt Email: cvgie...@gmail.com <mailto:cvgie...@gmail.com>  
> <mailto:cvgie...@gmail.com> or 
> cvgie...@protonmail.com <mailto:cvgie...@protonmail.com>  
> <mailto:cvgie...@protonmail.com>
> 
> 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.
> 
> 



  • Re: [fire... Alan J Davies alan.dav...@aldis-systems.co.uk [firebird-support]
    • RE: ... 'Christian Giesen' ch...@xt.co.za [firebird-support]
    • Re: ... 'River~~' river14ap...@gmail.com [firebird-support]
      • ... Ann Harrison aharri...@ibphoenix.com [firebird-support]
        • ... 'Louis van Alphen' lo...@nucleo.co.za [firebird-support]
          • ... 'River~~' river14ap...@gmail.com [firebird-support]
    • RE: ... liviuslivius liviusliv...@poczta.onet.pl [firebird-support]
    • Re: ... Gabor Boros mlngl...@bgss.hu [firebird-support]
      • ... Ann Harrison aharri...@ibphoenix.com [firebird-support]

Reply via email to