RE: [DUG]: Odd BDE problem with types
>OTOH if you know CustomerCode can never be null ...< That's the interim solution. It was never intended to be a mandatory field, but making it so doesn't have significant impact. The bizarre thing was the query ran ok on some machines... Terry --- New Zealand Delphi Users group - Delphi List - [EMAIL PROTECTED] Website: http://www.delphi.org.nz
RE: [DUG]: Odd BDE problem with types
Indeed - SQL can't use null in a comparison.
That is why I was suggesting you compare the null parameter separately -
that breaks the automatic nature of the master-detail though. Since AFAIK a
query's DataSource is really a ParamSource, and only goes looking for a
field with the same name as the parameter, the only downside to handling
the null parameter in code is not being able to prepare the query - but
then this may be the source of the problem.
ie
if MasterDataset.FieldByName('CustomerCode').IsNull then
...WHERE (CustomerCode IS NULL)
else ... Format(...WHERE (CustomerCode = %d),
[MasterDataset.FieldByName('CustomerCode').As...])
I've never had any problems with this kind of code even though it's
marginally slower. It does mean you are resetting the SQL each time the
query runs, but it will at least tell you if it's testing the parameter for
null that is the problem (even though it was actually never null).
OTOH if you know CustomerCode can never be null ...
Max
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On
Behalf Of Terry Johnson
Sent: Sunday, 4 April 1999 23:46
To: Multiple recipients of list delphi
Subject: RE: [DUG]: Odd BDE problem with types
The field values were ok (all actually non-null in the DB), and so the
parameter value could not be null either as :CustomerCode was from a master
dataset derived from the same DB (with another query). The problem I was
trying to get around is that under the rules of SQL the evaluation of (a=b)
will fail if both a and b are null, hence (a=b) or (a is null and b is
null) covers both possibilities. Any suggestions on alternatives?
Terry
>It may be worth focussing on whether it is failing on the field value or
the parameter value.
As a matter of course I would have tested the value of the [parameter
":CustomerCode" and changed the where clause to either (CustomerCode =
:CustomerCode) or just (CustomerCode IS NULL).
I haven't experienced this problem, with "is null" that is, but I also
avoid complex mixtures of AND and OR since I found that 16 and 32 bit BDE
had different, and mutually exclusive, problems with complex WHERE clauses.
This was all from the transition from D1 to D2 so it's old info though.
Max
<
---
New Zealand Delphi Users group - Delphi List - [EMAIL PROTECTED]
Website: http://www.delphi.org.nz
---
New Zealand Delphi Users group - Delphi List - [EMAIL PROTECTED]
Website: http://www.delphi.org.nz
RE: [DUG]: Odd BDE problem with types
The field values were ok (all actually non-null in the DB), and so the parameter value could not be null either as :CustomerCode was from a master dataset derived from the same DB (with another query). The problem I was trying to get around is that under the rules of SQL the evaluation of (a=b) will fail if both a and b are null, hence (a=b) or (a is null and b is null) covers both possibilities. Any suggestions on alternatives? Terry >It may be worth focussing on whether it is failing on the field value or the parameter value. As a matter of course I would have tested the value of the [parameter ":CustomerCode" and changed the where clause to either (CustomerCode = :CustomerCode) or just (CustomerCode IS NULL). I haven't experienced this problem, with "is null" that is, but I also avoid complex mixtures of AND and OR since I found that 16 and 32 bit BDE had different, and mutually exclusive, problems with complex WHERE clauses. This was all from the transition from D1 to D2 so it's old info though. Max < --- New Zealand Delphi Users group - Delphi List - [EMAIL PROTECTED] Website: http://www.delphi.org.nz
RE: [DUG]: Odd BDE problem with types
It may be worth focussing on whether it is failing on the field value or the parameter value. As a matter of course I would have tested the value of the [parameter ":CustomerCode" and changed the where clause to either (CustomerCode = :CustomerCode) or just (CustomerCode IS NULL). I haven't experienced this problem, with "is null" that is, but I also avoid complex mixtures of AND and OR since I found that 16 and 32 bit BDE had different, and mutually exclusive, problems with complex WHERE clauses. This was all from the transition from D1 to D2 so it's old info though. Max -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] On Behalf Of Myles Penlington Sent: Thursday, 1 April 1999 07:34 To: '[EMAIL PROTECTED]' Subject: RE: [DUG]: Odd BDE problem with types I've had lots of problems with the BDE when dealing with "Is null" in SQL where statements, in my cases it always fails 100% of the time. I have managed to avoid using "is null" and have not taken it any further. Myles -Original Message- From: Terry Johnson [SMTP:[EMAIL PROTECTED]] Sent: Wednesday, March 31, 1999 11:55 PM To: Multiple recipients of list delphi Subject:[DUG]: Odd BDE problem with types Now this is a little weird. Picture this; Client site using Delphi app with databases in the traditional paradox tables. All runs faultlessly. After several months, a report fails with a 'type mismatch' error. With the same databases on my machine, it works fine. Same DB, same BDE, same app. But on some other machines in the office, it fails. It seems that on machines with Delphi 2 installed, it runs fine. The program is analysed, and found to be failing on an SQL statement in a master/detail relationship; .. and (CustomerCode=:CustomerCode) or ((CustomerCode is null) and (:CustomerCode is null)) ... If we remove the 'or' clause, it works fine on all machines. Any ideas? Terry --- New Zealand Delphi Users group - Delphi List - [EMAIL PROTECTED] Website: http://www.delphi.org.nz winmail.dat
RE: [DUG]: Odd BDE problem with types
I've had lots of problems with the BDE when dealing with "Is null" in SQL where statements, in my cases it always fails 100% of the time. I have managed to avoid using "is null" and have not taken it any further. Myles -Original Message- From: Terry Johnson [SMTP:[EMAIL PROTECTED]] Sent: Wednesday, March 31, 1999 11:55 PM To: Multiple recipients of list delphi Subject:[DUG]: Odd BDE problem with types Now this is a little weird. Picture this; Client site using Delphi app with databases in the traditional paradox tables. All runs faultlessly. After several months, a report fails with a 'type mismatch' error. With the same databases on my machine, it works fine. Same DB, same BDE, same app. But on some other machines in the office, it fails. It seems that on machines with Delphi 2 installed, it runs fine. The program is analysed, and found to be failing on an SQL statement in a master/detail relationship; .. and (CustomerCode=:CustomerCode) or ((CustomerCode is null) and (:CustomerCode is null)) ... If we remove the 'or' clause, it works fine on all machines. Any ideas? Terry --- New Zealand Delphi Users group - Delphi List - [EMAIL PROTECTED] Website: http://www.delphi.org.nz application/ms-tnef
