Title: Firebird Generator Helper Class
Question: Most of the time we need to create a event handler to assign to a dataset field a value obtained from a firebird/interbase Generator.
This class simplifies this work by adding just two lines of code to your application.
Answer:
This causes a TField called MyField to be assigned with the result number generated by the ID_GEN Firebird/Interbase generator, every time the field's dataset OnNewRecord event is triggered:
Create it on Data Module creation
FIdGenerator:= TIBGeneratorField.Create(MySQLConnection, MyField, 'ID_GEN');
And free it on Data Module destruction
FIdGenerator.free;
The class code:
-------------------------------
unit lDBExpress;
interface
uses SysUtils, Db, SqlExpr;
type
TIBGeneratorField = class
private
FField: TField;
FDataset: TDataset;
FGeneratorName: string;
FSQLConnection: TSQLConnection;
FOldOnNewRecord: TDataSetNotifyEvent;
FEnabled: boolean;
function GetGeneratorValue: integer;
procedure SetGeneratorValue(const Value: integer);
protected
procedure OnNewRecord(Dataset: TDataset); virtual;
public
constructor Create(AConnection: TSQLConnection; AField: TField; GeneratorName: String); virtual;
destructor destroy; override;
property GeneratorValue: integer read GetGeneratorValue write SetGeneratorValue;
property Enabled: boolean read FEnabled write FEnabled;
end;
implementation
{ TIBGeneratorField }
constructor TIBGeneratorField.Create(AConnection: TSQLConnection; AField: TField; GeneratorName: String);
begin
FEnabled:= true;
FSQLConnection:= AConnection;
FField:= AField;
FGeneratorName:= GeneratorName;
if assigned(AField) then
begin
FDataset:= AField.DataSet;
FOldOnNewRecord:= FDataset.OnNewRecord;
FDataset.OnNewRecord:= Self.OnNewRecord;
end;
end;
destructor TIBGeneratorField.destroy;
begin
if assigned(FDataset) then
FDataset.OnNewRecord:= FOldOnNewRecord;
FOldOnNewRecord:= nil;
inherited;
end;
procedure TIBGeneratorField.OnNewRecord(Dataset: TDataset);
begin
if FEnabled then
begin
with TSQLQuery.Create(nil) do
try
SQLCOnnection:= FSQLConnection;
SQL.Text:= format('select GEN_ID(%s, 1) as result from rdb$database',[FGeneratorName]);
Open;
FField.AsInteger:= FieldByName('result').AsInteger;
finally
free;
end;
end;
if assigned(FOldOnNewRecord) then
FOldOnNewRecord(FDataset);
end;
function TIBGeneratorField.GetGeneratorValue: integer;
begin
with TSQLQuery.Create(nil) do
try
SQLCOnnection:= FSQLConnection;
SQL.Text:= format('select GEN_ID(%s, 0) as result from rdb$database',[FGeneratorName]);
Open;
result:= FieldByName('result').AsInteger;
finally
free;
end;
end;
procedure TIBGeneratorField.SetGeneratorValue(const Value: integer);
begin
with TSQLQuery.Create(nil) do
try
SQLCOnnection:= FSQLConnection;
SQL.Text:= format('SET GENERATOR %s TO %d',[FGeneratorName, Value]);
ExecSQL;
finally
free;
end;
end;
end.