DataParameter in SQL is not what is specified in call because of bad automatic datatype detection of the DA schema modeler

Data Abstract
1

I just spent more than half a day investigating a very weird issue with DA 10.0.0.1597

I let DA perform a GetData with 2 int parameters: @dynNL & @dynFR

The server logs the request and in the log I see the correct values
@dynNL=1 & @dynFR=-1

However the executed query and the result is wrong.
SQL profile shows me 3 parameters with @dynFR=1 as one of them.

I verified the binary data on server side and the number of rows match the bad query.

So my thought here was that there is a bug in this RO/DA release

MY OWN LOG

2024-12-04 18:39:14.5064|INFO|CloudEnabledDB(GetData/Officinall)|SELECT prd_primkey,prd_cnk,pnm_naam,prd_korting,prd_lok_primkey,lok_lokatienaam,
prd_automatischbestellen,prd_lev_primkey,prd_minvoorraad,prd_maxvoorraad,
prd_aantaltebestellen,prd_Wetgeving,prd_Bereiding,prd_BewaarTemperatuur,
prd_eigenaankoopprijs,prd_publieksprijs,prd_eigenprijs,prd_richtprijs,
prd_stock,prd_heeftvervaldatum,prd_atc_code,
prd_terugnamestatus,prd_terugnamevoorverval,prd_terugnamenaverval,prd_isleverbaar,
pnm_nederlands,pnm_frans,
prg_primkey,prg_rel_primkey, prd_sbs_Primkey
FROM vProductDetailLightML
LEFT JOIN productgroep ON prd_primkey=prg_prd_primkey
WHERE ([prg_rel_primkey] = @DAPARAM_1)
AND ( (pnm_nederlands = @dynNL) OR ( @dynNL =-1) )
AND ( (pnm_frans = @dynFR) OR ( @dynFR =-1) )

with parameters:@dynNL=1|@dynFR=-1|@DAPARAM_1=100000115| executed in 1.9994ms and returned 1745 bytes

SQL PROFILER

exec sp_executesql N’SELECT prd_primkey,prd_cnk,pnm_naam,prd_korting,prd_lok_primkey,lok_lokatienaam,
prd_automatischbestellen,prd_lev_primkey,prd_minvoorraad,prd_maxvoorraad,
prd_aantaltebestellen,prd_Wetgeving,prd_Bereiding,prd_BewaarTemperatuur,
prd_eigenaankoopprijs,prd_publieksprijs,prd_eigenprijs,prd_richtprijs,
prd_stock,prd_heeftvervaldatum,prd_atc_code,
prd_terugnamestatus,prd_terugnamevoorverval,prd_terugnamenaverval,prd_isleverbaar,
pnm_nederlands,pnm_frans,
prg_primkey,prg_rel_primkey, prd_sbs_Primkey
FROM vProductDetailLightML
LEFT JOIN productgroep ON prd_primkey=prg_prd_primkey
WHERE ([prg_rel_primkey] = @DAPARAM_1)
AND ( (pnm_nederlands = @dynNL) OR ( @dynNL =-1) )
AND ( (pnm_frans = @dynFR) OR ( @dynFR =-1) )

‘,N’@dynNL bit,@dynFR bit,@DAPARAM_1 int’,@dynNL=1,@dynFR=1,@DAPARAM_1=100000115

2

SOLUTION/CAUSE:

I eventually found out that my parameters had the data type boolean but in fact I use them as integers.

So it appears the reason why DA altered a -1 into a 1 is because he interpretes it as true as soon as it is not 0

So this is a bug in my DA schema but I feel DA could have helped a bit.

The schema modeler generated the parameters and boolean data type entirely on its own based on the SQL below.
I believe it would be better if it did not assign a type yet.
An alternative would be to have DA do data type checking on the parameters and throw an error when I pass an integer to a boolean parameter.

I have posted my issue because others might run into the same issue and/or the people at RemObjects might add data type checking which would have helped me in the long search after this bug.

SELECT prd_primkey,prd_cnk,pnm_naam,prd_korting,prd_lok_primkey,lok_lokatienaam,
prd_automatischbestellen,prd_lev_primkey,prd_minvoorraad,prd_maxvoorraad,
prd_aantaltebestellen,prd_Wetgeving,prd_Bereiding,prd_BewaarTemperatuur,
prd_eigenaankoopprijs,prd_publieksprijs,prd_eigenprijs,prd_richtprijs,
prd_stock,prd_heeftvervaldatum,prd_atc_code,
prd_terugnamestatus,prd_terugnamevoorverval,prd_terugnamenaverval,prd_isleverbaar,
pnm_nederlands,pnm_frans,
prg_primkey,prg_rel_primkey, prd_sbs_Primkey
FROM vProductDetailLightML
LEFT JOIN productgroep ON prd_primkey=prg_prd_primkey
WHERE {Where}
AND ( (pnm_nederlands = :dynNL) OR ( :dynNL =-1) )
AND ( (pnm_frans = :dynFR) OR ( :dynFR =-1) )