--- In firebird-support@yahoogroups.com, "Gaurav Sood"
<sood.gaurav@...> wrote:

>
> Hi Helen
>
> I've run through the program with the debugger and found that once we
> try to make a new person with the GUI, it calls the Person.pas file
> OnShow routine
>
> procedure TPersonDlg.FormShow(Sender: TObject);
> var
> personType: TPersonTypeEnum;
>
> begin
>
> { Check for new Person. }
> if Person < 1 then
> begin
> *** Person := NewPerson;
> Status := pdNew;
> end
> else
> Status := pdEdit;
>
>
> The *** Person := NewPerson; is an internal private function that does
> the below:
>
> function TPersonDlg.NewPerson: TPersonID;
> begin
> with ApplicationForm.ScratchQuery do
> begin
> { Create a new record in People. }
> Close;
> SQL.Clear;
> *** SQL.Add('Insert into People (ID) values (0)');
> ExecSQL;
>
> { Get ID for new Person. }
> Close;
> SQL.Clear;
> SQL.Add('Select max(ID) from People');

This is a really dumb way of assigning a new ID.

1) It is slow unless you have a descending index on the PK field

but more importantly

2) It does NOT work in a multi-transaction environment. Each
transaction has an isolated view of the database. If I called this
silly procedure and it gave me ID 100, which I then inserted but have
not yet committed, then you call the procedure, you will also be given
ID 100 to use, and you will get the error you received before.

In Firebird, use generators to assign numbers that are guaranteed to
be unique.

Some initial SQL to create and initialise the generator

CREATE GENERATOR GEN_PEOPLEID;
SET GENERATOR GEN_PEOPLEID TO 100;
(change 100 to the maximum value currently allocated)

And change the query to:

SQL.Add('Select gen_id(GEN_PEOPLEID,1) from RDB$DATABASE');

This will then ALWAYS give you a unique number for your person ID.

Adam