Columns from stored procedure are empty in dbgrid but have values
under FlameRob
lazarus / 16-Databases

----------------------------------------------------------------------
krzynio · 2020-12-21 11:43

Hello!

I use TZQuery component for obtaining some data from stored procedure
on Firebird server. The query is very simple:
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i select *
from PR_URLOPY ('2020', '2015', '%')WHERE Aktywny>='1' 
The result in Firebird is correct and shows all data
http://krzynio.net/_xfb.png

But the result from Lazarus application in dbGrid returns zeros in
most columns.
http://krzynio.net/_xpc.png
Only first row looks fine.

This is the code of the procedure
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i SET TERM ^
;ALTER PROCEDURE PR_URLOPY (    ROK1 INTEGER,    ROK_BO INTEGER,   
IDWYDZ VARCHAR(10) )RETURNS (    IDPRAC VARCHAR(10),    NAZPRAC
VARCHAR(50),    AKTYWNY SMALLINT,    ORGUNIT VARCHAR(10),    GRUPA
VARCHAR(10),    IDSTAN VARCHAR(10),    NAZSTAN VARCHAR(50),    ROK
INTEGER,    WYMIAR_BIEZ INTEGER,    EKWIWALENT_BIEZ INTEGER,   
POPRZEDNI INTEGER,    URLOP_RAZEM INTEGER,    WYK_UW INTEGER,   
WYK_UWZ INTEGER,    WYK_RAZEM INTEGER,    POZOSTALY INTEGER,   
WYK_UW_BIEZ INTEGER,    WYK_UWZ_BIEZ INTEGER )ASBEGIN     delete from
TEMP_URLOP_WYK;        insert into TEMP_URLOP_WYK (IDPRAC, ROK, TYP,
ILOSC)        select           kb.IDPRAC,           EXTRACT(YEAR from
k.DATA) as ROK,          ko.IDOPER as TYP,          SUM(DATEDIFF(HOUR,
CZASSTART, CZASSTOP)/8) as ILOSC        from          KARTYOPERACJE
ko          left join KARTY k on k.IDKARTY=ko.IDKARTY          left
join KARTYBRYGADY kb on kb.IDPARTII=ko.idpARTII          left join
PARAMS pm on pm.ID=1        where           ( EXTRACT(YEAR from
k.DATA) >= :ROK_BO )          AND          ( IDOPER = pm.IDURLOP or
IDOPER = pm.IDURLOPZ )        group by           kb.IDPRAC, ROK, TYP;
              for          select            p.IDPRAC            ,
p.NAZPRAC            , p.AKTYWNY            , p.ORGUNIT            ,
p.GRUPA            , p.IDSTAN            , s.NAZSTAN            ,
u.ROK            , u.WYMIAR as WYMIAR_BIEZ            ,
cOALESCE(u.EKWIWALENT, 0) AS EKWIWALENT_BIEZ            ,
COALESCE(dowyk.ILOSC, 0) - u.WYMIAR as ZALEGLY            ,
COALESCE(dowyk.ILOSC, 0) - 0 as URLOP_RAZEM            ,
COALESCE(wyk.ILOSC, 0) - 0 as WYK_UW            ,
COALESCE(wykuz.ILOSC, 0) -0  as WYK_UWZ                ,
COALESCE(wyk.ILOSC, 0) + COALESCE(wykuz.ILOSC, 0) as WYK_RAZEM       
    , COALESCE(dowyk.ILOSC, 0) - COALESCE(wyk.ILOSC, 0) -
COALESCE(wykuz.ILOSC, 0) as POZOSTALY            ,
COALESCE(wyk_biez.ILOSC, 0) - 0 as WYK_UW_BIEZ            ,
COALESCE(wykuz_biez.ILOSC,0) - 0 as WYK_UWZ_BIEZ        from
V_PRACOWNICY p        left join STANOWISKA s on s.IDSTAN=p.IDSTAN    
  left join PARAMS pm on pm.ID=1        left join URLOPY u on
u.IDPRAC=p.IDPRAC         left join         (            select      
       IDPRAC,               SUM(WYMIAR-COALESCE(EKWIWALENT, 0)) as
ILOSC,              SUM(COALESCE(EKWIWALENT, 0)) as EKWIWALENT       
   from              URLOPY u            WHERE             
u.ROK>=:ROK_BO and u.ROK<=:ROK1            GROUP BY             
IDPRAC        ) dowyk on dowyk.IDPRAC=u.IDPRAC        left JOIN
(select idprac, sum(ilosc) as ILOSC from TEMP_URLOP_WYK u left join
PARAMS pm on pm.ID=1 where u.ROK<=:ROK1 and u.TYP=pm.IDURLOP group by
u.IDPRAC) wyk on wyk.IDPRAC=p.idprac        left JOIN (select idprac,
sum(ilosc) as ILOSC from TEMP_URLOP_WYK u left join PARAMS pm on
pm.ID=1 where u.ROK<=:ROK1 and u.TYP=pm.IDURLOPZ group by u.IDPRAC)
wykuz on wykuz.IDPRAC=p.idprac        left JOIN (select idprac,
sum(ilosc) as ILOSC from TEMP_URLOP_WYK u left join PARAMS pm on
pm.ID=1 where u.ROK=:ROK1 and u.TYP=pm.IDURLOP group by u.IDPRAC)
wyk_biez on wyk_biez.IDPRAC=p.idprac        left JOIN (select idprac,
sum(ilosc) as ILOSC from TEMP_URLOP_WYK u left join PARAMS pm on
pm.ID=1 where u.ROK=:ROK1 and u.TYP=pm.IDURLOPZ group by u.IDPRAC)
wykuz_biez on wykuz_biez.IDPRAC=p.idprac        where           
u.ROK=:ROK1 and p.OUWYD like :idwydz        order by           
p.NAZPRAC                     into IDPRAC, NAZPRAC, AKTYWNY, ORGUNIT,
GRUPA, IDSTAN, NAZSTAN, ROK, WYMIAR_BIEZ, EKWIWALENT_BIEZ, POPRZEDNI,
URLOP_RAZEM, WYK_UW, WYK_UWZ, WYK_RAZEM, POZOSTALY, WYK_UW_BIEZ,
WYK_UWZ_BIEZ    do BEGIN        SUSPEND;    ENDEND^SET TERM ; ^  GRANT
EXECUTE ON PROCEDURE PR_URLOPY TO  SYSDBA; 
How force Lazarus to display all rows correctly?
I can add that it has been working any time ago but since this time I
upgraded Firebird from 2.5 to 3.0 and Zeoslib from 7.1 to 7.2

----------------------------------------------------------------------
krzynio · 2020-12-21 12:55

Sorry that Iam writing back to myself. I found solution and I am going
to leave the post for others.
The problem seems to lay in Zeos. I downgraded from 7.2.8 (included in
Online Package Manager) to 7.2.1 rc1 used previously and the old
version works fine.