[firebird-support] Re: Cross database update
Dear Thomas, thank you for your answer. I have experienced that pssing by the trigger is functioning well. The only thing wrong is that the loop has no effect on the data of the records. And I hoped that someone could check the statement syntaxwise. Maybe I am doing something wrong in my logic. Is the use of the for select do loop correct as stated? Maybe I have to use an update or insert statement except? Thanks again for any input. Kind regards Christian
[firebird-support] Re: Cross database update
Hi Set, thank you very much for the input. I will rework the block part and reply afterwards. Kind regards Christian
[firebird-support] Re: Cross database update
HI!
I have had feedback from IBExpert in respect of my block statement. The correct
syntax is easy and looks like the following below.
I can see in my trigger log that the update uses just one transaction but
unfortunately the triggers fire although the preceding rdb$set for each record
(as they normally should).
When I put everything into a block statement (as initially put in this topic)
the update has no effect, because the update runs isolated inside of the block.
Maybe someone has a clue?
Thank you
Christian
--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)
into
:TCACCY, :TCACCY2, :DATEFILTER, :FILE_ID
do
begin
use FBDEST;
try
execute statement 'select rdb$set_context(''USER_TRANSACTION'',
''bulkload'', ''1'') from rdb$database';
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;
except ibec_ShowMessage(ibec_err_Message());
end
end
commit;
ibec_CloseConnection(FBSRC);
ibec_CloseConnection(FBDEST);
end
[firebird-support] Re: Cross database update
Hi Sean I think not. When you look at it, the two tables could also be located in one db ad then the for select update loop should point to the same topic/question. How can I do this without using a block in the update part which sets the transaction property correctly but runs isolated apart from the parameters and when I use two different statements, the rdb$set has no effect and the triggers fire. So I think that IBExpert just juggles the connections and everything else is pure firebird. Kind regards Christian
[firebird-support] Re: Cross database update
And another thing. Deactivating the triggers is not an option for me but it works. execute statement 'alter trigger files_au0 inactive'; execute statement 'alter trigger files_bu0 inactive'; execute statement 'alter trigger pool_loss_record_bu0 inactive'; 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; execute statement 'alter trigger files_au0 active'; execute statement 'alter trigger files_bu0 active'; execute statement 'alter trigger pool_loss_record_bu0 active';
[firebird-support] Re: Cross database update
Hi All!
Just to close this and perhaps some one is interested.
I have now had several excellent feedbacks from the IBExpert Support forum
which solved the problem.
The relevant part has to be like this:
select rdb$set_context('USER_TRANSACTION', 'bulkload', '1') from
rdb$database into :somevar;
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;
So the presence of the transaction property in the variable does the trick.
Kind regards
Christian
P.S.: I still think that this is also helpful in a normal block statement
within one db.
[firebird-support] Common Table Expressions (“WITH ... AS ... SELECT”)
Hi to all, I am in the process of writing a list of entries with running costs in a quarter and all costs that have arsien since. So, I wrote a stored procedure with the mathematical logic for calculating the respective sums with different rates of exchange and some other rules. I made this procedure flexible so that it either returns all costs for a given period (e.g. quarter) or all costs until a certain point of time. The resulting structure for the lists basically looks like this: with costs_in_period as (select cp1.file_id, cp1.costs as costsinquarter from cost_procedure (periodfrom,periodto) cp1) select cp2.file_id, cip.costsinquarter, cp2.costs as costssofar from cost_procedure (null,periodto) cp2 left join costs_in_period cip on cip.file_id = cp2.file_id I now have about 7000 cases and both queries incividually take about 2-5 minutes to query. But when I now use the above given CTE structure I killed the running transaction after 5 hours running time. I thought that firebird does nothing else as individually running the two queries and then joining them. But it seems to me that it queries the CTE for each entry in the main query. Am I making something wrong and/or is there a better way of doing that? Kind regards and thanks for an answer in advance Christian
[firebird-support] Re: Common Table Expressions ( “WITH ... AS ... SELECT”)
Hi Dimitry, thank you for your response. I think in my case I can replace the outer join with an inner join, because it is the same sp with the same query parameters I am calling twice. Are there any future plans to change this re-evaluation structure in outter joins? Kind regards Christian
[firebird-support] Get/set context in derived table context
Hi all,
I have the need to implement something like a row number and read the last
thread about the autoincrement number which also led me to this FAQ:
http://www.firebirdfaq.org/faq343/ http://www.firebirdfaq.org/faq343/
I now tried something like this:
select
cast(rdb$get_context('USER_TRANSACTION', 'row#2') as integer) as row_number,
rdb$set_context('USER_TRANSACTION', 'row#2',
coalesce(cast(rdb$get_context('USER_TRANSACTION', 'row#2') as integer), 0) + 1)
as SET_CONTEXT,
other columns
from mytable
Which works fine. The first thing I now did is to create a derived table to get
rid of the SET_CONTEXT part. I now experienced something very strange to me.
When I did this:
select s.* from
(select
cast(rdb$get_context('USER_TRANSACTION', 'row#2') as integer) as row_number,
rdb$set_context('USER_TRANSACTION', 'row#2',
coalesce(cast(rdb$get_context('USER_TRANSACTION', 'row#2') as integer), 0) + 1)
as SET_CONTEXT,
other columns
from mytable) s
all is fine and row numbers are fine.
But when I explicitely call the individual rows like this;
select
s.row_number,
s.other columns
from
(select
cast(rdb$get_context('USER_TRANSACTION', 'row#2') as integer) as row_number,
rdb$set_context('USER_TRANSACTION', 'row#2',
coalesce(cast(rdb$get_context('USER_TRANSACTION', 'row#2') as integer), 0) + 1)
as SET_CONTEXT,
other columns
from mytable)
then the row numbers have all the same value namely the last counter position
(highest last value).
Am I doing something wrong?
Kind regards Christian
[firebird-support] Re: Get/set context in derived table context
What I initially wanted to do with this approach is join two datasets from different and different context variables sources with the row numbers as join criteria, which does not work at all, even when the individual queries show matching row numbers. BTW I start all processes with a set_context step, to set the desired starting point for both variables to make the row number match. Kind regards from Hamburg Christian
Re: [firebird-support] Get/set context in derived table context
Hi Louis and Walter, thank you very much for your responses. My Spanish is not so good but rudimental thanks to my Latin from school. So I will try the other approaches. Basically I need a join criteria to connect two datasets that have otherwise nothing in common than the order that I am giving them. Thanks again Christian
[firebird-support] Re: Get/set context in derived table context
So, I now used two small SPs with row_count and joined the output. Works fine for me. Thanka and kind regards Christian
