Mega Code Archive
How To Create An Auto Increment Key Using InterBase And Delphi
<![CDATA[
Title: How To Create An "Auto-Increment" Key Using InterBase And Delphi
Question: Creating an autoincrementing key using InterBase requires a little more work than Paradox, and if not done properly can create errors when using the BDE.
Answer:
Problems can occur when inserting data into an InterBase table with an autoincrement key using the BDE: it "loses" the record because the value of the primary key field (which it is using to keep track of the record) changes at he server, the client has no knowledge of the value that is inserted into the primary key field.
The solution (for an example table COUNTRY, that has a generated primary key
field of COUNTRY_ID) is:
ON THE SERVER:
1) create a generator
create generator g_country_id;
2) create the table
create table country(
COUNTRY_ID D_PRIMARY_KEY not null,
COUNTRY_NAME D_DESCRIPTION,
COUNTRY_ABRVN varchar(10),
COUNTRY_CODE D_SHORT_DESCRIPTION);
/* Primary Key */
alter table country add constraint PK_COUNTRY primary key (COUNTRY_ID);
/* Triggers */
/* this trigger will not usually be used, as the value of the COUNTRY_ID
will be defined
at the client, using the stored procedure p_get_country_id
It should be included anyway, for safety, standardization, etc. */
create trigger tbi_set_country_id for COUNTRY active before insert position
0 AS
BEGIN
IF (NEW.COUNTRY_ID IS NULL) THEN
NEW.COUNTRY_ID = GEN_ID(G_COUNTRY_ID, 1);
END
3) create a stored procedure that returns the generated key value:
create procedure p_get_country_id
returns (COUNTRY_ID integer)
as
BEGIN
COUNTRY_ID = GEN_ID(G_COUNTRY_ID, 1);
END
IN THE CLIENT CODE:
4) At the client, when a new record is created, define the primary key value using a stored procedure (the following assumes you are using Delphi, which one would have to be mad
not to... ):
Assumes a TTable (this process works just as well with TQuery objects) object name tblCountry and a TStoredProc object named spGetCountryID, in a TDataModule named dmCommon:
procedure TdmCommon.tblCountryNewRecord(DataSet: TDataSet);
begin
with spGetCountryID do
begin
ExecProc;
DataSet.FieldByName('COUNTRY_ID').AsInteger :=
ParamByName('COUNTRY_ID').AsInteger;
end;
end;
Some people prefer to put the code that calls the procedure and assigns the key in the BeforePost event: this is not wrong, just a matter of style.
Also, you can save some time/effort at the client by writing a shared procedure that all of your datasets call directly on the OnNewRecord event. Since the dataset is passed, you can change the Stored Procedure to be called based on the dataset name. It might make your code a little cleaner or easier to maintain, see below:
procedure TdmCommon.AllTablesGetKeyOnNewRecord(DataSet:TDataSet);
var
sFieldName : string;
spGetKey : TStoredProc;
begin
with DataSet do
begin
if Name = 'tblCountry' then
begin
sFieldName := 'COUNTRY_ID';
spGetKey := spGetCountryID;
end
else if Name = 'wwtblLanguage' then
begin
sFieldName := 'Language_ID';
spGetKey := spGetLanguageID;
end;
end;
with spGetKey do
begin
ExecProc;
DataSet.FieldByName(sFieldName).AsInteger :=
ParamByName(sFieldName).AsInteger;
end;
end;
]]>