Subject Re: [firebird-support] Expecting "" value back, receiving System.DBNULL
Author Alexandre Benson Smith
David Wessell wrote:
> Hi,
> I'm writing a SELECT query, and sometimes the results of one of the
> fields that is queried is empty.. I was expecting a empty string value
> to be returned (Writing in Visual Basic, using ODBC BTW)..
> However, the result returned when that field is empty is
> System.DBNULL.. Can someone point me in the direction as to why I
> would receive this?
> Other fields in the query that are empty are returning empty strings,
> it's just this one field..
> Thanks
> David

Perhaps I am saying something obvious to you, but if I understood your
question correctly you are making a basic mistake, null and empty ('')
are different beasts. Null means an unknown/undefined value. Null is a
state not a value !

If you don't with nulls in that field, define it as NOT NULL on table
creation and FB will not allow it to be null.

To fix the already stored data, do something like

update MyTable set MyStringField = Coalesce(MyStringField, ''),
MyIntegerField = Coalesce(MyIntegerField, 0);

see you !

Alexandre Benson Smith
THOR Software e Comercial Ltda
Santo Andre - Sao Paulo - Brazil