Below are few examples of EXECUTE IBEBLOCK.
To execute any of them just copy the text of
example into SQL Editor, make necessary modifications
and press F9 or click Debug button on the SQL Editor
toolbar.


1. The following example illustrates usage of EXECUTE IBEBLOCK
for comparation of two tables (table data) in different
databases:
-------------------------------------------------------------
execute ibeblock
returns (info varchar(100))
as
begin
  RecNum = 5000;  -- How many records will be inserted into our test table
  StartTime = ibec_gettickcount();  -- Note the time...

  -- If databases do exist already we will not try to create them.
  -- Of course, this way doesn't fit for remote databases
  if (not ibec_fileexists('c:\db1.fdb')) then
    create database 'localhost:c:\db1.fdb' user 'SYSDBA' password 'masterkey'
    page_size 4096 sql_dialect 3
    -- CLIENTLIB isn't mandatory if you're using standard gds32.dll
    clientlib 'C:\Program Files\Firebird\bin\fbclient.dll';

  if (not ibec_fileexists('c:\db2.fdb')) then
    create database 'localhost:c:\db2.fdb' user 'SYSDBA' password 'masterkey'
    page_size 4096 sql_dialect 3
    clientlib 'C:\Program Files\Firebird\bin\fbclient.dll';


  -- Creating two named connections to our databases...
  create connection db1 dbname 'localhost:c:\db1.fdb'
  password 'masterkey' user 'SYSDBA'
  clientlib 'C:\Program Files\Firebird\bin\fbclient.dll';

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

  -- We're going to create the IBE$$TEST_DATA table in each database
  -- and populate it with some data
  CreateStmt =
    'create table IBE$$TEST_DATA (
       ID integer not null,
       ID2 varchar(20) not null,
       F_INTEGER integer,
       F_VARCHAR varchar(100),
       F_DATE date,
       F_TIME time,
       F_TIMESTAMP timestamp, 
       F_NUMERIC numeric(15,2),
       F_BOOL char(1) check (F_BOOL in (''T'', ''F'')),
       F_BLOB blob sub_type 1,
       F_SEASON varchar(15) check(F_SEASON in (''Spring'', ''Summer'', ''Autumn'', ''Winter'')))';

  -- IBE$$TEST_DATA will have a primary key consisting of two fields.
  -- Just to show, how to do our job in case when a primary key consists
  -- of more than one field
  AlterStmt =
    'alter table IBE$$TEST_DATA add constraint PK_IBE$$TEST_DATA primary key (ID, ID2)';

  -- First we're working with DB1
  use db1;
  -- If IBE$$TEST_DATA doesn't exist in the database we must create it
  if (not exists(select rdb$relation_name from rdb$relations where rdb$relation_name = 'IBE$$TEST_DATA')) then
  begin
    -- Creating the table itself...
    execute statement :CreateStmt;
    -- You must commit DDL-statements explicitly
    commit;
    -- ...and creating a primary key
    execute statement :AlterStmt;
    commit;
  
    -- So, we've just created the table. Now we should populate it with data
    -- We will generate some random data for each field, only
    -- for the first primary key field we will use autoincrement value
    i = 0;
    while (i < RecNum) do
    begin
      fid2    = ibec_randomstring(1,20,65,90);
      fint    = ibec_random2(1, 100000);
      fvarc   = ibec_randomstring(1,100,65,90);
      fdate   = ibec_random2(20000,40000);
      ftime   = ibec_random(0);
      ftimest = ibec_random2(20000,40000) + ibec_random(0);
      fnum    = ibec_random2(1,40000) + ibec_random(0);
      fbool   = ibec_randomval('T','F');
      fblob   = ibec_randomstring(500, 1000, 0, 255);
      fseason = ibec_randomval('Spring', 'Summer', 'Autumn', 'Winter');
  
      insert into IBE$$TEST_DATA values (:i, :fid2, :fint, :fvarc, :fdate, :ftime, :ftimest, :fnum, :fbool, :fblob, :fseason);
      i = i + 1;
  
      -- After each 500 inserted records we will display a progress message.
      -- In SQL Editor it will be displayed on the progress panel above the
      -- Code Editor
      if (ibec_mod(i, 500) = 0) then
      begin
        ibec_progress(i || ' records inserted...');
        -- Don't forget to commit
        commit;
      end
    end
    -- Once more COMMIT. Maybe there are some uncommited INSERTs...
    commit;
  end

  -- Let's work with the second connection...
  use db2;
  -- If IBE$$TEST_DATA doesn't exist in the database we must create it
  if (not exists(select rdb$relation_name from rdb$relations where rdb$relation_name = 'IBE$$TEST_DATA')) then
  begin

    execute statement :CreateStmt;
    -- Don't forget to commit each DDL-statement explicitly!
    commit;
    execute statement :AlterStmt;
    commit;
  
    -- Main idea is: we get the data from the first database and insert
    -- it into IBE$$TEST_TABLE in the second database
    use db1;
  
    i = 0;
    k = 0;
    -- FOR ... SELECT will select data from the first database...
    for select * from IBE$$TEST_DATA
    into vals
    do
    begin
      -- ...and we will insert them into the second database
      use db2;
      k = k + 1;  -- Just a counter...
      -- So, we should modify some data. In other case we'll have nothing to compare ;)
      if (ibec_mod(k,100) <> 0) then  -- Each hundredth record will be skipped...
      begin
        if (ibec_mod(i,10) = 0) then  -- the 8th field of each tenth record will be changed to NULL...
          vals[7] = null;
        if (ibec_mod(i,30) = 0) then  -- ...and 10th field of each 30th record will be modified...
          vals[9] = ibec_randomstring(500, 1000, 0, 255);
        -- Finally insert a record
        insert into db2.IBE$$TEST_DATA values :vals;
        i = i + 1;

        -- After each 500 inserted records we will display a progress message.
        -- Also we commit each 500 INSERTs
        if (ibec_mod(i, 500) = 0) then
        begin
          ibec_progress(i || ' records inserted...');
          commit;
        end
      end
    end
  
    -- Once again COMMIT...
    use db2;
    commit;


    -- Now we will insert some more data into the second database
    -- just to have more differences between tables...
    i = k + 1;
    while (i < (RecNum + 100)) do
    begin
      fid2    = ibec_randomstring(1,20,65,90);
      fint    = ibec_random2(1, 100000);
      fvarc   = ibec_randomstring(1,100,65,90);
      fdate   = ibec_random2(20000,40000);
      ftime   = ibec_random(0);
      ftimest = ibec_random2(20000,40000) + ibec_random(0);
      fnum    = ibec_random2(1,40000) + ibec_random(0);
      fbool   = ibec_randomval('T','F');
      fblob   = ibec_randomstring(500, 1000, 0, 255);
      fseason = ibec_randomval('Spring', 'Summer', 'Autumn', 'Winter');
  
      insert into IBE$$TEST_DATA values (:i, :fid2, :fint, :fvarc, :fdate, :ftime, :ftimest, :fnum, :fbool, :fblob, :fseason);
      i = i + 1;
  
      if (ibec_mod(i, 500) = 0) then
      begin
        ibec_progress(i || ' records inserted...');
        commit;
      end
    end

    info = i || ' records inserted into db2.IBE$$TEST_DATA';
    suspend;
  end



  -- So, let's begin to compare data...
  -- Our goal is make the second IBE$$TEST_DATA a full copy of
  -- the first IBE$$TEST_DATA

  -- First of all we should get a primary key of reference table
  use db1;
  i = 0;
  for select i.rdb$field_name
  from rdb$relation_constraints rc, rdb$index_segments i, rdb$indices idx
  where (i.rdb$index_name = rc.rdb$index_name) and
        (idx.rdb$index_name = rc.rdb$index_name) and
        (rc.rdb$constraint_type = 'PRIMARY KEY') and
        (rc.rdb$relation_name = 'IBE$$TEST_DATA')
  order by i.rdb$field_position
  into fldname
  do
  begin
    PKFields[i] = fldname;
    i = i + 1;
  end

  -- Now we need to get a list of remaining fields.
  SelStmt = 'select rdb$field_name
             from rdb$relation_fields
             where (rdb$relation_name = ''IBE$$TEST_DATA'')';

  -- Here we add a condition to exclude primary key fields from select result
  i = 0;
  HighDim = ibec_high(PKFields);
  while (i <= HighDim) do
  begin
    SelStmt = SelStmt || ' and (rdb$field_name <> ''' || ibec_trim(PKFields[i]) || ''')';
    i = i + 1;
  end
  -- We need natural order of fields...
  SelStmt = SelStmt || ' order by rdb$field_position';

  -- Finally execute just created select statement and get
  -- an array of all non-pk fields
  i = 0;
  for execute statement :SelStmt
  into :s
  do
  begin
    -- Trim spaces, we need not them...
    NonPKFields[i] = ibec_trim(:s);
    i = i + 1;
  end


  -- Let's compose necessary statements:
  -- SelStmt will be used to retrieve data
  -- UpdStmt will be used to update the second table if two records differ
  SelStmt = 'select ';
  UpdStmt = 'update ibe$$test_data set ';
  WhereClause = ' where ';
  i = 0;
  HighDim = ibec_high(NonPKFields);
  while (i <= HighDim) do
  begin
    SelStmt = SelStmt || NonPKFields[i];
    SelStmt = SelStmt || ', ';
    UpdStmt = UpdStmt || ibec_chr(13) || NonPKFields[i] || ' = :' || NonPKFields[i];
    if (i < HighDim) then
      UpdStmt = UpdStmt || ', ';
    i = i + 1;
  end

  -- Here we compose a WHERE clause with primary key fields:
  -- WHERE (PK_FIELD1 = :PK_FIELD1) AND (PK_FIELD2 = :PK_FIELD2) AND ...
  i = 0;
  HighDim = ibec_high(PKFields);
  while (i <= HighDim) do
  begin
    SelStmt = SelStmt || ibec_trim(PKFields[i]);
    WhereClause = WhereClause || '(' || ibec_trim(PKFields[i]) || ' = :' || ibec_trim(PKFields[i]) || ')';
    if (i < HighDim) then
    begin
      SelStmt = SelStmt || ', ';
      WhereClause = WhereClause || ' and ';
    end
    i = i + 1;
  end

  SelStmt = SelStmt || ' from IBE$$TEST_DATA';

  -- We are ready to begin the first pass...
  -- During this pass we will insert records, that are missing in the
  -- second table, and modify records, that do exist in the second
  -- table but not equal to corresponding records in the first table
  ibec_progress('First pass...');

  k = 0;
  -- FOR ... SELECT selects data from the first table
  for execute statement :SelStmt
  into :Record1
  do
  begin
    -- Now we must use the second connection
    use db2;
    SelStmt2 = SelStmt || WhereClause;
    -- In PKValues array we'll store values of primary key fields.
    -- It is necessary to take into account that PK may consist of more
    -- than one field, so we must copy PK values correctly.
    l = 0;
    while (l <= ibec_high(PKFields)) do
    begin
      PKValues[l] = Record1[ibec_high(Record1) - ibec_high(PKFields) + l];
      l = l + 1;
    end
    -- Now we try to select a record from the second database
    -- with specified primary key value.
    -- We'll get an array of values if the record with specified
    -- PK value exists in the second table, and array of NULLs if
    -- there is no such record.
    execute statement :SelStmt2 into :Record2 values :PKValues;

    -- If the last Record2 value is NULL - record is missing in
    -- the second table and we must insert it
    if (Record2[ibec_high(Record2)] is NULL) then
    begin
      -- Before inserting of a new record we should rearrange
      -- our values array so as primary key values were
      -- first in the array. In this case we will be able to
      -- use natural order of fields.
      ibec_shiftrecord(Record1, HighDim + 1);
      -- Finally insert data using values of Record1
      insert into ibe$$test_data values :Record1;

      info = 'Record inserted. ' || PKValues[0];
      suspend;
    end;
    else
    begin
      -- The record already exist in the second database and we
      -- must compare two records, reference and comparative.
      -- Following statement compares two arrays and returns 0
      -- if both arrays are equal (have same dimensions and
      -- same values of each array item)
      iRes = ibec_cmprecords(Record1,Record2);
      if (iRes <> 0) then  -- Records differ...
      begin
        -- Updating the record in the comparative table using
        -- values of Record1
        execute statement :UpdStmt || WhereClause values :Record1;

        info = 'Record updated. ' || PKValues[0];
        suspend;
      end
    end;

    k = k + 1;
    if (ibec_mod(k,100) = 0) then
      ibec_progress(k || ' records compared...');
  end

  -- Don't forget to commit all modifications
  use db2;
  commit;

  -- Let's prepare for the second pass...
  -- During this pass we must determine records that must be deleted.
  -- We need only chech whether a record with specified primary key
  -- does exist in the first database, so our SELECT will consist of
  -- primary key fields only. This will greatly increase the performance...
  SelStmt = '';
  i = 0;
  HighDim = ibec_high(PKFields);
  while (i <= HighDim) do
  begin
    SelStmt = SelStmt || ibec_trim(PKFields[i]);
    if (i < HighDim) then
      SelStmt = SelStmt || ', ';
    i = i + 1;
  end
  SelStmt = 'select ' || SelStmt || ' from ibe$$test_data';

  -- SELECT is ready, let's begin the second pass...
  k = 0;
  ibec_progress('Second pass...');
  -- For each record in the comparative table we will search for
  -- corresponding record in the reference table
  for execute statement :SelStmt
  into :Record1
  do
  begin
    -- Now we should use the first connection
    use db1;
    SelStmt2 = SelStmt || WhereClause;
    execute statement :SelStmt2 into :Record2 values :Record1;
    if (Record2[0] is NULL) then
    begin
      -- There is no corresponding record in reference table
      -- so we should delete it from comparative table
      use db2;
      execute statement 'delete from ibe$$test_data ' || WhereClause values :Record1;
      info = 'Record deleted. ' || Record1[0];
      suspend;
    end
    k = k + 1;

    if (ibec_mod(k,100) = 0) then
      ibec_progress(k || ' records compared...');
  end

  -- ...and don't forget to commit DELETEs
  use db2;
  commit;

  -- Done. Close both connections
  close connection db1;
  close connection db2;

  -- Let's count elapsed time...
  EndTime = ibec_gettickcount();
  info = 'Total time: ' || ((EndTime - StartTime) / 1000) || ' seconds';
  suspend;
end
-------------------------------------------------------------




2. Test data generator
-------------------------------------------------------------
execute ibeblock
returns (info varchar(100))
as
begin
  RecNum = 10000;

  if (exists (select rdb$relation_name from rdb$relations where rdb$relation_name = 'IBE$$TEST_DATA')) then
  begin
    execute statement 'drop table IBE$$TEST_DATA';
    commit;
  end

  execute statement
  'create table IBE$$TEST_DATA (
     F_INTEGER integer,
     F_VARCHAR varchar(100),
     F_DATE date,
     F_TIME time,
     F_TIMESTAMP timestamp, 
     F_NUMERIC numeric(15,2),
     F_BOOL char(1) check (F_BOOL in (''T'', ''F'')),
     F_BLOB blob sub_type 1,
     F_SEASON varchar(15) check(F_SEASON in (''Spring'', ''Summer'', ''Autumn'', ''Winter'')),
     F_RELS varchar(64))';
  commit;

  StartTime = ibec_gettickcount();

  i = 0;
  for select rdb$relation_name
  from rdb$relations
  into :rel_names
  do
  begin
    rels[i] = :rel_names;
    i = i + 1;
  end

  i = 0;
  while (i < RecNum) do
  begin
    fint    = ibec_random2(1, 100000);
    fvarc   = ibec_randomstring(1,100,65,90);
    fdate   = ibec_random2(20000,40000);
    ftime   = ibec_random(0);
    ftimest = ibec_random2(20000,40000) + ibec_random(0);
    fnum    = ibec_random2(1,40000) + ibec_random(0);
    fbool   = ibec_randomval('T','F');
    fblob   = ibec_randomstring(500, 1000, 0, 255);
    fseason = ibec_randomval('Spring', 'Summer', 'Autumn', 'Winter');
    frel    = rels[ibec_random2(0,ibec_high(rels))];

    insert into IBE$$TEST_DATA values (:fint, :fvarc, :fdate, :ftime, :ftimest, :fnum, :fbool, :fblob, :fseason, :frel);
    i = i + 1;

    if (ibec_mod(i, 500) = 0) then
    begin
      ibec_progress(i || ' records inserted...');
      commit;
    end
  end

  commit;

  EndTime = ibec_gettickcount();
  info = 'Total time: ' || ((EndTime - StartTime) / 1000) || ' seconds';
  suspend;
  info = 'Per record: ' || ((EndTime - StartTime) / 1000 / RecNum) || ' seconds';
  suspend;
end
-------------------------------------------------------------



3. Joining tables from different databases 
-------------------------------------------------------------
execute ibeblock (iii integer, ivc varchar(100))
returns (id integer, ename varchar(100), company varchar(100))
as
begin
--  drop database 'localhost/3060:c:\db1.fdb' user 'SYSDBA' password 'masterkey'
--  clientlib 'C:\Program Files\Firebird\bin\fbclient.dll';

--  drop database 'localhost/3060:c:\db2.fdb' user 'SYSDBA' password 'masterkey'
--  clientlib 'C:\Program Files\Firebird\bin\fbclient.dll';

  create database 'localhost/3060:c:\db1.fdb' user 'SYSDBA' password 'masterkey'
  page_size 4096 sql_dialect 3
  clientlib 'C:\Program Files\Firebird\bin\fbclient.dll';

  create database 'localhost/3060:c:\db2.fdb' user 'SYSDBA' password 'masterkey'
  page_size 4096 sql_dialect 3
  clientlib 'C:\Program Files\Firebird\bin\fbclient.dll';


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

  create connection db2 dbname 'localhost/3060:c:\db2.fdb'
  password 'masterkey' user 'SYSDBA'
  sql_dialect 3
  clientlib 'C:\Program Files\Firebird\bin\fbclient.dll';

  use db1;

  vstmt = 'create table "employees" ( ' || '
     id integer not null primary key,
     full_name varchar(100),
     company_id integer)';


  execute statement :vstmt;

  commit;

  use default;

  select count(*) from help_items into :icount;

  use db1;

  insert into "employees" (id, full_name, company_id) values (1, 'Alexander Khvastunov', 2);
  insert into "employees" (id, full_name, company_id) values (2, 'Bill Gates', 1);
  insert into "employees" (id, full_name, company_id) values (3, 'John Doe', NULL);
  insert into "employees" (id, full_name, company_id) values (4, 'Vladimir Putin', 3);
  insert into "employees" (id, full_name, company_id) values (5, 'Somebody', 15);

  use db2;

  execute statement
  'create table companies (
     id integer not null primary key,
     company_name varchar(100))';

  commit;

  insert into companies (id, company_name) values (1, 'Microsoft');
  insert into companies (id, company_name) values (2, 'HK-Software');
  insert into companies (id, company_name) values (3, 'The Kremlin?');

  commit;

  use db1;

  for execute statement 'select id, full_name, company_id
  from "employees"'
  into :id, :ename, :cid
  do
  begin
    use db2;

    company = NULL;

    select company_name from companies
    where id = :cid
    into :company;

    suspend;
  end

  close connection db1;
  close connection db2;
end
-------------------------------------------------------------



4. Database indices recreating
-------------------------------------------------------------

execute ibeblock
returns (info varchar(1000))
as
begin
  i = 0;

  for select i.rdb$index_name, i.rdb$relation_name, i.rdb$unique_flag,
             i.rdb$index_inactive, i.rdb$index_type
      from rdb$indices i
      left join rdb$relation_constraints rc on (i.rdb$index_name = rc.rdb$index_name)
      where (i.rdb$system_flag is null) and (rc.rdb$index_name is null)
      into :IdxName, :IdxRelName, :IdxUnique, :IdxInactive, :IdxType
  do
  begin
    sFields = '';
    for select rdb$field_name from rdb$index_segments
        where rdb$index_name = :IdxName
        order by rdb$field_position
        into :ifields
    do
    begin
      if (sFields <> '') then
        sFields = sFields || ', ';
      sFields = sFields || ibec_formatident(ibec_trim(ifields));
    end

    DropStmt[i] = 'drop index ' || ibec_formatident(ibec_trim(IdxName));
    CreateStmt[i] = 'create ' || ibec_iif(IdxUnique = 1, 'unique ', '') || ibec_iif(IdxType = 1, 'descending ', '') ||
                    ' index ' || ibec_formatident(ibec_trim(IdxName)) ||
                    ' on ' || ibec_formatident(ibec_trim(IdxRelName)) || ' (' || sFields || ')';

    i = i + 1;
  end

  i = 0;
  while (i <= ibec_high(DropStmt)) do
  begin
    s = DropStmt[i];
    info = s;
    suspend;
    ibec_progress(info);
    execute statement :s;
    commit;
  
    s = CreateStmt[i];
    info = s;
    suspend;
    ibec_progress(info);
    execute statement :s;
    commit;

    i = i + 1;
  end
end
-------------------------------------------------------------



5. Database indices recreating (with using of AS DATASET)
-------------------------------------------------------------
execute ibeblock
returns (info varchar(1000))
as
begin
  select i.rdb$index_name, i.rdb$relation_name, i.rdb$unique_flag,
         i.rdb$index_inactive, i.rdb$index_type
  from rdb$indices i
  left join rdb$relation_constraints rc on (i.rdb$index_name = rc.rdb$index_name)
  where (i.rdb$system_flag is null) and (rc.rdb$index_name is null)
  as dataset ds_indices;

  while (not ibec_ds_eof(ds_indices)) do
  begin
    IdxName = ibec_trim(ibec_ds_getfield(ds_indices,0));
    IdxRelName = ibec_trim(ibec_ds_getfield(ds_indices,1));
    IdxUnique = ibec_ds_getfield(ds_indices,2);
    IdxInactive = ibec_ds_getfield(ds_indices,3);
    IdxType = ibec_ds_getfield(ds_indices,4);

    sFields = '';
    for select rdb$field_name from rdb$index_segments
        where rdb$index_name = :IdxName
        order by rdb$field_position
        into :IdxField
    do
    begin
      IdxField = ibec_trim(IdxField);
      if (sFields <> '') then
        sFields = sFields || ', ';
      sFields = sFields || ibec_formatident(IdxField);
    end

    DropStmt   = 'drop index ' || ibec_formatident(IdxName);
    CreateStmt = 'create ' || ibec_iif(IdxUnique = 1, 'unique ', '') || ibec_iif(IdxType = 1, 'descending ', '') ||
                 ' index ' || ibec_formatident(IdxName) ||
                 ' on ' || ibec_formatident(IdxRelName) || ' (' || sFields || ')';

    info = DropStmt;
    suspend;
    ibec_progress(info);
    execute statement :DropStmt;
    commit;
  
    info = CreateStmt;
    suspend;
    ibec_progress(info);
    execute statement :CreateStmt;
    commit;

    ibec_ds_next(ds_indices);
  end

  close dataset ds_indices;
end