Hello Christian,
> 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.
This all is proprietary IBExpert stuff, thus I'm afraid the best support
you can get is by contacting HK-Software (again).
Perhaps behind the scene is some sort of transaction magic, thus you
might widen the context of the RDB$SET_CONTEXT call by using
USER_SESSION instead of USER_TRANSACTION.
Another approach to dismiss triggers for such kind of bulk operations,
similar to the "context" approach is to connect with a different user
and check/handle the user name information inside the trigger.
--
With regards,
Thomas Steinmaurer
http://www.upscene.com/
Professional Tools and Services for Firebird
FB TraceManager, IB LogManager, Database Health Check, Tuning etc.
