Solved

Extract DML in Firebird1.5

Posted on 2004-04-07
18
598 Views
Last Modified: 2008-02-20
hi, people  
 
I need to extract the Data Manipulation Language (DML- INSERTS ,UPDATE,...) of the FireBird 1.5. I would like save in file like "bd.sql" .
I saw in the operations guide and tried some commands  that not worked.  
I tried gbak and isql commands  it's extract only metadata (Data Definition Language-DDL) not extract the data.  
Somebody Knows tools that does those operation. Or Knows some command or arguments that work.  
 
Thanks  
 
--Elielson
0
Comment
Question by:emaia
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 5
18 Comments
 
LVL 12

Expert Comment

by:esoftbg
ID: 10779111
For backup and restore INTERBASE / FIREBIRD database I am using Delphi components:
TIBBackupService and TIBRestoreService from InterBase Admin palette.
0
 
LVL 12

Expert Comment

by:esoftbg
ID: 10779148
These components do backup / restore of the metadata and the data.

emil
0
 

Author Comment

by:emaia
ID: 10780230
Ok Emil,

I'll try this solution....

thanks
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 12

Expert Comment

by:esoftbg
ID: 10789652
May be you need an example ?

unit Unit_Q_20947052;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, Buttons, IBServices, IBDatabase, DB, StdCtrls;

type
  TForm1 = class(TForm)
    SpeedButton1: TSpeedButton;
    Memo: TMemo;
    IBDB: TIBDatabase;
    IBTC: TIBTransaction;
    procedure SpeedButton1Click(Sender: TObject);
  private   { Private declarations }
  public    { Public declarations }
    procedure BackUpDataBase;
  end;

var
  Form1: TForm1;

implementation

uses DBModule;

{$R *.dfm}

procedure TForm1.BackUpDataBase;
var
  B:             Boolean;
  IBBackup:      TIBBackupService;
  SGDB:          string;
  SGBK:          string;
begin
  B := True;
  IBBackup := TIBBackupService.Create(Self);
  try
    IBBackup.BackupFile.Clear;
    SGDB := ExtractFilePath(Application.ExeName) + 'CATALOGS1251.GDB'; // Data.IBDB.DatabaseName;
    if IBDB.Connected then
    try
      IBDB.CloseDataSets;
        IBDB.Close;
    except
      B := False;
    end;
    if B then
    begin
      IBBackup.ServerName := 'Poulet';
      IBBackup.LoginPrompt := False;
      IBBackup.Params.Add('user_name=SYSDBA');
      IBBackup.Params.Add('password=masterkey');
      IBBackup.Active := True;
      try
        IBBackup.Verbose := True;
        IBBackup.Options := [NonTransportable, IgnoreLimbo];
        IBBackup.DatabaseName := SGDB;
        SGBK := ChangeFileExt(SGDB, '.GBK');
        IBBackup.BackupFile.Add(SGBK);
        IBBackup.ServiceStart;
        while not IBBackup.EOF do
          Memo.Lines.Add(IBBackup.GetNextLine);
      finally
        IBBackup.Active := False;
      end;
    end;
  finally
    IBBackup.Free;
    IBDB.DataBaseName := SGDB;
    IBDB.Open;
  end;
end;

procedure TForm1.SpeedButton1Click(Sender: TObject);
begin
  BackUpDataBase;
end;

end.

//...........................

object Form1: TForm1
  Left = 215
  Top = 114
  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 SpeedButton1: TSpeedButton
    Left = 18
    Top = 20
    Width = 96
    Height = 22
    Caption = 'Backup'
    OnClick = SpeedButton1Click
  end
  object Memo: TMemo
    Left = 118
    Top = 4
    Width = 551
    Height = 435
    TabOrder = 0
  end
  object IBDB: TIBDatabase
    DatabaseName = 'CATALOGS1252.GDB'
    Params.Strings = (
      'user_name=SYSDBA'
      'password=masterkey'
      'lc_ctype=WIN1252')
    LoginPrompt = False
    DefaultTransaction = IBTC
    IdleTimer = 0
    SQLDialect = 1
    TraceFlags = []
    Left = 16
    Top = 46
  end
  object IBTC: TIBTransaction
    Active = False
    DefaultDatabase = IBDB
    AutoStopAction = saNone
    Left = 64
    Top = 46
  end
