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

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 615
  • Last Modified:

Extract DML in Firebird1.5

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
emaia
Asked:
emaia
  • 9
  • 5
2 Solutions
 
esoftbgCommented:
For backup and restore INTERBASE / FIREBIRD database I am using Delphi components:
TIBBackupService and TIBRestoreService from InterBase Admin palette.
0
 
esoftbgCommented:
These components do backup / restore of the metadata and the data.

emil
0
 
emaiaAuthor Commented:
Ok Emil,

I'll try this solution....

thanks
0
Independent Software Vendors: 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!

 
esoftbgCommented:
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
 
esoftbgCommented:
please remove:

>uses DBModule;

it is from my project
0
 
esoftbgCommented:
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
 
emaiaAuthor Commented:
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
 
emaiaAuthor Commented:

You Know the command for Restore the Backup.

Thanks
0
 
esoftbgCommented:
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
 
emaiaAuthor Commented:
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
 
esoftbgCommented:
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
 
emaiaAuthor Commented:
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
 
esoftbgCommented:
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
 
DelphianCommented:

-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
 
esoftbgCommented:
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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 9
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now