Hi,

I'm aware with EDB that I can have a field that is null, Or an empty
string '', and the two are not the same.

In many of my SQL scripts, I use the following:

select *
from mytable
where myfield is null

Does this mean that I now need to use the statement:

select *
from mytable
where ((myfield is null) or (myfield = ''))

....for string fields?


If so - is there a simple way, such as setting a trigger to reset all
empty fields to null whenever the user tries to save them, so I can
treat all empty fields the same. (as NULL)?

Or - am I looking at this the wrong way?

Cheers

Adam.

Adam,

> Does this mean that I now need to use the statement:
>
> select *
> from mytable
> where ((myfield is null) or (myfield = ''))

There is a function:

select *  from mytable where coalesce(myfield,'') = '';

> If so - is there a simple way, such as setting a trigger to reset all
> empty fields to null whenever the user tries to save them, so I can
> treat all empty fields the same. (as NULL)?

Sure could you do that, but with "coalesce" you are done.

Uli

Adam

I eventually (after much bitching and research) decided that I would no longer use an empty string all of my fields either have data or NULL. I altered my Delphi code to use .Clear rather than .AsString := '' and modified the TEDBTable component (its in the extensions in the same .pas as TnlhTemporary) to do the same so that I don't have to bother.

If I build SQL on the fly I will set to null rather than empty string.

So if like me you tend to use tables then its easy - use my component. If you use sensitive sql result sets the same can be done for a query (haven't uploaded that yet). The only problem is if you use SQL code, gathering data from data entry fields. In that case you have to make sure you build the sql with nulls.

You could use a trigger but you have to do it for all columns for all tables. There's nothing I know of that will allow you to do it easily.

The reason I went for this approach rather than COALESCE(field,'') is twofold 1) I wanted consistency and 2) I didn't want to have to wrap EVERY text for every CHAR or VARCHAR column in COALESCE - IS NULL is much nicer.

The other much recommended approach was to use a DEFAULT for every CHAR and VARCHAR column of ''

In its favour it does have consistence, you just have to make sure you never null any fields and also has the effect of allowing you to combine CHAR and VARCHAR columns without worrying about the result disapearing in a puff of smoke when you try and add NULL to anything.

Roy Lambert [Team Elevate]

Thanks Uli and Roy for your replies.

I agree with Roy - I don't think the coalesce is best for my approach
for me as I don't want to be wrapping every field with coalesce in my SQL's.

I use .clear on TFields too, and set field = null in SQL normally.

However the issue I have is the unknowns. If I miss somewhere, it still
allows the potential for a blank value that is not null - causing
incorrect figures in my reports.

It also looks like I'd be working 'against' the design of the database.

This may have been discussed before - I don't know, but how hard would
it be to change the engine component of a database to treat blank values
as Null across the board. (A boolean property that can be turned on & off)?

I'd be happy to edit the code myself if it's not too much trouble - but
then again, if it's easy to do - would Tim be open to the possiblity of
adding this in an additional release?

