Only first row is returned (Lazarus + Firebird + Oracle)

[krzynio8]

Hello!
I was working with Lazarus trunk 2.1, Zeoslib 7.2.1-rc1 and Firebird 3.0 on Debian and sometimes on Oracle.
Recently I updated to Zeoslib 7.2.4
After this some queries on Lazarus return only one first row.
I went back to 7.2.3-rc and the same, only first row is returned.
The same query on Zeoslib 7.2.1-rc1 or under Flamerobin returns all rows.
Where can be the problem?
Any suggestions, please?

[marsupilami]

Hello krzynio8,

I assume this doesn't happen to all your queries? Could you post an example please, like a query and the underlying table structure and table data?

Best regards,

Jan

[krzynio8]

Yes, it happens only on two queries form Oracle and on one on Firebird.
Today I only have access to FB database so my example is as follows:

Query:

Code: Select all

SELECT * FROM PR_KALKULACJA_PP_SZCZEG

Procedure:

Code: Select all

SET TERM ^ ;
ALTER PROCEDURE PR_KALKULACJA_PP_SZCZEG
RETURNS (
    ID INTEGER,
    MASTER VARCHAR(30),
    NAZMASTER VARCHAR(50),
    OPIS1 VARCHAR(255),
    LVL INTEGER,
    PARENT VARCHAR(30),
    CHILD VARCHAR(30),
    NAZCHILD VARCHAR(50),
    TYP CHAR(1),
    AKTYWNY CHAR(1),
    CENA DECIMAL(18,4),
    CZAS DECIMAL(18,4),
    NR INTEGER,
    TYPBOM CHAR(1),
    MASTERILOSC FLOAT,
    ILOSCWWG FLOAT,
    ILOSCPOTRZEBNA FLOAT,
    JM VARCHAR(10),
    WARTOSC DECIMAL(18,4),
    CZASLACZNY DECIMAL(18,4) )
AS
declare cur cursor for 
    (SELECT ID, IDPRODPP, ILOSC FROM PR_PROGNOZA);

declare mID BIGINT;
declare mIDPROD VARCHAR(30);
declare mILOSC FLOAT;

BEGIN

DELETE FROM TEMP_PP;

OPEN cur;

while (1=1) do begin
    fetch cur into mID, mIDPROD, mILOSC;
    
    if (row_count = 0) then leave;
    
    -- poziom 0 - tylko nazwa i opis prognozowanego produktu
    INSERT INTO TEMP_PP
        (ID, MASTER, NAZCHILD, LVL)
        SELECT :mID, IDPROD, NAZPROD, 0 from PRODUKTY WHERE IDPROD=:mIDPROD;
    
    -- poziomy pozostałe
    INSERT INTO TEMP_PP (ID, MASTER, MASTERILOSC, LVL, PARENT, CHILD, NAZCHILD, TYP, ILOSCWWG, AKTYWNY, JM, CENA, CZAS, NR, TYPBOM)
        SELECT 
            :mID, :mIDPROD as MASTER, :mILOSC, LVL, PARENT, CHILD, NAZCHILD, TYP, ILOSCWWG, AKTYWNY, JM, CENA, CZAS, NR, TYPBOM
        FROM 
            PR_GETBOM_FULL (:mIDPROD);
        --WHERE
        --    TYP='M' or TYP='P';
end
  
CLOSE cur;

for
    select 
        t.ID
        , t.MASTER
        , p.NAZPROD as NAZMASTER
        , p.OPIS1
        , t.LVL, t.PARENT, t.CHILD, t.NAZCHILD, t.TYP, t.AKTYWNY, t.CENA, t.CZAS, t.NR, t.TYPBOM, t.MASTERILOSC, t.ILOSCWWG
        , t.MASTERILOSC*t.ILOSCWWG as ILOSCPOTRZEBNA
        , t.JM 
        , t.MASTERILOSC*t.ILOSCWWG * t.CENA as WARTOSC
        , t.MASTERILOSC*t.ILOSCWWG * t.CZAS / 3600.0000 as CZASLACZNY
    from
        TEMP_PP t
        left join PRODUKTY p on p.IDPROD=t.MASTER
    order BY
        t.ID, t.MASTER, t.LVL, t.PARENT, t.CHILD
    into
        ID, MASTER, NAZMASTER, OPIS1, LVL, PARENT, CHILD, NAZCHILD, TYP, AKTYWNY, CENA, CZAS, NR, TYPBOM, MASTERILOSC, ILOSCWWG
        ,ILOSCPOTRZEBNA
        ,JM
        ,WARTOSC
        ,CZASLACZNY

do SUSPEND;

END^
SET TERM ; ^

BTW, other similar query returns all rows. I have been trying to remove the first line with null values but it seems that null values aren't problematic.
Only next one row was returned then.

Data is included as Attachement.
I suppose that somewhere in data may be any unsupported character or some data is damage. But I cannot find.
On Oracle I have also cases where not only first row is returned but where less rows is returned than is in the table.