Hi all,

the emails from Elevate made me think again about converting existing
projects from DBISAM 4 to EDB. There is no _real_ need because they work
without a hitch, but sometime you have to make the step just to see new
horizons.

Changing the components is easy. Changing the events should not be that
hard, too. But the one obstacle that let me finch from doing it is the NULL
vs. empty string behaviour. And it seems that I am not alone.

I can find all NULLs, .Clear etc. in my source and handle them. But I can't
test the gazillion of SQL statements. What if the user has some edits to
define search parameters, and one of them is empty? LIKE would behave very
strange. But you know all the situations where that thing could bite you in
the lower back. No problem to consider that in a new project. But in 100.000
lines of existing code? No way! The result would be something that does not
show the expected results, which the user might not see immediately. No
madExcept protocoll to fix it, just a little bit less data, wrong reports
etc. The users would hang me.

So, my question: is there anything I can do with table and field definitions
to circumvent that? I don't want to use server side triggers because a)
until now, I always deploy vanilla db servers, which is _very_ handy, no
"who gets what damn server" etc, and b) some applications work in local and
c/s mode. But if triggers are the only solution, so be it.

What I need is a solution for dummies: do this and that, and you don't have
to worry about all the empty string and NULL problem. Tim, I am sorry to say
that, but without that, I will not convert a single application to EDB. I
bought EDB, and I will buy version 2. But _using_ it? Not in the near
future. With that empty string/NULL thing, it would be suicide. I am not the
only one complaining, maybe you should think again about a switch to change
the behaviour of EDB. Per database, per client, whatever. But for people
like me, there _must_ be an easier way than "try to find all places in the
code and hope that your costumer will not freak out".

Ralf

Hi Ralf,

Back in the day when this issue has come up first, I've suggested
a simple solution: declare ALL VARCHAR fields as NOT NULL!
You don't need nulls, so don't use them, period. Your code will
either work, or give errors (attempt to store NULL is not nullable
field). You won't encounter any nulls in selects and likes for
the very same reason. When importing data, convert all NULLs into
'' string.

Regards,
Michael





Ralf Mimoun wrote:
> Hi all,
>
> the emails from Elevate made me think again about converting existing
> projects from DBISAM 4 to EDB. There is no _real_ need because they work
> without a hitch, but sometime you have to make the step just to see new
> horizons.
>
> Changing the components is easy. Changing the events should not be that
> hard, too. But the one obstacle that let me finch from doing it is the
> NULL vs. empty string behaviour. And it seems that I am not alone.
>
> I can find all NULLs, .Clear etc. in my source and handle them. But I
> can't test the gazillion of SQL statements. What if the user has some
> edits to define search parameters, and one of them is empty? LIKE would
> behave very strange. But you know all the situations where that thing
> could bite you in the lower back. No problem to consider that in a new
> project. But in 100.000 lines of existing code? No way! The result would
> be something that does not show the expected results, which the user
> might not see immediately. No madExcept protocoll to fix it, just a
> little bit less data, wrong reports etc. The users would hang me.
>
> So, my question: is there anything I can do with table and field
> definitions to circumvent that? I don't want to use server side triggers
> because a) until now, I always deploy vanilla db servers, which is
> _very_ handy, no "who gets what damn server" etc, and b) some
> applications work in local and c/s mode. But if triggers are the only
> solution, so be it.
>
> What I need is a solution for dummies: do this and that, and you don't
> have to worry about all the empty string and NULL problem. Tim, I am
> sorry to say that, but without that, I will not convert a single
> application to EDB. I bought EDB, and I will buy version 2. But _using_
> it? Not in the near future. With that empty string/NULL thing, it would
> be suicide. I am not the only one complaining, maybe you should think
> again about a switch to change the behaviour of EDB. Per database, per
> client, whatever. But for people like me, there _must_ be an easier way
> than "try to find all places in the code and hope that your costumer
> will not freak out".
>
> Ralf

Michael

That's a simplistic way of viewing it. It may be the right way for a new application, and it may be what the morons who wrote the standard recommend but lets just see what it means in real life for an existing app.

1. Go through the database and set all string fields, in all records that are NULL to EmptyString
2. Go through the database and set all string fields to NOT NULL
3.a) Go through ALL .pas files and locate all .Clears
  b) For each occurence of .Clear
      i) check if its clearing a field
      ii) if it is clearing a field check if it's a string field being cleared
      iii) if above is true alter to .AsString := EmptyString
4.a) Locate ALL SQL statements with IS NULL or IS NOT NULL in them
     i) Identify if the IS NULL or IS NOT NULL relates to a string field
     ii) Alter all the tests to  = EmptyString or <> EmptyString
  b) Locate ALL SQL statements with SET and NULL in them
     i) If the NULL is being SET
     ii) if the field being set is a string field
     iii) alter NULL to EmptyString
5. Now try compiling and go back through and correct the number of quotes to get it right.



Roy Lambert

Ralf,