end
0
 
LVL 12

Expert Comment

by:esoftbg
ID: 10789708
please remove:

>uses DBModule;

it is from my project
0
 
LVL 12

Accepted Solution

by:
esoftbg earned 100 total points
ID: 10800052
An improved version:

unit Unit_Q_20947052;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, Buttons, IBServices, IBDatabase, DB, StdCtrls;

type
  TForm1 = class(TForm)
    SpeedButton1: TSpeedButton;
    Memo: TMemo;
    IBDB: TIBDatabase;
    IBTC: TIBTransaction;
    procedure SpeedButton1Click(Sender: TObject);
  private   { Private declarations }
  public    { Public declarations }
    procedure BackUpDataBase;
  end;

var
  Form1: TForm1;

implementation

{$R *.dfm}

procedure TForm1.BackUpDataBase;
var
  B:             Boolean;
  IBBackup:      TIBBackupService;
  SGDB:          string;
  SGBK:          string;
begin
  B := True;
  IBBackup := TIBBackupService.Create(Self);
  try
    IBBackup.BackupFile.Clear;
    SGDB := ExtractFilePath(Application.ExeName) + 'CATALOGS1252.GDB'; // IBDB.DatabaseName;
    if IBDB.Connected then
    try
      IBDB.CloseDataSets;
      IBDB.Close;
    except
      B := False;
    end;
    if B then
    begin
      IBBackup.LoginPrompt := False;
      IBBackup.ServerName := 'Poulet';
      IBBackup.Params.Add('user_name=SYSDBA');
      IBBackup.Params.Add('password=masterkey');
      IBBackup.Active := True;
      try
        IBBackup.Verbose := True;
        IBBackup.Options := [NonTransportable, IgnoreLimbo];
        IBBackup.DatabaseName := SGDB;
        SGBK := ChangeFileExt(SGDB, '.GBK');
        IBBackup.BackupFile.Add(SGBK);
        IBBackup.ServiceStart;
        while not IBBackup.EOF do
          Memo.Lines.Add(IBBackup.GetNextLine);
      finally
        IBBackup.Active := False;
      end;
    end;
  finally
    IBBackup.Free;
    IBDB.DataBaseName := SGDB;
    IBDB.Open;
  end;
end;

procedure TForm1.SpeedButton1Click(Sender: TObject);
begin
  BackUpDataBase;
end;

end.
0
 

Author Comment

by:emaia
ID: 10999708
Emil,  
 
I tried to use your code and I have some questions:    
 
- your code  works when the clients are connected?  
- how  can save in another directory and  with another name like db1 ,db2 ,db3 without to underwrite  
- it's normal the file *.gbk to larger.  
    Ex: BD Original has 6 MB and BACKUP *.gbk has 25MB  
 
Thanks  
 
-Elielson

0
 

Author Comment

by:emaia
ID: 10999718

You Know the command for Restore the Backup.

Thanks
0
 
LVL 12

Expert Comment

by:esoftbg
ID: 11024809
Hi,
  - I don't like the idea to make backup file with connected clients

  - To make different backup file every time:

procedure TForm1.BackUpDataBase;
var
  B:             Boolean;
  IBBackup:      TIBBackupService;
  SPath:         string;
  SGDB:          string;
  SGBK:          string;
  SNow:          string;
begin
  B := True;
  IBBackup := TIBBackupService.Create(Self);
  try
    IBBackup.BackupFile.Clear;
    SGDB := ExtractFilePath(Application.ExeName) + 'CATALOGS1252.GDB'; // IBDB.DatabaseName;
    if IBDB.Connected then
    try
      IBDB.CloseDataSets;
      IBDB.Close;
    except
      B := False;
    end;
    if B then
    begin
