Uniface User Forum

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 enitity 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" <-- why does this happen.

-> Hits selected: 871928 .

The findkey for the second record generates different and unwanted SQL. It is the same statement 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.  I can anyone explain why the NEW program has a different interpreation of the entity?  Both OLD and NEW were recompiled today in the same environment. Does anybody have an explanation for this situation. Thanks in advance. 

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 enitity 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" <-- why does this happen.

-> Hits selected: 871928 .

The findkey for the second record generates different and unwanted SQL. It is the same statement 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.  I can anyone explain why the NEW program has a different interpreation of the entity?  Both OLD and NEW were recompiled today in the same environment. Does anybody have an explanation for this situation. Thanks in advance. 

Hi Rob,

Question is: Is the value of $curkey = 1 when your code is running?

I feel that behaviour is related to findkey checking for another key, not the primary key...for really unknown reason(s).

If you want to check explicitely for the primary key as quick&dirty solution you could try to force key 1 instead of using $curkey.

If that will be the case, "why is happening" is a good question... probably your case should be analyzed from the ULab, following you in the process of duplicating the original component and applying the changes you've done.

Hope it helps!

Gianni

P.S. Happy new year to everyone! :-)