Hi all,

Just to follow up the issue I described several days ago in the post
titled:

"IB/Firebird server abnormal termination, server and client 10054
errors"

...I've managed to locate the problem and before I started to
understand it (it looked more like the server problem than my app
bug) and fix it, I decided to upgrade FB from 1.0.2 to 1.0.3, hoping
this could help and... it did! (amazing - isn't it? what would you
say Murphy?! :) )

What I'd like you to help me understand is if that was really the
core FB server bug or something is wrong with my approach...

First of all - thanks for your replies and suggestions.

Now - after some deep analysis of my app logs I found the critical
place in the code - execution of the following statement:

----------------------------------------------------------------
select
K.*,
KT.ILOSC ILOSC_W_KOMPLECIE,
S.STAN_ID,
S.DATA_Z,
S.DOSTEPNE DOSTEPNE_NA_STANIE

from KOMPLET_TOWAR KT

left outer join KOMPLET K on
KT.SYS_FILIA_ID = K.SYS_FILIA_ID AND
KT.KOMPLET_ID = K.KOMPLET_ID

left outer join STAN_LIST S on
KT.SYS_FILIA_ID = S.SYS_FILIA_ID AND
KT.TOWAR_ID = S.TOWAR_ID AND
KT.STAN_ID = S.STAN_ID

WHERE KT.SYS_FILIA_ID = :FiliaID AND KT.KOMPLET_ID = :KompletID
----------------------------------------------------------------

this statement was called for each row in the displayed list by the
client app and SOMETIMES caused server to crash under CERTAIN
CIRCUMSTANCES created by the application doing PARTICULAR DB
operations on other client machine.

the problematic place is: "left outer join STAN_LIST" - when I
changed it to "left outer join STAN" and "S.DOSTEPNE" to "S.ILOSC"
(STAN is the base table for the STAN_LIST view) - problem disappeared
even on FB 1.0.2

what do the STAN_LIST do - here it goes:

----------------------------------------------------------------
CREATE VIEW STAN_LIST (
SYS_FILIA_ID,
TOWAR_ID,
STAN_ID,
MAGAZYN_NR,
ILOSC,
ZAREZERWOWANE,
ZAMOWIONE,
DATA_Z,
CENA_Z_NETTO,
CENA_Z_BRUTTO,
VAT_Z,
CENA_S_NETTO,
CENA_S_BRUTTO,
VAT_S,
INDEX_WLASNY,
ILOSC_TYMCZ,
ZAREZERWOWANE_TYMCZ,
ZAMOWIONE_TYMCZ,
W_KOMPLETACH,
DOSTEPNE) AS
SELECT S.SYS_FILIA_ID,
S.TOWAR_ID,
S.STAN_ID,
S.MAGAZYN_NR,
S.ILOSC,
S.ZAREZERWOWANE,
S.ZAMOWIONE,
S.DATA_Z,
S.CENA_Z_NETTO,
S.CENA_Z_BRUTTO,
S.VAT_Z,
S.CENA_S_NETTO,
S.CENA_S_BRUTTO,
S.VAT_S,
T.INDEX_WLASNY,
(S.ILOSC - (SELECT liczba_wy FROM NULL2ZERO((SELECT SUM(ILOSC) FROM
STAN_TYMCZASOWY ST
WHERE ST.SYS_FILIA_ID = S.SYS_FILIA_ID AND
ST.TOWAR_ID = S.TOWAR_ID AND
ST.STAN_ID = S.STAN_ID)))),
(S.ZAREZERWOWANE + (SELECT liczba_wy FROM NULL2ZERO((SELECT SUM
(ZAREZERWOWANE) FROM STAN_TYMCZASOWY ST
WHERE ST.SYS_FILIA_ID = S.SYS_FILIA_ID AND
ST.TOWAR_ID = S.TOWAR_ID AND
ST.STAN_ID = S.STAN_ID)))),
(S.ZAMOWIONE + (SELECT liczba_wy FROM NULL2ZERO((SELECT SUM
(ZAMOWIONE) FROM STAN_TYMCZASOWY ST
WHERE ST.SYS_FILIA_ID = S.SYS_FILIA_ID AND
ST.TOWAR_ID = S.TOWAR_ID AND
ST.STAN_ID = S.STAN_ID)))),
(SELECT liczba_wy FROM NULL2ZERO(
(SELECT SUM(ILOSC) FROM KOMPLET_TOWAR KT
WHERE KT.SYS_FILIA_ID = S.SYS_FILIA_ID AND
KT.TOWAR_ID = S.TOWAR_ID AND
KT.STAN_ID = S.STAN_ID))),
(S.ILOSC - S.ZAREZERWOWANE - S.ZAMOWIONE -
((SELECT liczba_wy FROM NULL2ZERO((SELECT SUM(ST.ILOSC)
FROM STAN_TYMCZASOWY ST
WHERE ST.SYS_FILIA_ID = S.SYS_FILIA_ID AND
ST.TOWAR_ID = S.TOWAR_ID AND
ST.STAN_ID = S.STAN_ID))) +
(SELECT liczba_wy FROM NULL2ZERO((SELECT SUM(ST.ZAREZERWOWANE)
FROM STAN_TYMCZASOWY ST
WHERE ST.SYS_FILIA_ID = S.SYS_FILIA_ID AND
ST.TOWAR_ID = S.TOWAR_ID AND
ST.STAN_ID = S.STAN_ID))) +
(SELECT liczba_wy FROM NULL2ZERO((SELECT SUM(ST.ZAMOWIONE)
FROM STAN_TYMCZASOWY ST
WHERE ST.SYS_FILIA_ID = S.SYS_FILIA_ID AND
ST.TOWAR_ID = S.TOWAR_ID AND
ST.STAN_ID = S.STAN_ID)))))
FROM STAN S
LEFT OUTER JOIN TOWAR T
ON S.SYS_FILIA_ID = T.SYS_FILIA_ID AND S.TOWAR_ID = T.TOWAR_ID;
----------------------------------------------------------------
STAN, STAN_TYMCZASOWY and TOWAR are tables,

NULL2ZERO looks as follows:
----------------------------------------------------------------
CREATE PROCEDURE NULL2ZERO (liczba_we DOUBLE PRECISION)
RETURNS (liczba_wy DOUBLE PRECISION)
AS
BEGIN
IF (:liczba_we IS NULL) THEN
liczba_wy = 0;
ELSE
liczba_wy = liczba_we;

SUSPEND;
END !!
----------------------------------------------------------------

and the collision (problem - whatever you call it) happened when the
other client app was inserting or deleting from STAN_TYMCZASOWY which
is used by STAN_LIST

My question is - does anybody knows if this what has been fixed in
1.0.3 and is described as:

"An event handler bug was fixed. Some Operating Systems that
supported multi-threading were using out of band notification for
events. This would lead to occasional, hard to diagnose problems"

may had something to do with the problem I was getting, maybe
something else...? The reason I ask is that even though I'm happy the
problem disappeared after upgrade to 1.0.3, I'm not sure if that's
enough... You know, when you find your own bug and fix it, you can be
usually sure it is fixed.

Thanks in advance for any suggestions, conclusions

Cheers,
Tomasz