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
