Hi All!
I am having problems regarding an update skript I want to write that fetsches data from table1 in db1 and writes the result into exisiting records in table2 in db2.
To mange this task I am using firebird 2.1.5 and a tool called IBExpert with a script extension called ibeblock, that lets me specify different db connections within this script.

Like this I a have created a second db with analysis data that holds aggregated amounts for all cases and other analytical data. This is queried in db1 and then written to db2, where the relevant tables have been cleared prior the insert. This process runs every night and takes about an hour to run through.

I now want to write some parts of the data back to db1 to have some evaluation data available here.

I have read that firebird 2.5 offers an additional "on external" function in an execute statement but this is still 2.1.

Furthermore I have the problem that there are several triggers present in db1 which have to be activated or passed by. I found an article regarding rdb$get_context and rdb$get_context and therefore I prepared the triggers.

So my script looks like this, but has no effect at all.
It worked fine testwise with just one record update.
There has to be some problem in the loop, but I don't know why:

execute ibeblock
as
begin

--This time the Analysis DB is the source
   FBSRC  = ibec_CreateConnection(__ctFirebird,'DBName="PATH to DB1";
   ClientLib=C:\WINDOWS\system32\fbclient.dll;
   user=XX; password=XX; names=ISO8859_1; sqldialect=3');

-- and writes to the Test DB/Prod. DB
   FBDEST  = ibec_CreateConnection(__ctFirebird,'DBName="PATH to DB2";
   ClientLib=C:\WINDOWS\system32\fbclient.dll;
   user=XX; password=XX; names=ISO8859_1; sqldialect=3');

   ibec_UseConnection(FBSRC);
   ibec_UseConnection(FBDEST);

use FBSRC;
for select
        (lrc.paid_claims_eur + lrc.paid_costs_eur - lrc.paid_recoveries_eur + lrc.paid_fees_eur +
        lrc.os_claims_eur + lrc.os_costs_eur - lrc.os_recoveries_eur) as TCACCY,
        (lrc.paid_claims_usd + lrc.paid_costs_usd - lrc.paid_recoveries_usd + lrc.paid_fees_usd +
        lrc.os_claims_usd + lrc.os_costs_usd - lrc.os_recoveries_usd) as TCACCY2,
        current_date, lrc.file_id
        from loss_record_claims lrc
        where lrc.file_id in (120966,120214) --testwise just two records
into
        :TCACCY, :TCACCY2, :DATEFILTER, :FILE_ID
do
  begin

use FBDEST;
      TRY
        execute statement 'execute block as begin rdb$set_context(''USER_TRANSACTION'', ''bulkload'', ''1'');
        update files f set f.ccy_total_claim_amount_net = :TCACCY, f.ccy2_total_claim_amount_net = :TCACCY2, f.date_filter = :DATEFILTER
        where f.file_id = :FILE_ID;end';
      EXCEPT
      END
    end
commit;
   ibec_CloseConnection(FBSRC);
   ibec_CloseConnection(FBDEST);
end


I hope someone can help my. Transactionwise I got the info from the developer of IBExpert that by default, the script editor creates one transaction per connection. But I made tests without the trigger deactivation and created a trigger log and found out that there was one transaction per cycle of the loop.
I think that this is wrong, but I am not sure.

Kind regards
Christian