LS,

Trying to understand how to use null in select statements: is there a
difference in null and ''

id_campagne is varchar(10)

The result of

select * from Registratie
where id_campagne is null

is not the same result as

select * from Registratie
where id_campagne = ''

Regards, Harry

Harry


You obviously missed my thread on nulls. Apparently an empty string isn't the same as a null in ElevateDB. This is the STUPID sql standard! (which I hate .

The best bet (from my view) is to double up the tests eg

select * from Registratie
where (id_campagne is null or id_campagne ='')

Oh yes and if you use navigational methods you'll also have to use field.Clear rather than field.AsString := ''; to "empty" a field.

Roy Lambert

Roy,

I must have missed missed that thread, yes. Thanks for pointing it out.
Nice to know that now we have to check for null, '' and perhaps ' ' (a space
char). Life can't be complicated enough

Some questions though:
- so a field can only be null initialy (no value asigned yet) or if it's
specifficly set to null?
- clearing an editbox or a grid's cell by selecting the value and pressing
DEL. Will it set the field to '' or to null?

Regards, Harry



"Roy Lambert" <roy.lambert@skynet.co.uk> schreef in bericht
news:34E8D0E8-A6FB-49D5-AF0B-6F10A030C151@news.elevatesoft.com...
> Harry
>
>
> You obviously missed my thread on nulls. Apparently an empty string isn't
the same as a null in ElevateDB. This is the STUPID sql standard! (which I
hate .
>
> The best bet (from my view) is to double up the tests eg
>
> select * from Registratie
> where (id_campagne is null or id_campagne ='')
>
> Oh yes and if you use navigational methods you'll also have to use
field.Clear rather than field.AsString := ''; to "empty" a field.
>
> Roy Lambert
>
>

Harry


>I must have missed missed that thread, yes. Thanks for pointing it out.
>Nice to know that now we have to check for null, '' and perhaps ' ' (a space
>char). Life can't be complicated enough

What I'm going to do it just occurred to me) is in a trigger trim any trailing spaces and set any fields that have '' to null. That way I'll only have one test to bother about. I'll need to come up with a consistent strategy for handling boolean, integer and decimal fields.


>Some questions though:
>- so a field can only be null initialy (no value asigned yet) or if it's
>specifficly set to null?

Yup

>- clearing an editbox or a grid's cell by selecting the value and pressing
>DEL. Will it set the field to '' or to null?

Dunno. My guess is '' but its down to the implementation of the component.

Roy Lambert

Harry,

<< - so a field can only be null initialy (no value asigned yet) or if it's
specifficly set to null? >>

Correct.

<< - clearing an editbox or a grid's cell by selecting the value and
pressing DEL. Will it set the field to '' or to null? >>

It will be set to NULL for non-string fields.  For string fields, the field
is just considered empty, but not NULL.  You can, of course, modify this
behavior in your application by attaching an OnSetText event handler to your
string fields to have it Clear the field instead.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim


Thought for the manual. Can we have a table somewhere showing the various datatypes and how "traditional DBISAM" relates to ElevateDB eg

Char & VarChar  = '' <> = null

Integer <> 0 <> null (or possibly <> 0 = null - whilst I might have been told this is not yet part of my internal knowledgebase)

field.AsString := ''  <> field.Clear

etc


Roy Lambert

Roy,

<< Thought for the manual. Can we have a table somewhere showing the various
datatypes and how "traditional DBISAM" relates to ElevateDB eg

Char & VarChar = '' <> = null >>

The only difference is with the CHAR/VARCHAR types (ftFixedChar/ftString).
The rest are identical to DBISAM in terms of NULL handling.

The general information about how NULLs is handled is here:

http://www.elevatesoft.com/edb1sql_nulls.htm

--
Tim Young
Elevate Software
www.elevatesoft.com