<< ODBC Viewer | IBExpert | IBExpert Services menu >>

IBExpert command-line tools

IBEExtract and IBECompare were replaced in 2005 by IBEScript.exe and EXECUTE IBEBLOCK.

For those of you working with older versions of IBExpert, the following command-line tools are available:

*Replaced in 2005 by IBEScript.exe and EXECUTE IBEBLOCK.

These cover the majority of the options offered by the InterBase and Firebird command-line utilities and much more.

To be allowed to distribute any of the IBExpert modules (ibexpert.exe, ibescript.exe, ibescript.dll, ibeextract.exe and ibecompare.exe) together with your application, you require:

Some functions of the new IBExpert modules do not work on non-licensed computers, so you can only use them where your IBExpert license is valid.

IBEScript

IBEScript.exe can be found in the IBExpert root directory, and needs to be started from DOS. (This feature is unfortunately not included in the Personal Edition.)

Syntax

 IBEScript script_filename [options]
  • -S = silent mode
  • -V<Verbose_file> = verbose output file. If <Verbose_file> exists, IBEScript will overwrite it.
  • -v<verbose_file> = verbose output file. If <verbose_file> exists, IBEScript will append message to this file.
  • -E = display only error messages.
  • -N = continue after error.
  • -T = write timestamp into log.
  • -D = connections string (use it if your script does not contain CONNECT or CREATE DATABASE statements).
  • -P = connection password (use only with -D option).
  • -R = connection role (use only with -D option).
  • -U = connection user name (use only with -D option).
  • -C = character set (use only with -D option).
  • -l = client library file (gds32.dll if not specified).
  • -L<1|2|3> = SQL dialect (use only with -D option; 1 if not specified).
  • -i = idle priority.
  • -G<variable_name>=<value> = specify global variable values.
  • -I<file_path_and_name> = By default (without the -I option specified). IBEScript.exe processes IBEScript.ini files immediately after starting in the following order:
1. IBEScript.ini in the IBEScript.exe directory, if it exists,
2. IBEScript.ini in the current directory, if it exists. If only -I is specified without a file name, any INI-file will be ignored. If a file name is specified after -I (e.g. -I"C:\my files\myibescript.ini") ONLY this file will be processed if it exists. Parameters specified in the command line will overwrite corresponding ones from an INI file.

WARNING! All options are case-sensitive!

The following features are also available: when no password and/or user name are specified in the CONNECT or CREATE DATABASE statements, a login dialog will appear. It is also possible to change the connection character set (SET NAMES) and garbage collection option (SET GARBAGE_COLLECT) before the RECONNECT statement. Any SET commands mentioned which are followed by a RECONNECT statement will affect the new connection.

It is also possible to use environment variables in INPUT, OUTPUT and SET BLOBFILE statements (see example below).

IBExpert version 2014.01.01 introduced support for Firebird 3.0 packages and now UTF8 BOM is skipped when executing a script from file. And since IBExpert version 2014.03.16 the OUTPUT command now supports the OctetsAsHex option, which allows the extraction of CHAR(n) CHARACTER SET OCTETS values in hexadecimal format.

back to top of page

IBEScript examples

1. IBEBlock technology to create procedures with access to data in different Firebird/InterBase databases

