'' and NULL confusion

[db2admin]

hello,

db2 does not recognize '' as null?
say i have T1(C1 INT,C2 CHAR(4))
and i insert (1,'') , it would not be same as (1,NULL)
what db2 consider '' as? how does it store value '' as if not as NULL
because technically is it no data just empty string.

we have application which try to update some columns to ''.
same column is a foreign key from other table and it complains because
parent column has NULL and child column value '' does not equal NULL.
we can not change applications at this time and also can not remove
foreign key

please suggest if you have any ideas.

regards,
jagdip singh

[dot.yet]

that's an empty string...... not null...

regards,
dotyet

On Mar 28, 2:15 pm, "db2admin" <[e-mail]:

hello,

db2 does not recognize '' as null?
say i have T1(C1 INT,C2 CHAR(4))
and i insert (1,'') , it would not be same as (1,NULL)
what db2 consider '' as? how does it store value '' as if not as NULL
because technically is it no data just empty string.

we have application which try to update some columns to ''.
same column is a foreign key from other table and it complains because
parent column has NULL and child column value '' does not equal NULL.
we can not change applications at this time and also can not remove
foreign key

please suggest if you have any ideas.

regards,
jagdip singh

[Serge Rielau]

db2admin wrote:

db2 does not recognize '' as null?

Correct, DB2 does not recognize an apple as a banana.
It's a standard thing...

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[Lennart]

db2admin wrote:

hello,

db2 does not recognize '' as null?

If it did it would have been a bug

say i have T1(C1 INT,C2 CHAR(4))
and i insert (1,'') , it would not be same as (1,NULL)

'' is a value, null is not

what db2 consider '' as? how does it store value '' as if not as NULL
because technically is it no data just empty string.

How does it store 0 as if not null because technically is it no data
just zero?

we have application which try to update some columns to ''.
same column is a foreign key from other table and it complains because
parent column has NULL and child column value '' does not equal NULL.

FYI, your problem is not that there is null in the parent row (if it
where, that column could not have been part of p.k or unique constaint)

we can not change applications at this time and also can not remove
foreign key

please suggest if you have any ideas.

Guess you can implement before triggers that translates '' to null

create trigger Trig1
....
referencing new as n
before inert on T
set (col1, col2, ...,coln) = (nullif(n.col1, ''), nullif(n.col2, ''),
...., nullif(coln, ''))
/Lennart

[db2admin]

On Mar 28, 2:53 pm, Lennart <[e-mail]:

db2admin wrote:

hello,

db2 does not recognize '' as null?

If it did it would have been a bug

say i have T1(C1 INT,C2 CHAR(4))
and i insert (1,'') , it would not be same as (1,NULL)

'' is a value, null is not

what db2 consider '' as? how does it store value '' as if not as NULL
because technically is it no data just empty string.

How does it store 0 as if not null because technically is it no data
just zero?

we have application which try to update some columns to ''.
same column is a foreign key from other table and it complains because
parent column has NULL and child column value '' does not equal NULL.

FYI, your problem is not that there is null in the parent row (if it
where, that column could not have been part of p.k or unique constaint)

we can not change applications at this time and also can not remove
foreign key

please suggest if you have any ideas.

Guess you can implement before triggers that translates '' to null

create trigger Trig1
...
referencing new as n
before inert on T
set (col1, col2, ...,coln) = (nullif(n.col1, ''), nullif(n.col2, ''),
..., nullif(coln, ''))

/Lennart

thanks for help
i feel stupid

[dot.yet]

oh that's OK... we all do... sometimes :)

regards,
dotyet

On Mar 28, 4:50 pm, "db2admin" <[e-mail]:

On Mar 28, 2:53 pm, Lennart <[e-mail]:

db2admin wrote:

hello,

db2 does not recognize '' as null?

If it did it would have been a bug

say i have T1(C1 INT,C2 CHAR(4))
and i insert (1,'') , it would not be same as (1,NULL)

'' is a value, null is not

what db2 consider '' as? how does it store value '' as if not as NULL
because technically is it no data just empty string.

How does it store 0 as if not null because technically is it no data
just zero?

we have application which try to update some columns to ''.
same column is a foreign key from other table and it complains because
parent column has NULL and child column value '' does not equal NULL.

FYI, your problem is not that there is null in the parent row (if it
where, that column could not have been part of p.k or unique constaint)

we can not change applications at this time and also can not remove
foreign key

please suggest if you have any ideas.

Guess you can implement before triggers that translates '' to null

create trigger Trig1
...
referencing new as n
before inert on T
set (col1, col2, ...,coln) = (nullif(n.col1, ''), nullif(n.col2, ''),
..., nullif(coln, ''))

/Lennart

thanks for help
i feel stupid

[Knut Stolze]

db2admin wrote:

hello,

db2 does not recognize '' as null?

Of course not. '' is an empty string whereas NULL indicates the absence of
a string. You have the same string in Java, for example. A NULL pointer
is not the same as an empty string "".

say i have T1(C1 INT,C2 CHAR(4))
and i insert (1,'') , it would not be same as (1,NULL)
what db2 consider '' as? how does it store value '' as if not as NULL
because technically is it no data just empty string.

In this case, DB2 will store a string comprised of 4 blanks. You used
CHAR(4) and not VARCHAR(4), which implies that all strings are padded to
the right with blanks. Since you have an empty string, 4 padding blanks
are needed to reach the length of 4.

In case of VARCHAR(4), you would have a length of 0 for the value and no
characters in the string.

we have application which try to update some columns to ''.
same column is a foreign key from other table and it complains because
parent column has NULL and child column value '' does not equal NULL.

Naturally. NULL is not an empty string.

we can not change applications at this time and also can not remove
foreign key

You could add a BEFORE trigger to the dependent table which changes '' to
NULL.

CREATE TRIGGER ... BEFORE INSERT ON ...
REFERENCING NEW AS n
FOR EACH ROW
SET n.string_column = NULLIF(n.string_column, '')

--
Knut Stolze
DB2 z/OS Utilities Development
IBM Germany