• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1730
  • Last Modified:

Read generator field of firebird database.

i am new in firebird and i develop an application in delphi.I want to know if it there is a way from delphi  to read from the generators category of a firebird database a field so to be able to know any time what value has and if i want to deleted or to update this value.

For example when we create a table in firebird at the same time if we have an auto increment field it produce a GEN_ID field.Let's say i have a table people and i have to the generators the auto increment field
PEOPLE_ID_GEN.I want to be able to read from delphi the current value of the PEOPLE_ID_GEN and to update or delete this value with sql commands throw delphi.

Thanks in advance!

0
Alex
Asked:
Alex
  • 8
  • 6
1 Solution
 
ziolkoCommented:
0
 
ziolkoCommented:
in your case:

current value
select GEN_ID(PEOPLE_ID_GEN, 0) from RDB$DATABASE

next value
select GEN_ID(PEOPLE_ID_GEN, 1) from RDB$DATABASE

explicit set:
SET GENERATOR PEOPLE_ID_GEN TO <NewValue>

drop:
DROP GENERATOR PEOPLE_ID_GEN

ziolko.
0
 
AlexSoftware EngineerAuthor Commented:
could you please make me a full example how can i make all this

==========
current value
select GEN_ID(PEOPLE_ID_GEN, 0) from RDB$DATABASE

next value
select GEN_ID(PEOPLE_ID_GEN, 1) from RDB$DATABASE

explicit set:
SET GENERATOR PEOPLE_ID_GEN TO <NewValue>

drop:
DROP GENERATOR PEOPLE_ID_GEN
=============

with delphi code?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
ziolkoCommented:
sure, which delphi version you use and which DB components?

ziolko.
0
 
AlexSoftware EngineerAuthor Commented:
delphi 5 and 7 (if has differences could you make me 2 examples for each versions?) and the components from interbase : ibquery,ibdatabase, a datasource and ibtransaction.

also when i read the value of the generator i want to place it in a label caption.

Thank you Ziolko.
0
 
ziolkoCommented:
pas file:
unit Unit1;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, DB, DBTables, StdCtrls, IBDatabase, IBCustomDataSet, IBQuery;

type
  TForm1 = class(TForm)
    Button1: TButton;
    IBQuery1: TIBQuery;
    IBDatabase1: TIBDatabase;
    IBTransaction1: TIBTransaction;
    procedure Button1Click(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  Form1: TForm1;

implementation

{$R *.dfm}

procedure TForm1.Button1Click(Sender: TObject);
begin
  IBDatabase1.Open;
  IBQuery1.SQL.Add('select GEN_ID(ID_SPRZ, 0) from RDB$DATABASE');
  try
    IBQuery1.Open;
    ShowMessage(Format('current value is: %s', [IBQuery1.FieldByName('GEN_ID').AsString]));
  finally
    IBQuery1.Close;
  end;
end;

end.

dfm file:
object Form1: TForm1
  Left = 192
  Top = 201
  Width = 696
  Height = 480
  Caption = 'Form1'
  Color = clBtnFace
  Font.Charset = DEFAULT_CHARSET
  Font.Color = clWindowText
  Font.Height = -11
  Font.Name = 'MS Sans Serif'
  Font.Style = []
  OldCreateOrder = False
  PixelsPerInch = 96
  TextHeight = 13
  object Button1: TButton
    Left = 60
    Top = 76
    Width = 75
    Height = 25
    Caption = 'Button1'
    TabOrder = 0
    OnClick = Button1Click
  end
  object IBQuery1: TIBQuery
    Database = IBDatabase1
    Transaction = IBTransaction1
    BufferChunks = 1000
    CachedUpdates = False
    UniDirectional = True
    Left = 60
    Top = 184
  end
  object IBDatabase1: TIBDatabase
    DatabaseName = 'C:\DesignTime\FN.fdb'
    Params.Strings = (
      'user_name=fn'
      'password=****')
    LoginPrompt = False
    DefaultTransaction = IBTransaction1
    IdleTimer = 0
    SQLDialect = 1
    TraceFlags = []
    Left = 108
    Top = 184
  end
  object IBTransaction1: TIBTransaction
    Active = False
    DefaultDatabase = IBDatabase1
    AutoStopAction = saNone
    Left = 164
    Top = 184
  end
end

all you have to do is type in valid password

ziolko.
0
 
AlexSoftware EngineerAuthor Commented:
procedure TForm1.Button1Click(Sender: TObject);
begin
  IBDatabase1.Open;
  IBQuery1.SQL.Add('select GEN_ID(ID_SPRZ, 0) from RDB$DATABASE');
  try
    IBQuery1.Open;
    ShowMessage(Format('current value is: %s', [IBQuery1.FieldByName('GEN_ID').AsString]));
  finally
    IBQuery1.Close;
  end;
end;

this is work perfect!

if i want to update this value or delete or whatever i 'll just change this:

 IBQuery1.SQL.Add('select GEN_ID(ID_SPRZ, 0) from RDB$DATABASE');

to update or delete statement?Is that right?

0
 
ziolkoCommented:
not quite:)
of course you have to change:
IBQuery1.SQL.Add('select GEN_ID(ID_SPRZ, 0) from RDB$DATABASE');