Or - does this break some sort of industry standard? (I'm still happy to
break the industry standard, but could understand Tim not wanting to

(BTW Roy - you're the main reason I'm going across to EDB at the moment.
Currently - I just can't see the benefits on going to EDB over DBISam.
There seems to be too many backwards steps - however after reading your
older posts, and seeing that you were in the same boat, but now you love
EDB - I'm guessing that there are advantages I'm yet to see that will
outweight the issues I'm facing).

And if not... I'll hunt you down.

Cheers

Adam.

Thanks Uli and Roy for your replies.

I agree with Roy - I don't think the coalesce is best for my approach
for me as I don't want to be wrapping every field with coalesce in my SQL's.

I use .clear on TFields too, and set field = null in SQL normally.

However the issue I have is the unknowns. If I miss somewhere, it still
allows the potential for a blank value that is not null - causing
incorrect figures in my reports.

It also looks like I'd be working 'against' the design of the database.

This may have been discussed before - I don't know, but how hard would
it be to change the engine component of a database to treat blank values
as Null across the board. (A boolean property that can be turned on & off)?

I'd be happy to edit the code myself if it's not too much trouble - but
then again, if it's easy to do - would Tim be open to the possiblity of
adding this in an additional release?

Or - does this break some sort of industry standard? (I'm still happy to
break the industry standard, but could understand Tim not wanting to

(BTW Roy - you're one of the main reason I'm going across to EDB at the
moment. Currently - I just can't see the benefits on going to EDB over
DBISam. There seems to be too many backwards steps - however after
reading your older posts, and seeing that you were in the same boat, but
now you love EDB - I'm guessing that there are advantages I'm yet to see
that will outweight the issues I'm facing).

And if not... I'll hunt you down.

Cheers

Adam.

This message was cancelled from within Mozilla Thunderbird.

Adam


The more you research this issue the more you'll find its "religious"

>However the issue I have is the unknowns. If I miss somewhere, it still
>allows the potential for a blank value that is not null - causing
>incorrect figures in my reports.

You have to differentiate between NULL handling for figures, NULL handling for strings and NULL handling for other types.

One of the many reasons I hate this behaviour is that unless you add additional processing (as Tim has done for the grids in EDBManager) its impossible for a human being to spot what's NULL and what's emptyspace.

However, for numbers, I don't think there should be a problem. Having said that I have no idea of what doing integerfield.AsString := '' actually does. I shall have to test and see.

Personally I believe that SQLs NULL handling is inconsistent, possibly caused by the fact that there are functions that process down but none that process across.

>It also looks like I'd be working 'against' the design of the database.
>
>This may have been discussed before - I don't know, but how hard would
>it be to change the engine component of a database to treat blank values
>as Null across the board. (A boolean property that can be turned on & off)?

Since I don't have the source I can't comment. What I can say is that until recently many of the big boys (including ORACLE) had switches that did just this, but I think they're all compliant now.

>I'd be happy to edit the code myself if it's not too much trouble - but
>then again, if it's easy to do - would Tim be open to the possiblity of
>adding this in an additional release?

He's always refused so far.

>Or - does this break some sort of industry standard? (I'm still happy to
>break the industry standard, but could understand Tim not wanting to

It will break the SQL standard. Why they put it in to start with I do not understand. Its a half baked attempt to allow states to be set against data and differentiating between empty and empty string has little or no relevance in the real world. At least that's my opinion. Its also a good demonstration of the vested interests in the standard setting body. It also demonstrates how they want a programming language but ignore the fact that at some point it has to get to people.

To back up my statement about real world usage. I asked in these ngs about it. Ignoring the old rubbish about the government need to know if you have a middle name or not I was given one example of it usage to determine if a question had been asked or not, and that usage logic was flawed and would have been better served with a boolean column.

>(BTW Roy - you're one of the main reason I'm going across to EDB at the
>moment. Currently - I just can't see the benefits on going to EDB over
>DBISam. There seems to be too many backwards steps - however after
>reading your older posts, and seeing that you were in the same boat, but
>now you love EDB - I'm guessing that there are advantages I'm yet to see
>that will outweight the issues I'm facing).

Love it is probably a bit strong. The biggest problem is that DBISAM was so easy to use and in quite a few cases it still feels as though I'm fighting with ElevateDB. One annoyance is that with DBISAM when I wanted to try something out I dropped a table component on a form, pointed it at a directory and got going. Now I have to drop 4 components on the form and hook them together before I start.

>And if not... I'll hunt you down.

That's OK - I've moved.

Roy Lambert [Team Elevate]

ps what are you doing about no right trimming any more?

Hi Roy,

So far - I think you and I must have very similar thought patterns!

>> I'd be happy to edit the code myself if it's not too much trouble - but
>> then again, if it's easy to do - would Tim be open to the possiblity of
>> adding this in an additional release?
>
> He's always refused so far.

Aah... so he's already been asked. I won't hassle him then. (Well, on
this issue

>> (BTW Roy - you're one of the main reason I'm going across to EDB at the
>> moment. Currently - I just can't see the benefits on going to EDB over
>> DBISam. There seems to be too many backwards steps - however after
>> reading your older posts, and seeing that you were in the same boat, but
>> now you love EDB - I'm guessing that there are advantages I'm yet to see
>> that will outweight the issues I'm facing).
>
> Love it is probably a bit strong. The biggest problem is that DBISAM was so easy to use and in quite a few cases it still feels as though I'm fighting with ElevateDB. One annoyance is that with DBISAM when I wanted to try something out I dropped a table component on a form, pointed it at a directory and got going. Now I have to drop 4 components on the form and hook them together before I start.

Yes - I'm still getting my head around the basics of EDB. I have so many
years invested in DBISam, so many custom scripts - set ways of doing
things... I must admit - I've been quite a bit frusturated at times.

> ps what are you doing about no right trimming any more?

I haven't seen an issue. I've been using VarChars as opposed to Chars
which from what I can tell works the same as string fields in DBISam.

Cheers mate

Adam.

Adam

>> ps what are you doing about no right trimming any more?
>
>I haven't seen an issue. I've been using VarChars as opposed to Chars
>which from what I can tell works the same as string fields in DBISam.

Yes if you're using table components or editing a sensitive (live) query, no if you use SQL directly.

UPDATE table SET field = '1234567890'

will fail if field is a VARCHAR with a length less than 10

UPDATE table SET field = '12345      '

will leave the trailing blanks in place. They're ignored in an SQL comparison (ie SELECT * FROM table WHERE field = '12345' will work) but will cause a fail in a Delphi comparison!

Roy Lambert [Team Elevate]

Hi Roy,

I'd thank you for your reply... but I'm not sure I like the answer.

>> I haven't seen an issue. I've been using VarChars as opposed to Chars
>> which from what I can tell works the same as string fields in DBISam.
>
> Yes if you're using table components or editing a sensitive (live) query, no if you use SQL directly.
>
> UPDATE table SET field = '1234567890'
>
> will fail if field is a VARCHAR with a length less than 10

Shouldn't it fail? (ie - the value given is larger than the size of the
varchar field)?

> UPDATE table SET field = '12345      '
>
> will leave the trailing blanks in place.

As I would expect too...

But shouldn't Update table SET field = '12345' work on a varchar(10)?

> They're ignored in an SQL comparison (ie SELECT * FROM table WHERE field = '12345' will work) but will cause a fail in a Delphi comparison!

Not sure I follow you by what you mean with a 'Delphi Comparison'?

Cheers mate

Adam.