Hi

I thought that the Subject would grab Roy's attention

In DBISAM3 the following (abbreviated) SQL delivered the correct records

------------------------------------
SELECT * /* Heaps of Stuff */
FROM BankTrans  B
LEFT OUTER JOIN TenantTrans TT ON TT.TransNo = B.TransNo
/* other joins too */
WHERE B.StatementID = 80
AND B.Presented = True
AND B.TransType <> 'B/Fwd'
AND TT.TransType <> 'Bond Ref'
ORDER BY BatchNo, TransDate, TransNo
----------------------------------

Note: TenantTrans doesn't have a matching row for every BankTrans row
so TT.TransType is NULL in those cases.

I would have expected TT.TransType <> 'Bond Ref' to return TRUE in those
cases.

BUT it appears not and I had to change the test to:-

((TT.TransType <> 'Bond Ref') OR TT.TransType IS NULL)

------------------------------------
SELECT * /* Heaps of Stuff */
FROM BankTrans  B
LEFT OUTER JOIN TenantTrans TT ON TT.TransNo = B.TransNo
/* other joins too */
WHERE B.StatementID = 80
AND B.Presented = True
AND B.TransType <> 'B/Fwd'
AND ((TT.TransType <> 'Bond Ref') OR TT.TransType IS NULL)
ORDER BY BatchNo, TransDate, TransNo
----------------------------------

This gives the expect result set.

So it looks like an equality compare of a NULL column to a literal
returns FALSE - seems barmy to me.

Not sure how I'm going to get on with yards and yards of SQL (well
kilometers really) scattered around the app and all needing to be
checked   Tell me I'm wrong ...

Cheers

Jeff

On 23/10/2014 10:24 a.m., Jeff Cook wrote:

BUT didn't proof read properly!!!

> So it looks like an equality compare of a NULL column to a literal
> returns FALSE - seems barmy to me.

I meant to say:-

So it looks like an INequality compare of a NULL column to a literal
returns FALSE - seems barmy to me.

On 10/22/2014 5:29 PM, Jeff Cook wrote:
> On 23/10/2014 10:24 a.m., Jeff Cook wrote:
> So it looks like an INequality compare of a NULL column to a literal
> returns FALSE - seems barmy to me.

Does this help :

http://www.elevatesoft.com/manual?action=viewprop&id=edb2&product=rsdelphiwin32&version=XE7&comp=TEDBEngine&prop=StandardNullBehavior


Raul

On 23/10/2014 10:32 a.m., Raul wrote:
> Does this help :
>
> http://www.elevatesoft.com/manual?action=viewprop&id=edb2&product=rsdelphiwin32&version=XE7&comp=TEDBEngine&prop=StandardNullBehavior
>

Hi Raul

Thanks for yor reply. The answer to your question is "Yes and No".

Now I understand (I think!) and knowing about StandardNullBehavior I
have search the forums and found previous posts ... It all looks
familiar, so I must have seen the posts way back when.

BUT, I can't see where to implement it.

I've tried   EDBComps.Engine.StandardNullBehavior := False;
in my Datamodule before the database is opened, but it seems to ignore it.

I've tried   EDBComps.Engine.StandardNullBehavior := False;
in the OnActivate event of my main form and I get an error:-

Cannot perform this operation when the engine has been started.

I've tried   TEDBEngine.StandardNullBehavior := False;
in the OnActivate event of my main form and I get an compilation error:-

[DCC Error] apmMain.pas(2352): E2233 Property 'StandardNullBehavior'
inaccessible here

Cheers

> So it looks like an equality compare of a NULL column to a literal
> returns FALSE - seems barmy to me.
>
> Not sure how I'm going to get on with yards and yards of SQL (well
> kilometers really) scattered around the app and all needing to be
> checked   Tell me I'm wrong ...

I went over this step myself, and posted a few messages about it a
month or so ago. The key is to determine if NULL makes sense in the
column or not. If not, set the default for the column to be False (or
whatever the default should be) and then these problems go away. NULL
is a good thing when you want a "no known value", and you can't assume
it is false because it isn't known. But if the options are only ever
true or false, or a name or empty string, then set the column to
default to what you want and it all works as it did before.

For me, it was a matter of determining which columns to change, or
whether to change the SQL. The SQL is easier sometimes as you can
change it from "NOT FALSE" to "IS TRUE" (thus NULLs now work to your
favour).

--

Matthew Jones

Jeff

>BUT, I can't see where to implement it.
>
>I've tried EDBComps.Engine.StandardNullBehavior := False;
>in my Datamodule before the database is opened, but it seems to ignore it.
>
>I've tried EDBComps.Engine.StandardNullBehavior := False;
>in the OnActivate event of my main form and I get an error:-
>
>Cannot perform this operation when the engine has been started.
>
>I've tried TEDBEngine.StandardNullBehavior := False;
>in the OnActivate event of my main form and I get an compilation error:-
>
>[DCC Error] apmMain.pas(2352): E2233 Property 'StandardNullBehavior'
>inaccessible here


It did, and would have earlier if I hadn't been tucked up and snoring.

I did my conversions before Tim added that switch so have no idea how it works apart from what it says in the manual.

However,  what I can say is that depending on how you're using ElevateDB you'll have to set the flag in the engine, session (localStandardNullBehaviour) and/or the server (Behaviour tab).

