Using Generators with ODBC

From InterBase
Jump to: navigation, search

Go Up to Programming Applications with Delphi or C++ Builder


Using an InterBase trigger to change the value of a primary key on a table can cause the BDE to produce a record or key deleted error message. This can be overcome by adding a generator to your trigger.

For example, when your client sends a record to the server, the primary key is NULL. Using a trigger, InterBase inserts a value into the primary key and posts the record. When the BDE tries to verify the existence of the just-inserted record, it searches for a record with a NULL primary key, which it will be unable to find. The BDE then generates a record or key deleted error message.

To get around this, do the following:

  1. Create a trigger similar to the following. The “if” clause checks to see whether the primary key being inserted in NULL. If so, a value is produced by the generator; if not, nothing is done to it.
    Create Trigger COUNTRY_INSERT for COUNTRY
    active before Insert position 0
    as
    begin
         if (new.Pkey is NULL) then
         new.Pkey = gen_id(COUNTRY_GEN,1);
    end
    
  2. Create a stored procedure that returns the value from the generator:
    Create Procedure COUNTRY_Pkey_Gen returns (avalue INTEGER)
    as
    begin
       avalue = gen_id(COUNTRY_GEN,10);
    end
    
  3. Add a TStoredProc component to your Delphi or C++Builder application and associate it with the COUNTRY_Pkey_Gen stored procedure.
  4. Add a TQuery component to your application and add the following code to the BeforePost event:
    If(TQuery.state = dsinsert) then
    begin
        StoredProc1.ExecProc;
         TQuery.FieldByName('Pkey').AsInteger := StoredProc1.ParamByName('avalue').AsInteger;
    end;
    

This solution allows the client to retrieve the generated value from the server using a TStoredProc component and an InterBase stored procedure. This assures that the Delphi or C++Builder client will know the primary key value when a record is posted.