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.
------------------------------
Rob Koppendraier
Developer
Waard Verzekeringen BV
Wognum NL
------------------------------