Solved

Extract DML in Firebird1.5

Posted on 2004-04-07
18
585 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
  • 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
 
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
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 

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

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

This article explains how to create forms/units independent of other forms/units object names in a delphi project. Have you ever created a form for user input in a Delphi project and then had the need to have that same form in a other Delphi proj…
Have you ever had your Delphi form/application just hanging while waiting for data to load? This is the article to read if you want to learn some things about adding threads for data loading in the background. First, I'll setup a general applica…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

747 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now