Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Read generator field of firebird database.

Posted on 2007-08-08
14
Medium Priority
?
1,609 Views
Last Modified: 2013-12-09
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
Comment
Question by:Alex
  • 8
  • 6
14 Comments
 
LVL 21

Expert Comment

by:ziolko
ID: 19652015
0
 
LVL 21

Expert Comment

by:ziolko
ID: 19652031
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
 
LVL 9

Author Comment

by:Alex
ID: 19652043
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 21

Expert Comment

by:ziolko
ID: 19652049
sure, which delphi version you use and which DB components?

ziolko.
0
 
LVL 9

Author Comment

by:Alex
ID: 19652069
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
 
LVL 21

Expert Comment

by:ziolko
ID: 19652108
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
 
LVL 9

Author Comment

by:Alex
ID: 19652120
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
 
LVL 21

Expert Comment

by:ziolko
ID: 19652141
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
 
LVL 9

Author Comment

by:Alex
ID: 19652155
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
 
LVL 21

Expert Comment

by:ziolko
ID: 19652169
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
 
LVL 9

Author Comment

by:Alex
ID: 19652205
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
 
LVL 21

Accepted Solution

by:
ziolko earned 2000 total points
ID: 19652221
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
 
LVL 9

Author Comment

by:Alex
ID: 19652268
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
 
LVL 21

Expert Comment

by:ziolko
ID: 19652333
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

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Lotus Notes has been used since a very long time as an e-mail client and is very popular because of it's unmatched security. In this article we are going to learn about  RRV Bucket corruption and understand various methods to Fix "RRV Bucket Corrupt…
Creating a Cordova application which allow user to save to/load from his Dropbox account the application database.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
Suggested Courses

572 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question