delete statement do not return records so you have to remove:
ShowMessage(Format('current value is: %s', [IBQuery1.FieldByName('GEN_ID').AsString]));

and change: IBQuery1.Open; to IBQuery1.ExecSQL;

ziolko.
0
 
AlexSoftware EngineerAuthor Commented:
look this...

procedure TForm1.Button2Click(Sender: TObject);
begin

 IBQuery1.SQL.Add('SET GENERATOR PEOPLE_ID_GEN TO <NewValue>); <-- here which is the correct syntax?

  try
    IBQuery1.ExecSQL;
  finally
    IBQuery1.Close;
  end;
end;
0
 
ziolkoCommented:
yup, it's ok you need to insert desired value in place of <NewValue>

this:
IBQuery1.SQL.Add('SET GENERATOR PEOPLE_ID_GEN TO 446000);
will set
PEOPLE_GEN_ID  to 446000

ziolko.
0
 
AlexSoftware EngineerAuthor Commented:
ok everything it is fine now but when i read and then i update the value and after this go to read again with select statement the new value i get an error for the select statement.What is going wrong?

0
 
ziolkoCommented:
drop TMemo on your form and run this:

procedure TForm1.Button1Click(Sender: TObject);
begin
  Memo1.Clear;
  IBDatabase1.Open;
  try
    IBQuery1.SQL.Clear;
    IBQuery1.SQL.Add('select GEN_ID(PEOPLE_ID_GEN, 0) from RDB$DATABASE');
    IBQuery1.Open;
    Memo1.Lines.Add(IBQuery1.FieldByName('GEN_ID').AsString);
    IBQuery1.SQL.Clear;
    IBQuery1.SQL.Add('SET GENERATOR PEOPLE_ID_GEN TO 446001');
    IBQuery1.ExecSQL;
    IBQuery1.SQL.Clear;
    IBQuery1.SQL.Add('select GEN_ID(PEOPLE_ID_GEN, 0) from RDB$DATABASE');
    IBQuery1.Open;
    Memo1.Lines.Add(IBQuery1.FieldByName('GEN_ID').AsString);
  finally
    IBQuery1.Close;
    IBDatabase1.Close;
  end;
end;


ziolko.
0
 
AlexSoftware EngineerAuthor Commented:
thanks a lot ziolko you are great man!!!!

maybe i'll need you again in the future if i want some help in firebird/delphi ;)
0
 
ziolkoCommented:
i'm glad i could help you:)
you can ask as many Qs as you want, that's what experts are here for:)

ziolko.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

  • 8
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now