Re: Oracle Primary key and Delphi: How to insert the primary key value automatically ?

From: Alexey A. Vekshin <vekshin_at_usa.net>
Date: 1997/10/05
Message-ID: <3437614A.3498D8AB@usa.net>#1/1

Hi Robert,

Robert Altena wrote:

> >> Insert into orders (orderno, custno)
> >> values (order_seq.NEXTVAL, 1032)
> >>
> >> Questions:
> >>
> >> 1. Is it possible to use Oracle Database triggers to automatically fill in
> >> the next sequence number in the primary key field ?
> >> If so how can this be achieved ?
>
> Triggers are not the way to do this. A stored procedure (which takes almost
> all data to be inserted as parameters will do the trick)

It's not very convenient to use SP for that - you have to do to much work of
filling params array. Also, generating PK this way is rather inconvinient
then you need to manipulate your table not from Delphi - suppose, import something
to it from external datafile or so.

> Just my $0.02: DON'T use the insert/update capabilities of any data-bound
> control.
> Write your own INSERT and UPDATE sql.

You mean cached updates ? This capability of BDE is rather buggy - it works OK up
 to the first error from server - e.g. after attempt to delete master record with
 RI enabled and children records exists. You got error message, perform .CancelUpdates -
 and in most cases 'Update failed' is the only result of subsequent calls to .ApplyUpdates.
 On examination of update buffer one can see that unsuccessful delete is still in
 update buffer (.Cancel won't help) - and BDE will try to apply it with maniacal
 stubbornness. The only way to recover from errors i've found for now is  GetBookMark/Close/Open/GotoBookMark after any error in applying updates.

> >> 3. Maybe both are possible: What do you prefer ?
> >>
> Use a TQuery component with a " ... values ( seq_PK.Nextval..." clause

IMHO PK generation is the task to be performed by DB itself, not by application program - it's
better style for DB design.

After some experimentation with DBTables.pas TTable internals I realised that it's nearly
impossible to tell BDE new PK for your just-posted record. It's almost worked - but data-aware
components like dbgrid heavily relies on bookmarks for positioning in dataset - so my bright ideas
of overriding TTable.InternalPost and supply it with correct value of PK _after_ actual
post turned to be useless.

Now i use following to deal with
sequence-ID-trigger-PK-generation-unknown-to-Delphi problem:

at Oracle:

• 8< testpack.sql 8< -------

CREATE OR REPLACE package TestPack as

  CurrSuppOrder number := -1;
  procedure GetNextSuppOrdID(ID out number, FromT in boolean default false);

  NotYet exception;
  pragma exception_init(NotYet, -8002);

end TestPack;

CREATE OR REPLACE package body TestPack as

  procedure GetNextSuppOrdID (ID out number, FromT in boolean default false) is

    NewID number;
 /* take ID from sequence for SuppOrder */   begin
    begin

      select SuppOrderSeq.CurrVal into NewID from dual;
     exception
      when NotYet then
        select SuppOrderSeq.NextVal into NewID from dual;

    end;
    if NewID = CurrSuppOrder then
      select SuppOrderSeq.NextVal into NewID from dual;     end if;

    ID := NewID;
    if FromT then CurrSuppOrder := NewID; end if;   end GetNextSuppOrdID;

end TestPack;

create or replace trigger TI_SuppOrd
  BEFORE INSERT on SuppOrder for each row -- generate unique IDs for SuppOrder
begin
  :new.DateConf := null;
  TestPack.GetNextSuppOrdID(:new.SuppOrdID, true); end;
/

• 8< testpack.sql 8< -------

in delphi:

• 8< unit1.pas 8< -------

function GetSuppOrdID: longint;
begin
  spSuppOrdID.ExecProc;
  result := spSuppOrdID.ParamByName('ID').AsInteger; end;

procedure TForm1.MyTableNewRecord(Sender: TDataSet); begin
  MyTable['ID'] := GetSuppOrdID;
end;

• 8< unit1.pas 8< -------

As you see, main ideas are
1) to use same procedure from trigger and from delphi 2) to return same number from this procedure till actual insert will take place.

Maybe it's a little bit over-complicated, but I like it :)

	Regards,
		Alexey.

Received on Sun Oct 05 1997 - 00:00:00 CDT