IBEBlocks

IBEBlock is a set of DDL, DML and other statements that are executed on the server and on the client side, and which include some specific constructions applicable only in IBExpert or IBEScript (excluding the free versions of these products), independent of the database server version.

To understand more about what IBEBlocks are and what they can do, along with a full list of the functions available, please refer to the IBEBlock documentation.

To illustrate the possible deployment of IBEBlocks, we would like to show an example using the IBExpert Tools menu item, Database Comparer, and ibec_CompareMetadata.

If you've installed your own software application at a customer's you probably won't want to give him his own IBExpert, and let him play around and manipulate the database structure on his own. For such a situation we have created inside the IBEBlock language, for example, ibec_CompareMetadata, where you can create your own database connection to two databases, db1 and db2, compare their metadata, and run the resulting script to update the customer's database. (Refer to the IBEBlock documentation chapters, ibec_CreateConnection and ibec_CompareMetadata, for details of syntax and parameters.)

This does the same thing as IBExpert's Database Comparer but handles it automatically. It uses the reference database, in this case db1, and compares it to the customer database, db2, followed by the name and directory of the script file. This script file contains an SQL script of all the differences between the two databases.

This IBEBlock can then be stored to a folder, e.g. IBExpert, as comp.ibe. When ibescript.exe is now started on the command line, it starts a command-line version of IBExpert. IBEScript can be found in the main directory of the IBExpert full version. If you want to distribute this to your customers, you will need the IBExpert Server Tools.

 C:\IBExpert\ibescript comp.ibe

So if you want to update your customer's database, you need ibescript.exe, taken from the IBExpert Server Tools, you need a script file, e.g. comp.ibe, a reference database (i.e. the new version of the database), and the customer's database. Then you simply execute the script to create a script listing all differences between the two databases, which can be done in a batch file or using the ibescript.dll directly implemented in your application. In the next step, after the SQL differences are created, you can say

 C:\IBExpert>ibescript \res.sql

and restore in the main directory.

So there are two steps in the command-line window to update any database to a new structure based on a reference database, without any human interaction. If you look at the script closely, the order in which new objects need to be created, and the order in which old objects are deactivated is sometimes not so easy to understand. Let's imagine you have a stored procedure that uses another stored procedure which in turn uses a third stored procedure. You want to change the third procedure. So you need to deactivate your first and second procedure so that the third procedure is free for changes. This is all handled automatically by IBExpert and its script tools.

The source code of your procedures and IBEBlocks may contain sensitive information. So there is the possibility to use the -e parameter:

 C:\IBExpert> ibescript comp.ibe –e

This encrypts the script into binary encrypted code. The file is converted into an ESQL file (comp.ibe.esql). If the password is used, it can only be encrypted with the password. This particular file cannot be decrypted, but IBExpert can still execute

 C:\IBExpert> ibescript comp.ibe.esql

without any decryption. And if you ignore the callback (ibec_Progress) functions:

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

no messages will appear on the screen during execution. This callback function cbb is just a string that represents another IBEBlock, ibec_Progress, with this function inside.

Another example of what you can do with such an IBEBlock is ODBC access:

 execute ibeblock 
 as 
 begin
   FBX = ibec_CreateConnection(__ctFirebird, 
   'DBName="localhost/3021:C:\db1.fdb; Clientlib=gds32.dll;
   User=SYSDBA; Password=masterke; Names=NONE; SQLDialect=3;')
   OBX = ibec_CreateConnection(__ctODBC, 
   'DBQ=C:\demo.mdb;DRIVER=Microsoft Access Driver (*.mdb)');
   use OBX;
   for
     select CustNo, Company, Addr1
     from customer
     order by company
     into :CustNo, :Company, :Addr1
     do
     begin
       use FBX;
       insert into "customerx"
       ("CustNo", "Company", "Addr")
       values
       (:CustNo, :Company, :Addr1);
     end
   use FBX;
   commit;
   ibec_CloseConnection(OBX);
   ibec_CloseConnection(FBX);
 end

Here the Firebird connection and the the ODBC connection has been created, and then a SELECT FOR statement is executed on the ODBC connection.

The returning values are put into the variables defined. If you do not want to declare your variables, IBExpert doesn't require it. The FOR SELECT statement then switches to the Firebird database (FBX). The data is then inserted into the Firebird database's CUSTOMERX table. Finally the Firebird connection needs to be committed and then both connections closed. There is also the possibility to do some exception handling, and for example the ibec_CopyData is able to copy data to a local Firebird table from any source.

If you don't know what tables already exist in your ODBC partner, it is sometimes hard to write your own CREATE TABLE statements compatible to the one that you need in your Firebird database. There is a useful tool in IBExpert, the ODBC Viewer. This can be used easily to open the same database.

In the Windows Control Center / Data Sources (ODBC) there is a demo.mdb, which is based on an MSAccess ODBC driver. Double-clicking on the database name in IBExpert's ODBC Viewer directly opens the ODBC driver and displays the CUSTOMER table and its data. To select a table whose data is required in the Firebird database, use the menu item Export to script/table, select the Export into a table page, give the new table a name, and simply export.

This way you very quickly have a new database table full of the data that was in the original Access database. If the table data is removed, so that only the metadata structure remains, you can see how the IBEBlock used earlier transfers the data automatically. This functionality does not depend on Firebird/InterBase^®. It can be used between any databases with ODBC connectivity.

Search

German

Follow IBExpertise on:

edit SideBar

the database experts

IBEBlocks

Search