The main suggestion that was made to me when I ranted (mainly about character fields) on was to have a default to set the field to '' (ie emptystring). I didn't really like that and went my own way.

One of the problems I perceived was that by setting a default it would mean that Delphi and SQL behaved differently. I have many placed in my code where I go field.Clear; This would no longer be acceptable because according to the new SQL standard emptystring <> NULL.

I subclassed TEDBTable & TEDBQuery to do what I wanted. Setting a character field to emptystring sets it to NULL.

The code is in the extensions newsgroup.

Roy Lambert

Matthew

>I went over this step myself, and posted a few messages about it a
>month or so ago. The key is to determine if NULL makes sense in the
>column or not. If not, set the default for the column to be False (or
>whatever the default should be) and then these problems go away. NULL
>is a good thing when you want a "no known value", and you can't assume
>it is false because it isn't known. But if the options are only ever
>true or false, or a name or empty string, then set the column to
>default to what you want and it all works as it did before.

That almost makes sense as long as you use SQL and only SQL to maintain and query your database. However, if you use field.Clear rather than field.AsString := '' you're in trouble. In addition you can no longer use field.IsNull but have to use field.AsWhateverItsTypeIs = DefaultValueForThisType.

IMBHO (in my big headed opinion) this creates a major inconsistency. It stems from the fact that splitting applications into multiple layers (database, presentation etc) is a "good thing". Unfortunately the people who define each layer ignore each other (or that's how it seems to me).

I've never denied the usefulness of NULLs its just that I think emptystring has no existence and is the same as NULL.

Roy Lambert

Roy Lambert wrote:

> However, if you use field.Clear rather than field.AsString := ''
> you're in trouble. In addition you can no longer use field.IsNull but
> have to use field.AsWhateverItsTypeIs = DefaultValueForThisType.

That's a good point, but then it depends rather on what you do with the
values etc. The key is that ElevateDB is a SQL database, and SQL does
what it does - it is better that it works the way the world expects it
to, and we as programmers should adjust to fit that way. Then we are
just haggling over the price, as the joke has it, and we can decide if
the "make the problem go away" options of localStandardNullBehaviour
and the like are worth doing (a quick fix) or not. From my limited
experience, the defaults were an easy and proper solution. If you use
..Clear, then perhaps you might want to consider that (not hard to
search a project and change to .AsString := ''

At the end of the day, EDB is flexible enough to work whichever way
someone wants to work.

--

Matthew Jones

Matthew

Oh dear, you really shouldn't have done that -  I'm feeling bored and you've just lit the blue touchpaper <vbg>

>> However, if you use field.Clear rather than field.AsString := ''
>> you're in trouble. In addition you can no longer use field.IsNull but
>> have to use field.AsWhateverItsTypeIs = DefaultValueForThisType.
>
>That's a good point, but then it depends rather on what you do with the
>values etc.

>The key is that ElevateDB is a SQL database, and SQL does
>what it does

Yes and no Your statement is a little over general. A specific dialect of SQL does what a specific dialect of SQL does. Tim has added some nice extensions and so have other vendors. I accept that there is a large amount of core functionality that is the same but there's also a lot of variance. Look at some of the questions that get asked on the newsgroups.

You may also have noticed some small differences between DBISAM sql and ElevateDB sql, and yes I know they were implementations of different versions of the standard.

>- it is better that it works the way the world expects it
>to, and we as programmers should adjust to fit that way.

If by world you mean the standards committee then I disagree. I can remember a discussion with Ole where it was basically they knew better than me and I should shut up (OK overstating somewhat to make the point). Standards such as the SQL standard are simply the opinions of people and as such open to challenge, and change.

On the other hand if by world you meant the general populace then I doubt the majority would know what you were talking about so we can safely ignore them until standards setting becomes a true democratic process

So to restate your point:

"it is better that it works the way the SQL standards committee desire, and we as programmers should do as we're told."

>From my limited
>experience, the defaults were an easy and proper solution.

Certainly easy and quick and I did seriously consider it. If the switch had been available back (and I had been agitating for it) then I might well have used it.

>If you use
>.Clear, then perhaps you might want to consider that (not hard to
>search a project and change to .AsString := ''

Not quite as easy as you think - you're assuming every occurrence of .Clear is on a string field, and you're also ignoring the fact that you then have to alter any use of .IsNull as well, and check all your filters and sql as well for use of field IS NULL

>At the end of the day, EDB is flexible enough to work whichever way
>someone wants to work.

Nope, noway, never. Its a good product, has a lot of capability but "work whichever way" I want - see first sentence here.

Being wicked. Tell me what you include in EDB and I'll give you some real examples of ways I'd like to work. They will range from trivial to unachievable but they will be genuine examples of how I'd like to work.

Roy Lambert

Roy Lambert wrote:

> So to restate your point:
>
> "it is better that it works the way the SQL standards committee
> desire, and we as programmers should do as we're told."

Yes, that's a better way of stating it. I agree that SQL is not ideal,
but it is better that I can look up a book on SQL and apply it to my
database than have some custom implementation where nothing standard
applies. IMO anyway.

If you don't want the benefits, stick with DBISAM. 8-)

It is of course more than just .Clear to .AsString, but it is probably
not as hard as one might think, depending on your code of course. And I
expect your wicked self can find lots of nasties, but what other
database doesn't have the same? At least with a standard SQL you can
use the internet to find solutions to the common problems.

--

Matthew Jones