Icon View Incident Report

Serious Serious
Reported By: Connie McBride
Reported On: 3/24/2006
For: Version 4.22 Build 6
# 2177 Boolean Parameters Not Working Properly in SQL Queries

I have the following two queries.

Query 1 works correctly, including the invoices.
Query 2 returns 0 records with the same parameters, except in DBSYS, when I hard code the parameter values(because if I don't code the parameter values, 0 records are returned in DBSys).

Query 1 -
Select I.*, I.UID as OrderUID
from Invoice I
where
  I.IsFromOrder = false and
 (:StartDate is null or
 I.InvoiceDate >= :StartDate) and
 (:EndDate is null or I.InvoiceDate <= :EndDate) and
 (:RecordUID is null or
 (I.CustUID = :RecordUID or I.UID = :RecordUID)) and
 (:Closed is null or I.Closed = :Closed) 

union all
Select I.*, S.UID as OrderUID
from Invoice I, Service S
where
  I.IsFromOrder = true and
 (:StartDate is null or  I.InvoiceDate >= :StartDate) and
 (:EndDate is null or I.InvoiceDate <= :EndDate) and
 (:RecordUID is null or
 (I.CustUID = :RecordUID or S.UID = :RecordUID or
 S.InvoiceUID = :RecordUID)) and
 (:Closed is null or I.Closed = :Closed) and
  S.InvoiceUID = I.UID 

union all
Select I.*, S.UID as OrderUID
from Invoice I, Install S
where
  I.IsFromOrder = true and
 (:StartDate is null or  I.InvoiceDate >= :StartDate) and
 (:EndDate is null or I.InvoiceDate <= :EndDate) and
 (:RecordUID is null or( I.CustUID = :RecordUID or
   S.UID = :RecordUID or S.InvoiceUID = :RecordUID)) and
 (:Closed is null or I.Closed = :Closed) and
  S.InvoiceUID = I.UID 

Query 2 :
Select I.*
from Invoice I
where
 (:StartDate is null or  I.InvoiceDate >= :StartDate) and     
 (:EndDate is null or I.InvoiceDate <= :EndDate) and
 (:RecordUID is null or
  (I.CustUID = :RecordUID or I.UID = :RecordUID)) and
 (:Closed is null or I.Closed = :Closed)



Comments Comments
The issue was with the :Closed parameter assignment due to an issue with the WordBool type that is used with DBISAM for Boolean values. DBISAM massages WordBool values to handle compatiblity issues with older versions of Delphi that used the value 1 for True instead of the value 255, which the latest versions of Delphi use for True.


Resolution Resolution
Fixed Problem on 3/26/2006 in version 4.23 build 1


Products Affected Products Affected
DBISAM CLX Client-Server
DBISAM CLX Client-Server with Source
DBISAM CLX Standard
DBISAM CLX Standard with Source
DBISAM VCL Client-Server
DBISAM VCL Client-Server with Source
DBISAM VCL Standard
DBISAM VCL Standard with Source

Image