I'd like to know how to set a field value to NULL in a live query-result
using databound controls like TDBEdit.
Have I to use a trigger to set all fields of the record to null that
contain empty strings after the record has been updated?

Thanks Uli

Uli,

> I'd like to know how to set a field value to NULL in a live query-result
> using databound controls like TDBEdit.

You can't. After a value has been assigned to a varchar column, the only
I know to make it null again is using the TField.Clear method.

> Have I to use a trigger to set all fields of the record to null that
> contain empty strings after the record has been updated?

You can use a trigger or you can define the column as DEFAULT '' with or
without NOT NULL, but just out of curiosity, why do you want to convert
all empty strings to Null?

--
Fernando Dias
[Team Elevate]

Uli,

I just realized that the second part (about default values and not null)
  of my answer doesn't make sense! Please ignore it. It should be:

You can use a trigger, but just out of curiosity, why do you want to
convert all empty strings to Null?

--
Fernando Dias
[Team Elevate]

Fernando,

> You can use a trigger, but just out of curiosity, why do you want to
> convert all empty strings to Null?

Thank you for helping.
The reason is the coalesce function. I use in in quite a lot of cases
and it works with null-values. As I described in my last thread, I
altered the function in order to work with empty strings, but it doesn't
make sense to change the sources with each upcoming release.

Regards Uli

Uli,

> The reason is the coalesce function. I use in in quite a lot of cases
> and it works with null-values. As I described in my last thread, I
> altered the function in order to work with empty strings, but it doesn't
> make sense to change the sources with each upcoming release.

COALESCE is not an EDB extension but a standard SQL function and it is
doing exactly what it's supposed to do, so it really doesn't make much
sense to change it's behavior.

One solution would be to leave COALESCE and your data as they are now
and use NULLIF with COALESCE for varchar columns. For example:

   COALESCE( NULLIF(Name,''), '*No name*')

Returns '*No data*' either column "Name" is null or empty.

--
Fernando Dias
[Team Elevate]

Correction:
  COALESCE( NULLIF(Name,''), '*No data*')
  Returns '*No data*' either column "Name" is null or empty.

I really need to sleep
--
Fernando Dias
[Team Elevate]

Fernando,

> COALESCE is not an EDB extension but a standard SQL function and it is
> doing exactly what it's supposed to do, so it really doesn't make much
> sense to change it's behavior.

I agree.

> COALESCE( NULLIF(Name,''), '*No name*')
> Returns '*No data*' either column "Name" is null or empty.

Great! That's a really good solution. Many thanks.

Regards Uli