<< What I need is a solution for dummies: do this and that, and you don't
have to worry about all the empty string and NULL problem. Tim, I am sorry
to say that, but without that, I will not convert a single application to
EDB. >>

I'm going to try and resolve this with a TEDBEngine property in the next
couple of weeks.  I can't make any promises as to whether it will be
technically possible or not, however.  There are a lot of places where
assignments occur, and it might not be possible to tie them all back to a
single property in the engine.  The main problem is that EDB was not
designed with anything in mind other than the SQL standard for this
behavior.

--
Tim Young
Elevate Software
www.elevatesoft.com

Michael


And another step

Locate all filter statements with IS NULL or IS NOT NULL and adjust if its a string field

Roy Lambert

Roy,

> 1. Go through the database and set all string fields, in all records that are NULL to EmptyString
Can be easily embedded into database import procedure.

> 2. Go through the database and set all string fields to NOT NULL
No big deal, really. Not to sound as self promotion, but in our Database
designer it's a few clicks job.

> 3.a) Go through ALL .pas files and locate all .Clears
>    b) For each occurence of .Clear
>        i) check if its clearing a field
>        ii) if it is clearing a field check if it's a string field being cleared
>        iii) if above is true alter to .AsString := EmptyString
Not necessarily. You could subclass TEDBTable/Query and, before posting, set
all Required String fields if null to ''. This can be done in one place. Or
you can press Tim into adding this really simple feature IMO, it's a great
practice to use subclassed components, instead of standard ones. This will
allow you to handle tons of things in one place.

> 4.a) Locate ALL SQL statements with IS NULL or IS NOT NULL in them
>       i) Identify if the IS NULL or IS NOT NULL relates to a string field
>       ii) Alter all the tests to  = EmptyString or <> EmptyString
Yes, this could be a lot of work. Although, my impression was, that NOT NULL
problem was that = '' evaluated to TRUE if a field is null. IS NULL never
evaluated to true if a field is ''. So you should really not have too many
of these comparisons, or it just mean that your SQL was wrong so it makes sense
to revise it anyway.

>    b) Locate ALL SQL statements with SET and NULL in them
>       i) If the NULL is being SET
>       ii) if the field being set is a string field
>       iii) alter NULL to EmptyString
>       Locate all filter statements with IS NULL or IS NOT NULL and adjust if
>       its a string field
Certainly, that could be a lot of work, although, few people use UPDATE
statements with DBISAM - usually we are talking about table-based operations. It
would be great to extract all SQL from your application into a single file and
then go through it and check it all. This would be a nice tool to add to
DBISAM/Elevate DB toolset, btw. Could work similar to localization tools.

> 5. Now try compiling and go back through and correct the number of quotes to get it right.
Yes, I'm not saying that porting DBISAM to EDB is easy, however, NULL issue, IMO
is one of the issues that are fairly streight and if done right will improve the
consistency of your database structure.


Regards,
Michael

Michael


>> 1. Go through the database and set all string fields, in all records that are NULL to EmptyString
>Can be easily embedded into database import procedure.

Agreed, after many errors I came up with a script in ElevateDB to do it.

>> 2. Go through the database and set all string fields to NOT NULL
>No big deal, really. Not to sound as self promotion, but in our Database
>designer it's a few clicks job.

Same script as above.

Nowt wrong with a bit of advertising but I prefer not to have to buy someone else's tools to use a tool I bought

>> 3.a) Go through ALL .pas files and locate all .Clears
>> b) For each occurence of .Clear
>> i) check if its clearing a field
>> ii) if it is clearing a field check if it's a string field being cleared
>> iii) if above is true alter to .AsString := EmptyString
>Not necessarily. You could subclass TEDBTable/Query and, before posting, set
>all Required String fields if null to ''. This can be done in one place. Or
>you can press Tim into adding this really simple feature IMO, it's a great
>practice to use subclassed components, instead of standard ones. This will
>allow you to handle tons of things in one place.

You could, but unless you do a real good job you'll lose the benefits of things like preparing statements.

>> 4.a) Locate ALL SQL statements with IS NULL or IS NOT NULL in them
>> i) Identify if the IS NULL or IS NOT NULL relates to a string field
>> ii) Alter all the tests to = EmptyString or <> EmptyString
>Yes, this could be a lot of work. Although, my impression was, that NOT NULL
>problem was that = '' evaluated to TRUE if a field is null. IS NULL never
>evaluated to true if a field is ''. So you should really not have too many
>of these comparisons, or it just mean that your SQL was wrong so it makes sense
>to revise it anyway.

Wrong how?

>> b) Locate ALL SQL statements with SET and NULL in them
>> i) If the NULL is being SET
>> ii) if the field being set is a string field
>> iii) alter NULL to EmptyString
> > Locate all filter statements with IS NULL or IS NOT NULL and adjust if
> > its a string field
>Certainly, that could be a lot of work, although, few people use UPDATE
>statements with DBISAM - usually we are talking about table-based operations. It
>would be great to extract all SQL from your application into a single file and
>then go through it and check it all. This would be a nice tool to add to
>DBISAM/Elevate DB toolset, btw. Could work similar to localization tools.

