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

From: Paal M. Østby <paal_at_xplore.no>
Date: 1997/09/24
Message-ID: <01bcc8f7$20b8f1c0$0c01a8c0@PMA.xplore.no>#1/1

Ger Otten <gmjotten_at_turnkiek.nl> wrote in article <01bcc673$bbac4820$2589f1c3_at_ger>...
> At the moment I am developing a client/server application with delphi 3
> Client/Server and Oracle 7.3.3.
> To make the application as fast as possible I want to use numeric primary
> keys.
>
> Oracle has the possibilty to generate sequence numbers that can be used
 as
> the value for the primary key in a table.
> For instance:
>
> Create sequence order_seq
> start with 1
> increment by 1
> nocylce
> cache 20;
>
>
> At the moment I insert a new record in a table I can reference the
 sequence
> number with NEXTVAL.
> For instance:
>
> 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 ?

  Yes and no.
  Yes, but then the field containing the sequence number must be allowed to   accept NULLs and hence not be a part of a primary key. Or you insert a   dummy value ex 99999 which you then exchange with the sequence number in a trigger.
  If this is in a multiuser environment, this solution is probably not the best.

>
> 2. If this is not possible I would like to know the following : When I
 have
> a table open in a grid open in Delphi and I am in the edit
> mode. When I insert a record can I then get the next sequence number from
> Oracle and fill it myself in the table ? How do I do that.

Try this:

{given that all sequences are created in a certain manner, you may find this function useful}   

function GetNextID (dbOwner, table_name:String ) : Double ; var
  nextID : string ;
begin
  nextID := dbOwner + '.SEQ_'+ table_name + '.NEXTVAL' ;    with qryDual do
   begin

     sql.clear ;
     sql.add('SELECT');
     sql.add(nextID);
     sql.add('FROM DUAL');
     open ;
     Result := Fields[0].AsFloat  ;
     close ;

   end ;
end ;

On the BeforeInsert event write something like:

   Query.FieldByName('ID').AsFloat := GetNextID(OWNER,CUSTOMER);

This will work fine except for one small catch: Oracle caches sequences in order to speed up performance.
If it is critical that you do not get holes in your numbering sequence, like for invoices, the whole method of using Oracle sequences may need to be reconsidered.

> 3. Maybe both are possible: What do you prefer ?

 Method 2.

regards
Paal

>
>
> Thank you very much in advance !!
>
> sincerely,
>
> Ger Otten
>
> email: gmjotten_at_worldonline.nl
>
>
Received on Wed Sep 24 1997 - 00:00:00 CDT