//      SGBK := ChangeFileExt(SGDB, '.GBK');
//    if you like backup file to be placed in a given path just do:
      SPath := 'C:\FireBird\Backups\';
      ForceDirectories(SPath);
      SGBK := SPath + 'CATALOGS1252.GBK';
      SNow := FormatDateTime('.'+'yyyy.MM.dd'+'.'+'HH.mm.ss', Now);
      while FileExists(SGDB+SNow) do
        SNow := FormatDateTime('.'+'yyyy.MM.dd'+'.'+'HH.mm.ss', Now);
      SGBK := SGBK + SNow;
      IBBackup.LoginPrompt := False;
      IBBackup.ServerName := 'Poulet';
      IBBackup.Params.Add('user_name=SYSDBA');
      IBBackup.Params.Add('password=masterkey');
      IBBackup.Active := True;
      try
        IBBackup.Verbose := True;
        IBBackup.Options := [NonTransportable, IgnoreLimbo];
        IBBackup.DatabaseName := SGDB;
//        SGBK := ChangeFileExt(SGDB, '.GBK');
        IBBackup.BackupFile.Add(SGBK);
        IBBackup.ServiceStart;
        while not IBBackup.EOF do
          Memo.Lines.Add(IBBackup.GetNextLine);
      finally
        IBBackup.Active := False;
      end;
    end;
  finally
    IBBackup.Free;
    IBDB.DataBaseName := SGDB;
    IBDB.Open;
  end;
end;

  - it is not normal Backup file to be larger than the Original:
  I tested a FireBird database file: size = 11382784 bytes and after backup the .GBK file is 10405888 bytes.

//..................................................................

Yes I know a command for  Restore the Backup.

emil
0
 

Author Comment

by:emaia
ID: 11043131
Thanks Emil,

I'll try your code posted. And about size of file. Do you Know if exists one way of decrease the size?

--Elielson
0
 
LVL 12

Expert Comment

by:esoftbg
ID: 11056549
And about size of file. I don't know a way of decrease the size. When I do Backup my .GBK file is always less than the original .GDB file....
0
 

Author Comment

by:emaia
ID: 11061444
Ok thanks,

I'll try again. Probability I failed in some part of code.

By the way, Do you use Firebird?

I tried to make a installation using Inno Setup where I put my application and files of Firebird 1.5 Final. However I got sucess only client installation but sometimes my application needs to server running. In my Installation the user can install only client or if the application will work in the network it's necessery install firebird server "fbserver".

You know how I can do it ? Or Know what files I can use and how install fbserver as service in the Windows.

Thanks..

--Elielson


0
 
LVL 12

Expert Comment

by:esoftbg
ID: 11132244
I am using InterBase 6.5, but I have been used FireBird 1.02.908 a time ago and there were no problems. But I have not a network at home and can not test as a network process....
0
 
LVL 4

Assisted Solution

by:Delphian
Delphian earned 100 total points
ID: 11267438

-Backups can be bigger, mainly in transportable mode or with certain datatypes - there's
no worry, it's normal.
-Yeah, it works with users connected
- Only restores are needed to get exclusive access
-If restore a backup, the DB size will be smaller because
it in truth create a new database and populate it, from scratch.
-In www.firebirdsql.org you can find a link to a Inno setup script for it


It's safer to use the command-line utility gbak instead of the Interbase
Services API wrappers of IBExpress. These lasts will not warrant any
future compatibility with future releases of Firebird.

0
 
LVL 12

Expert Comment

by:esoftbg
ID: 13249836
Please note that,
1). about the original question I really have a useful answer:    Date: 04/11/2004 09:50AM EEST
2). but about an additional question:
> - how  can save in another directory and  with another name like db1 ,db2 ,db3 without to underwrite  
I have a useful answer too:    Date: 05/09/2004 09:29AM EEST
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Introduction I have seen many questions in this Delphi topic area where queries in threads are needed or suggested. I know bumped into a similar need. This article will address some of the concepts when dealing with a multithreaded delphi database…
In my programming career I have only very rarely run into situations where operator overloading would be of any use in my work.  Normally those situations involved math with either overly large numbers (hundreds of thousands of digits or accuracy re…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

739 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