Uniface User Forum

 View Only

  • 1.  findkey statement generating unexected SQL, HELP needed

    Posted 12-29-2023 10:52

    Hi. I am new on this forum and I have a problem with a findkey statement

    Ik copied a programa,  made some adjustments and did a testrun with 2 records. Both programs (I will call them OLD and NEW)  refer tot a entity / table called MYTABLE (not real name). Only the entity is used, no local modification in the definition. The validate key trigger uses findkey to check if the record already exists before store.

    Record 1 OLD

    "C: PROGRAM_5 S: 0 P: 0" (); VALIDATEKEY; 1; [0]; findkey $entname, $curkey

    I/O function: S, mode: 1, on file/table: MYTABLE index: 1 =

     Where ((NR_REKENING = 441202) And (DT_OVERZICHT = 09112023) And (NR_VOL = 5))

    SELECT 0 FROM "MYTABLE" WHERE  "NR_REKENING" = :"WNR_REKENING" AND "DT_OVERZICHT" = :"WDT_OVERZICHT" AND "NR_VOL" = :"

    WNR_VOL"

    -> Hits selected: 0 .

    Record 2 OLD

    C: PROGRAM_5 S: 0 P: 0" (); VALIDATEKEY; 1; [0]; findkey $entname, $curkey

    I/O function: S, mode: 1, on file/table: MYTABLE index: 1 =

     Where ((NR_REKENING = 441202) And (DT_OVERZICHT = 09112023) And (NR_VOL = 5))

    SELECT 0 FROM "MYTABLE" WHERE  "NR_REKENING" = :"WNR_REKENING" AND "DT_OVERZICHT" = :"WDT_OVERZICHT" AND "NR_VOL" = :"

    WNR_VOL"

    -> Hits selected: 0 .

    This seems correct to me

    Recod 1 NEW

    "C: PROGRAM_B S: 0 P: 0" (); LP_VERWERK_DETAILREGEL; 121; [0]; store

    "C: PROGRAM_B S: 0 P: 0" (); VALIDATEKEY; 6; [0]; findkey $entname, $curkey

    I/O function: S, mode: 1, on file/table: MYTABLE index: 1 =

     Where ((NR_REKENING = 441202) And (DT_OVERZICHT = 09112023) And (NR_VOL = 4))

    SELECT 0 FROM "MYTABLE" WHERE  "NR_REKENING" = :"WNR_REKENING" AND "DT_OVERZICHT" = :"WDT_OVERZICHT" AND "NR_VOL" = :"WNR_VOL"

    -> Hits selected: 0 .

    Record 2 NEW

    "C: PROGRAM_B S: 0 P: 0" (); VALIDATEKEY; 6; [0]; findkey $entname, $curkey

    I/O function: S, mode: 1, on file/table: MYTABLE index: 1 >=

    SELECT COUNT(*) FROM "MYTABLE"

    -> Hits selected: 871928 .

    The findkey fort he second record generates different and unwanted SQL. It is the same statemen findkey and $curkey is 1 , the entity/table has only 1 primary key. Strangely the recods written are different

    OLD

    I/O function: W, mode: 0, on file/table: MYTABLE length: 415

    INSERT INTO "MYTABLE" ( "NR_REKENING", "DT_OVERZICHT", "NR_VOL", "U_VERSION", "NAAM_FONDS", "CD_SYMBOOL", "CD_ISIN", "

    CD_TYPE", "CD_VALUTA", "BD_KOERS", "AANT_NOM_WAARDE", "BD_WISSELKOERS", "BD_WAARDE_EUR", "BD_WAARDE_VV", "BD_AANSCHAF", "BD_OPGE

    LOPEN_RENTE", "BD_LIQUIDITEIT", "OV_EXE_ASS_FACTOR", "DT_EXPIRATIE", "BD_EXPIRATIE", "OMS_BEL_INSTR_TYPE", "CD_FONDS", "OV_LAAGB

    R", "DT_LAAWYZ", "OV_BRON" ) VALUES ( :"XNR_REKENING", :"XDT_OVERZICHT", :"XNR_VOL", :"XU_VERSION", :"XNAAM_FONDS", :"XCD_SYMBOO

    L", :"XCD_ISIN", :"XCD_TYPE", :"XCD_VALUTA", :"XBD_KOERS", :"XAANT_NOM_WAARDE", :"XBD_WISSELKOERS", :"XBD_WAARDE_EUR", :"XBD_WAA

    RDE_VV", :"XBD_AANSCHAF", :"XBD_OPGELOPEN_RENTE", :"XBD_LIQUIDITEIT", :"XOV_EXE_ASS_FACTOR", :"XDT_EXPIRATIE", :"XBD_EXPIRATIE",

     :"XOMS_BEL_INSTR_TYPE", :"XCD_FONDS", :"XOV_LAAGBR", :"XDT_LAAWYZ", :"XOV_BRON" )

    NEW

    "C: PROGRAM_B S: 0 P: 0" (); WRITE; 3; [0]; write

    I/O function: W, mode: 0, on file/table: MYTABLE length: 340

    INSERT INTO "MYTABLE" ( "NR_REKENING", "DT_OVERZICHT", "NR_VOL", "U_VERSION", "NAAM_FONDS", "CD_SYMBOOL", "CD_ISIN", "

    CD_TYPE", "CD_VALUTA", "BD_KOERS", "AANT_NOM_WAARDE", "BD_WISSELKOERS", "BD_WAARDE_EUR", "BD_WAARDE_VV", "BD_AANSCHAF", "BD_OPGE

    LOPEN_RENTE", "BD_LIQUIDITEIT", "OV_EXE_ASS_FACTOR", "DT_EXPIRATIE", "BD_EXPIRATIE", "OMS_BEL_INSTR_TYPE", "CD_FONDS", "OV_LAAGB

    R", "DT_LAAWYZ" ) VALUES ( :"XNR_REKENING", :"XDT_OVERZICHT", :"XNR_VOL", :"XU_VERSION", :"XNAAM_FONDS", :"XCD_SYMBOOL", :"XCD_I

    SIN", :"XCD_TYPE", :"XCD_VALUTA", :"XBD_KOERS", :"XAANT_NOM_WAARDE", :"XBD_WISSELKOERS", :"XBD_WAARDE_EUR", :"XBD_WAARDE_VV", :"

    XBD_AANSCHAF", :"XBD_OPGELOPEN_RENTE", :"XBD_LIQUIDITEIT", :"XOV_EXE_ASS_FACTOR", :"XDT_EXPIRATIE", :"XBD_EXPIRATIE", :"XOMS_BEL

    _INSTR_TYPE", :"XCD_FONDS", :"XOV_LAAGBR", :"XDT_LAAWYZ" ).

    So despite both OLD and NEW referencing the same unmodifed entity MYTABLE, they generate different SQL statements. According tot he debugger all fields including ov_bron, were correctly filled in the entity before store. I made several variations of OLD and new using duplicate and export/import but the discrepantie was also for every variation of NEW. Can anyone explain why the NEW program has a different interpreation of the entity?  Both OLD and NEW were recompiled today. Does anyone have an explanation. Thanks in advance.



    ------------------------------
    Rob Koppendraier
    Developer
    Waard Verzekeringen BV
    Wognum NL
    ------------------------------


  • 2.  RE: findkey statement generating unexected SQL, HELP needed

    Posted 01-04-2024 03:28

    Hi, I found the reason.  In the first situation the records were written bij the program so it went all right. In the second situation before writing the record there was a call to a service which contained on older definition of the same entity and this service was not recompiled. While only used to compute a next sequence number the entity layout used the service gets somehow overwriten the layout of the entity  the calling program. The calling program fills all fields but somehow the interfacce to de database is one field short. I never experienced it before. I hope this makes sense and is of sme help to you all. 



    ------------------------------
    Rob Koppendraier
    Developer
    Waard Verzekeringen BV
    Wognum NL
    ------------------------------

    Original Message