Hi

I'm 'converting my  ( very big -  500 .pas ) application from DbIsam4 to
EDB2.

In my application DBIsam 4 I do not use NULL.

in my database i do not have No field value to Null.

In my delphi code does not exist check for NULL value.

and all work well


My code is 95 % with TDBisamTable,  5 % with TDbIsamQuery

now..........   ask :

if i update to EDB2 :

WHEN the problem of NULL is showed ?

Enrico


>In my application DBIsam 4 I do not use NULL.
>
>in my database i do not have No field value to Null.

Unless you set the default value for fields then whenever a new record is created the fields (columns in ElevateDB speak) are all "populated" with NULL. If you then set the field values that will "remove" the NULL.

>In my delphi code does not exist check for NULL value.
>
>and all work well

So your test to see if a string / memo field is empty is field.AsString = '' not field.IsNull and as long as you stick with that you're OK

>My code is 95 % with TDBisamTable, 5 % with TDbIsamQuery

Which means you have at worst 5% problem If your SQL does not use IS NULL / IS NOT NULL, SET field = NULL then again you're unless you a) don't have a default for string fields and b) use sql to join string fields together eg Forename+' '+Surname. If either Forename or Surname is NULL the result will be NULL which is correct according to the standard but not according to my opinion.

>now.......... ask :
>
>if i update to EDB2 :
>
>WHEN the problem of NULL is showed ?

The way you've explained your app you won't have one. I do, however, suggest that if you don't already have a default for your string fields you set one to '' (ie quote quote = emptystring)

Roy Lambert [Team Elevate]

I'm converting a similar big application at the moment.

My null problems are : I concatenated varchars in SQL very often, e.g. "select forename+'
'+surname as name". In EDB, whenever one varchar concatenated is null (former simply
"empty"), the result of the concatenation is null. This was in fact one of my first EDB
experiences : The user "Administrator", having no forename, disappeared from the user list ;-D

Also, whenever you want to know if a varchar field is empty, you have to check if it's
null, AND if it's emptystring. User enters something, deletes it, field is empty -> but
FieldByName('varchar').isNull alone won't give you the same answer than the one are used to.

I thought of 2 different ways to solve this annoyance...

1) Default every VARCHAR column to '' ...which also means, that every SQL "update/insert
... set varcharfield=null" as well as FieldByName('varchar').Clear and
FieldByName('varchar').isNull has to be replaced.

2) Use a varchar concatenation function instead of "+". Use a checkIfNullOrEmpty-function
instead of "where varcharfield=null". Replace all "FieldByName('varchar').isNull" with
"FieldByName('varchar').isNull or FieldByName('varchar').asString=''".

Both meant about the same amount of work to me, so I decided to use option 1)...for one
reason, because it's the closest DBISAM emulation I can get. For another, because I don't
like fields holding different values that have (at least to me, and to my customers) the
same meaning...and can not be distinguished in any standard DBaware component.

Heiko

Heiko


What are you doing about clob columns. The same problem sort of applies. The one advantage is I rarely join a clob to a clob or string column, but, as I found out, if you default those to emptystring you end up with a huge amount of wasted space.

I'm still fudging round the issue at present. I'm hoping Tim will take on board and implement Eryk's idea about a special operator that joins strings / empty strings / nulls together sensibly. If he does within reasonable timescales I'll probably subclass TEDBTable to set emptystring to null for string columns and RTRIM them at the same time.

Roy Lambert

> I'm still fudging round the issue at present. I'm hoping Tim will take on
> board and implement Eryk's idea about a special operator that joins
> strings / empty strings / nulls together sensibly. If he does within
> reasonable timescales I'll probably subclass TEDBTable to set emptystring
> to null for string columns and RTRIM them at the same time.

will release the component source ?   

Roy,

>>What are you doing about clob columns

Not much. Defaulting them to emptystring is not an option, my DB size would explode.
Luckily I didn't use them very often, almost never do SQL operations with them, and
remember the very few places where I rely on isNull checks...so it's manageable.

Heiko

Enrico


When I've written it I'll post it to the extensions ng.

Roy Lambert

Heiko,

<< Not much. Defaulting them to emptystring is not an option, my DB size
would explode. >>

I'm seriously considering changing this for 2.01 B2 so that an empty CLOB
does not actually write anything to the .EDBBlb file.  It's not that big of
a deal.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim

Thank you Oh mighty one, but let me guess clob IS NULL still wouldn't be the same as clob = ''?


Roy Lambert

Tim

>>I'm seriously considering changing this for 2.01 B2 so that an empty CLOB
>>does not actually write anything to the .EDBBlb file.  It's not that big of
>>a deal.

Hey...great news ! Do it. It will be no standard violation, so I can't see a reason why
not to do it. Besides, it would save disk space even for those not defaulting to null.

Heiko