Icon View Incident Report

Serious Serious
Reported By: Keld Hansen
Reported On: 11/22/2019
For: Version 2.31 Build 9
# 4767 Parameters in Joins Result in Prepared SQL Statements Not Using New Parameters

Paste in the following SQL into an EDB Manager SQL window, click "Prepare" and set the parameter TestDate to 2019-11-22 and execute.

You'll get 5 records…

Then go back to the parameter tab (at the bottom) and change the TestDate value to 2019-11-23

Then press Execute. You get the same 5 records (which you shouldn't).

Then press "Unprepare" and then "Prepare" and then "Execute"

You now get only 2 records (which is the correct amount).

SELECT bd.*,e.No AS "EmpNo",e.Type,IFNULL(e.NameID,e.Name,e.NameID) AS "EmpName",
e.DepartmentNo,e.Placing,es.TradeGroup AS "EmpGroup",es.TradeLevel AS "EmpLevel",c.FromTime,c.ToTime
FROM BookDetailsTEST bd, Employes e
LEFT OUTER JOIN EmpBookSetups es ON es.EmpNo=e.No
AND (bd.TradeGroup IS NULL OR bd.TradeGroup=es.TradeGroup) AND (bd.TradeLevel IS NULL OR bd.TradeLevel<=es.TradeLevel)
LEFT OUTER JOIN Calendar c ON c.EmpNo=e.No AND c."Date"=:TestDate AND c.DepartmentNo=1 AND c.Status=0
AND c.Online=TRUE
WHERE IFNULL(bd.FixedEmpNo,TRUE,e.No=bd.FixedEmpNo) AND e.Active=TRUE
AND e.Online=TRUE
GROUP BY Step,Priority,RefRecID,EmpLevel,EmpNo,FromTime
HAVING FromTime IS NOT NULL OR (Type=2 AND (DepartmentNo IS NULL OR DepartmentNo=1))



Comments Comments
The issue was only with parameters in join conditions, not in WHERE clauses.


Resolution Resolution
Fixed Problem on 11/25/2019 in version 2.31 build 10


Products Affected Products Affected
ElevateDB Additional Software and Utilities
ElevateDB DAC Client-Server
ElevateDB DAC Client-Server with Source
ElevateDB DAC Standard
ElevateDB DAC Standard with Source
ElevateDB DAC Trial
ElevateDB LCL Standard with Source
ElevateDB PHP Standard
ElevateDB PHP Standard with Source
ElevateDB PHP Trial
ElevateDB VCL Client-Server
ElevateDB VCL Client-Server with Source
ElevateDB VCL Standard
ElevateDB VCL Standard with Source
ElevateDB VCL Trial

Image