By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,586 Members | 2,418 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,586 IT Pros & Developers. It's quick & easy.

'' and NULL confusion

P: n/a
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

Mar 28 '07 #1
Share this Question
Share on Google+
6 Replies


P: n/a
that's an empty string...... not null...

regards,
dotyet

On Mar 28, 2:15 pm, "db2admin" <jag...@gmail.comwrote:
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

Mar 28 '07 #2

P: n/a
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
Mar 28 '07 #3

P: n/a
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

Mar 28 '07 #4

P: n/a
On Mar 28, 2:53 pm, Lennart <erik.lennart.jons...@gmail.comwrote:
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

Mar 28 '07 #5

P: n/a
oh that's OK... we all do... sometimes :)

regards,
dotyet

On Mar 28, 4:50 pm, "db2admin" <jag...@gmail.comwrote:
On Mar 28, 2:53 pm, Lennart <erik.lennart.jons...@gmail.comwrote:
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

Mar 29 '07 #6

P: n/a
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
Mar 29 '07 #7

This discussion thread is closed

Replies have been disabled for this discussion.