A simple script to copy data from one Firebird/InterBase database to another:

 execute ibeblock
 as
 begin
   FBSrc  = ibec_CreateConnection(__ctFirebird,'DBName="localhost:C:\DB1.FDB";
   ClientLib=C:\Program Files\Firebird\Bin\fbclient.dll;
   user=SYSDBA; password=masterkey; names=WIN1252; sqldialect=3');
   FBDest = ibec_CreateConnection(__ctFirebird,'DBName="localhost:C:\DB2.FDB";
   ClientLib=C:\Program Files\Firebird\Bin\fbclient.dll;
   user=SYSDBA; password=masterkey; names=WIN1252; sqldialect=3');
   ibec_UseConnection(FbSrc);
   for select CustNo, Company, Addr1 from customer order by company into :CustNo, :Company,
     :Addr1
   do
   begin
      use FBDest;
      INSERT INTO CUSTOMER (CustNo, Company, Addr1) VALUES (:CustNo, :Company, :Addr1);
      use FBSrc;
   end
   use FBDest;
   COMMIT;
   ibec_CloseConnection(FBSrc);
   ibec_CloseConnection(FBDest);
 end

back to top of page

2. ODBC access to all ODBC data sources for importing or exporting data from a script

The same can also be done with any ODBC data source as the source and/or destination (this functionality has been tested with IBM DB2, Oracle, MS Access, Sybase etc.):

 execute ibeblock
 as
 begin
   OdbcCon = ibec_CreateConnection(__ctODBC, 'DBQ=C:\demo.mdb; DRIVER=Microsoft Access
   Driver (*.mdb)');
   FBCon = ibec_CreateConnection(__ctFirebird,'DBName="AVX-MAIN:D:\FB2_DATA\IBEHELP.FBA";
   ClientLib=C:\Program Files\Firebird\Bin\fbclient.dll;
   user=SYSDBA; password=masterkey; names=WIN1251; sqldialect=3');
   ibec_UseConnection(OdbcCon);
   for select CustNo, Company, Addr1 from customer order by company into :CustNo, :Company,
   :Addr1
   do
   begin
      use FBCon;
   INSERT INTO CUSTOMER (CustNo, Company, Addr1) VALUES (:CustNo, :Company, :Addr1);
      use OdbcCon;
   end
   use FBCon;
   COMMIT;
   ibec_CloseConnection(OdbcCon);
   ibec_CloseConnection(FBCon);
 end

back to top of page

3. Comparing databases from scripts

The following script compares the structure of two Firebird/InterBase databases and stores a script that can be used to synchronize the database structure in the destination database: Save the following text as c:\comp.sql:

 execute ibeblock
 as
 begin
   create connection ReferenceDB dbname 'localhost:c:\RefDB.fdb'
   password 'masterkey' user 'SYSDBA'
   clientlib 'C:\Program Files\Firebird\bin\fbclient.dll'; 

   create connection CustomerDB dbname 'localhost:c:\customerDB.fdb'
   password 'masterkey' user 'SYSDBA'
   clientlib 'C:\Program Files\Firebird\bin\fbclient.dll';

   cbb = 'execute ibeblock (LogMessage variant)
          as
          begin
            ibec_progress(LogMessage);
          end';

   ibec_CompareMetadata(ReferenceDB, CustomerDB, 'C:\CompRes.sql', 'OmitDescriptions; OmitGrants', cbb);

   close connection ReferenceDB;
   close connection CustomerDB;
 end

Now run the following command line to create the script and synchronize the databases:

 ibescript.exe c:\comp.sql
 ibescript.exe c:\compres.sql

back to top of page

4. Create automatic reports

ibec_CreateReport prepares a report from a specified source and returns prepared report data. For preparing the initial report please refer to the IBExpert Report Manager.

This feature can be used for executing reports created with the IBExpert Report Manager in command-line mode, for example with batch files. The monthly sales report, invoices or other such reports can be designed in the Report Manager and executed with simple SQL statements. The result can then be saved in the database as a PDF file or other formats and sent by email or exported using ibec_ExportReport.

 execute ibeblock
  as
  begin
    Params['HeaderMemo'] = '';
    Params['MEMO2'] = 2;

    SELECT IBE$REPORT_SOURCE FROM ibe$reports
    where ibe$report_id = 4
    into :RepSrc;

    Report = ibec_CreateReport(RepSrc, Params, null);
    ibec_SaveToFile('D:\reptest.fp3', Report, 0);
            Res = ibec_ExportReport(Report, 'D:\reptest.pdf', __erPDF, 'EmbeddedFonts=TRUE');
    Res = ibec_ExportReport(Report, 'D:\reptest.jpg', __erJPEG, 'CropImages; Quality=90');
 end

back to top of page

5. File import into blob fields from SQL scripts

The following script imports the data from the files into the table TEST:

 SET BLOBFILE 'C:\f1.jpg';
 INSERT INTO TEST(ID,BLOBCOL) VALUES (1, :h00000000_7FFFFFFF);
 SET BLOBFILE 'C:\f2.jpg';
 INSERT INTO TEST(ID,BLOBCOL) VALUES (2, :h00000000_7FFFFFFF);
 SET BLOBFILE 'C:\f3.jpg';
 INSERT INTO TEST(ID,BLOBCOL) VALUES (3, :h00000000_7FFFFFFF);

The same syntax can be used for updating blob data.

6. Using environment variables in INPUT, OUTPUT and SET BLOBFILE statements

 execute ibeblock 
 as
 begin
 ibec_SetEnvironmentVariable('MyScriptDir', 'D:\Scripts\MyScripts');
 ibec_SetEnvironmentVariable('MyDataDir', 'D:\Data');
 ibec_SetEnvironmentVariable('MyBlobData', 'D:\Data\Blobs');
 end;

 SET BLOBFILE '%MyBlobData%\mytable.lob';

 OUTPUT '%MyDataDir%\mytable.sql';
 select * from mytable
 asinsert;
 COMMIT;

 INPUT '%MyScriptDir%\ProcessData.sql';

These are just a few examples of the many tasks you can do with IBEScript. The full syntax and parameter list for ibec_CompareMetadata can be found in the online documentation, along with a full list of all current IBEBlock commands.

back to top of page

Encryption & decryption

There are two possible ways to encrypt/decrypt scripts and to execute encrypted scripts:

  1. Encrypting without the password. In this case there is no possibility to decrypt an encrypted script but it is possible to execute this script with IBEScript.
  2. Encrypting with the password. In this case it possible to decrypt the script and execute it with IBExpert if the correct password is specified.

The following options control the encrypting and decrypting:

  • -e = encrypts a script file and creates a file with the extension .esql if the output file is not specified (no execution will be performed).
  • -d = decrypts an encrypted script file if it was encrypted with password (no execution will be performed).
  • -p<password> = encrypt/decrypt password.
  • -o<file_name> = output file name for encrypted and decrypted scripts.

Again: all options are case-sensitive!

Example 1

 IBEScript "C:\MyScripts\CreateDB.sql"

Example 2

 IBEScript C:\MyScripts\CreateDB.sql -S -UScriptLog.txt

This product can be purchased as part of the distribution licenses listed above.

See also:
Firebird Interactive SQL Utility
IBEBlock
Script Executive

back to top of page

IBEScript.dll

IBEScript.dll can be used in applications to execute scripts from file or from a string buffer. There is a small demo application illustrating its use in the /IBEScriptDll folder. Please also refer to the IBEScriptDll Readme.txt.

For regulations regarding distribution of any of the IBExpert modules (ibexpert.exe, ibescript.exe, ibescript.dll, ibeextract.exe and ibecompare.exe) together with your application, please refer to the beginning of this chapter.

back to top of page

IBEScriptDll Readme.txt

1. IBEScript.dll exports the following functions:

  • ExecScriptFile: executes script from file.
  • ExecScriptText: executes script from string buffer.
  • CONNECT: connects to the database if there is no CONNECT statement in the script.

2. Examples of the use of ExecScriptFile and ExecScriptText: see demo application in the /IBEScriptDll folder.

3. Example using the CONNECT function:

 procedure TForm1.Button2Click(Sender: TObject);
 var
   Hndl : THandle;
   ESP : TExecuteScriptProc;
   CP : TConnectDBProc;
   s : string;
   Res : integer;
 begin
   ErrCount := 0;
   StmtCount := 0;
   mLog.Lines.Clear;
   s := mScript.Text;
   if Trim(s) = '' then
   begin
     ShowMessage('Nothing to do!');
     Exit;
   end;
   try
     Hndl := LoadLibrary(PChar('IBEScript.dll'));
     if (Hndl > HINSTANCE_ERROR) then
     begin
       ESP := GetProcAddress(Hndl, 'ExecScriptText');
       CP := GetProcAddress(Hndl, 'Connect');
       if (@ESP <> nil) and (@CP <> nil) then
       begin
         Pages.ActivePage := tsOutput;
         Res := CP(PChar('db_name=localhost:c:\empty.fdb; password=masterkey; user_name=SYSDBA;'
 +
                         'lc_ctype=win1251; sql_role_name=ADMIN; sql_dialect=3;' +
                         'clientlib="c:\program files\firebird\bin\fbclient.dll"'), @CEH);
         if Res = 0 then
           ESP(PChar(s), @HandleError, @BeforeExec, @AfterExec);
         end;
       end;
   finally
     if Hndl > HINSTANCE_ERROR then
       FreeLibrary(Hndl);
   end;
 end;

This product can be purchased as part of the distribution licenses listed above.

See also:
Firebird and InterBase command-line utilities

back to top of page

IBECompare

IBECompare was replaced in 2005 by IBEScript.exe and EXECUTE IBEBLOCK.

For those of you working with older versions of IBExpert, IBECompare is a command-line tool to compare databases, scripts and table data. It was originally introduced in 2004.

IBECompare.exe can be found in the IBExpert root directory, and needs to be started from DOS:

 C:\Program Files\HK-Software\IBExpert 2004>ibecompare

IBECompare offers the following options:

  • -C<config_file> = config file.
  • -O<output_file> = output file (Result.sql if not specified).
  • -V<verbose_file> = verbose file.
  • -D = compare database metadata and script.
  • -T = compare table data.
  • -S = silent mode.
  • -s = create a config file sample (config_sample.ini).

WARNING: All options are case-sensitive!

Example

 IBECompare -D -Cconfig.ini -OC:\Scripts\result.sql -Vlog.txt

In both cases (i.e. options -D or -T) IBECompare produces an SQL script file. It is necessary to specify an input settings file using the -C option.

You can obtain the template of this file starting IBECompare with the -s option(IBECompare -s). In this case IBECompare will create a config_sample.ini file within the current directory, which is simple and quick to modify.

It is also possible to create a settings file using the Save configuration button in the IBExpert Tools menu item, Database Comparer.

The following is an example of an .ini file, for comparing table data:

 [MasterDB]
 ConnectString=LOCALHOST:C:\MyData\Master.gdb
 Username=SYSDBA
 Password=masterkey
 Charset=WIN_1251
 ClientLib=gds32.dll
 ; Next item will be used while comparing tables
 TableName=CUSTOMER

 ; Instead of MasterDB section you can use MasterScript section:
 ;[MasterScript]
 ; ScriptFile=D:\MyScripts\MyData.dql

 [TargetDB]
 ConnectString=MYSERVER:D:\Data\customer.gdb
 Username=SYSDBA
 Password=masterkey
 Charset=WIN_1251
 ClientLib=gds32.dll
 ; Next item will be used while comparing tables
 TableName="Customer" 

 ; Instead of TargetDB section you can use TargetScript section:
 ;[TargetScript]
 ;ScriptFile=D:\MyScripts\MyData.dql

 [CompareObjects]
 Domains=1
 Tables=1
 Views=1
 Triggers=1
 Procedures=1
 Generators=1
 Exceptions=1
 Functions=1
 Roles=1
 Indices=1
 Grants=1
 Descriptions=1
 PrimaryKeys=1
 ForeignKeys=1
 Uniques=1
 Checks=1

 [Options]
 ; Next items will be used while comparing tables
 ProcessINSERTs=1
 ProcessUPDATEs=1
 ProcessDELETEs=1

Should the script generated by IBECompare include a

 SET BLOBFILE 'xxx.lob';

command, it is necessary to execute the script using IBEScript or the IBExpert Script Executive.

SET BLOBFILE is a special extension of script language that allows insert or update blob values via script.

See also:
Script Executive
Database Comparer
Table Data Comparer

back to top of page

IBEExtract

IBEExtract was replaced in 2005 by [#IBEScript | IBEScript.exe]] and EXECUTE IBEBLOCK. Please refer to ibec_ExtractMetadata for further information.

For those of you working with older versions of IBExpert, IBEExtract.exe can be found in the IBExpert root directory, and needs to be started from DOS.

Syntax

 IBEExtract database [options]
  • -U<user_name> = user name (SYSDBA if not specified).
  • -P<password> = password (masterkey if not specified).
  • -C<character_set> = character set.
  • -O<output_file> = output file (Result.sql if not specified).
  • -F<output_folder> = output folder (for Separate Files mode; current directory, if not specified).
  • -G = set generator values.
  • -D = extract data.
  • -B = extract blobs (please refer to blob fields for further information about blobs).
  • -S = silent mode.
  • -V<verbose_file> = verbose file.
  • -M<config_file> = use config file.
  • -T = generate CREATE DATABASE statement.
  • -N = generate CONNECT statement.
  • -W = include password into CREATE DATABASE or CONNECT statement.
  • -R = extract object descriptions.
  • -A<integer_value> = commit after <integer_value> records.
  • -Y = extract computed fields separately.
  • -X = extract privileges.
  • -L = extract privileges only for selected objects.
  • -d = date format (native Firebird/InterBase date format <DD-MMM-YYYY>, if not specified).
  • -f = extract into separate files.
  • -s = extract into separate files.
  • -r = use REINSERT instead of repeated INSERTs.
  • -l = client library file (gds32.dll, if not specified).
  • -z = maximum size of resulting files in megabytes.
  • -u = Use UPDATE instead of DESCRIBE option.

WARNING! All options are case-sensitive!

Example 1

 IBEExtract localhost:c:\mydata\mydatabase.gdb -OC:\scripts\result.sql -USYSDBA -Pmasterkey -CWIN1251

Example 2

 IBEExtract "C:\IB Data\my.gdb" -O"My Script.sql" -V"Extract Log.txt"

All options listed here can also be found in IBExpert under Tools / Extract Metadata.

See also:
Extract Metadata
ibec_ExtractMetadata
Using Extract metadata to repair databases

back to top of page
<< ODBC Viewer | IBExpert | IBExpert Services menu >>