I do some, and I'm starting to use more.

Extracting all the sql from an app is a wonderful pipedream. I, and I'm sure many others, store sql in 1) a query component, 2) in a TStringList (or container like that) 3) dynamically build it from various input.

>> 5. Now try compiling and go back through and correct the number of quotes to get it right.
>Yes, I'm not saying that porting DBISAM to EDB is easy, however, NULL issue, IMO
>is one of the issues that are fairly streight and if done right will improve the
>consistency of your database structure.

How will it improve the consistency of the database structure?


Roy Lambert

Roy,

> Nowt wrong with a bit of advertising but I prefer not to have to buy someone
> else's tools to use a tool I bought
Not that I disagree, however, the set of tools required to perform a task
called a toolset

>>> 3.a) Go through ALL .pas files and locate all .Clears
>>> b) For each occurence of .Clear
>>> i) check if its clearing a field
>>> ii) if it is clearing a field check if it's a string field being cleared
>>> iii) if above is true alter to .AsString := EmptyString
>> Not necessarily. You could subclass TEDBTable/Query and, before posting, set
>> all Required String fields if null to ''. This can be done in one place. Or
>> you can press Tim into adding this really simple feature IMO, it's a great
>> practice to use subclassed components, instead of standard ones. This will
>> allow you to handle tons of things in one place.
>
> You could, but unless you do a real good job you'll lose the benefits of things like
> preparing statements.
I don't see how you loose anything here, especially in regards to preparing.
You only handle standard BeforePost event and correct field values, that's all.

>>> 4.a) Locate ALL SQL statements with IS NULL or IS NOT NULL in them
>>> i) Identify if the IS NULL or IS NOT NULL relates to a string field
>>> ii) Alter all the tests to = EmptyString or <> EmptyString
>> Yes, this could be a lot of work. Although, my impression was, that NOT NULL
>> problem was that = '' evaluated to TRUE if a field is null. IS NULL never
>> evaluated to true if a field is ''. So you should really not have too many
>> of these comparisons, or it just mean that your SQL was wrong so it makes sense
>> to revise it anyway.
> Wrong how?
My bad, I was wrong. DBISAM actually treats = '' and is null and generally ''
and null for string fields as equivalent. I though it's only for comparison,
but it's also for assignment. You can't distinguish '' from null fields in
DBISAM. I didn't realize that, which is why I thought that there's
inconsistency. In effect, all strings in dbisam are NOT NULL already, because
you can't distinguish between the null and empty, so you may as well think
that they are not null.

>>> 5. Now try compiling and go back through and correct the number of quotes to get it right.
>> Yes, I'm not saying that porting DBISAM to EDB is easy, however, NULL issue, IMO
>> is one of the issues that are fairly streight and if done right will improve the
>> consistency of your database structure.
>
> How will it improve the consistency of the database structure?
By removing the ambiguity between ='' and is null. Sometimes you do need to
distinguish between them (in case of foreign keys, for example). However, I
might be wrong here. I'm looking at it from my perspective, because I got use to
it and cannot imagine that a database would consider ='' and is null equivalent,
so it might be just me

Here's one additional thought. Unlike VARCHAR and CHAR, MEMO fields do have
null values in dbisam and it is different from ''. This is where you might
get inconsistency.

Regards,
Michael

Michael


<<By removing the ambiguity between ='' and is null. Sometimes you do need to
>distinguish between them (in case of foreign keys, for example).>>

Here's where I really disagree. As far as I'm concerned (and I suspect a lot of people) there is no difference between null and emptystring.

I don't know wether its part of the standard or something I read but there was a comment along the lines of "its the duty of the presentation layer to make sure the data is presented properly". What a cop out!

I asked a while ago about real life examples where null and emptystring shouldn't be the same. I received mainly hypothetical cases and one real one which I felt would have been better handled another way.

<<Here's one additional thought. Unlike VARCHAR and CHAR, MEMO fields do have
null values in dbisam and it is different from ''. This is where you might
get inconsistency.>>

Never tried that so I can't say.

Roy Lambert

Roy Lambert wrote:

>
> Here's where I really disagree. As far as I'm concerned (and I
> suspect a lot of people) there is no difference between null and
> emptystring.
>

I grant your suspicion is valid .. but there is a difference.  I can't
say it better than Codd whose 3rd rule is pretty concise even before my
butchery:

"Null values ... are ... for representing missing information and
inapplicable information in a systematic way, independent of data type."

Now if you don't need a systematic way to represent or identify missing
or inapplicable info, then you don't need them but someone else
probably will. <shrug>

Personally, I use nulls to indicate that info that will be required
later, is not yet available or was not provided.  Then I enable/disable
subsequent operations, such as reporting, based on their
presence/absence.

Malcolm
--