| |
|
If this is your first visit, be sure to check out the FAQ by clicking the link above.
You may have to register before you can post: click the register link above to proceed.
To start viewing messages, select the forum that you want to visit from the selection below.
|
 |
09-16-04, 11:32
|
|
Registered User
|
|
Join Date: Feb 2002
Location: Minneapolis, MN
Posts: 253
|
|
|
CLOB and NULL
|
|
Wondering if someone could shed some light on something I've just discovered:
I have a table that has two CLOB columns. In running a query(SELECT <clob_column> FROM TABLE), I saw that in one row in the table, the CLOB column was NULL.
I ran another query specifically targeting that row and saw NULL in the CLOB column. Also did a SELECT LENGTH(CLOB_COLUMN) FROM TABLE WHERE..... and got a length of zero.
When I ran a SELCT * FROM TABLE WHERE CLOB COLUMN IS NULL, I get nothing back!!
How is it that a column can be both NULL, have a length of zero, but yet when querying WHERE...IS NULL, nothing comes back?
Is there a special syntax when using NULL and CLOB columns?
Thanks!
__________________
Anthony Robinson
"If I'm curt with you, it's because time is a factor here. I think fast, I talk fast, and I need you guys to act fast if you want to get out of this. So, pretty please - with sugar on top..."
|
|
09-16-04, 14:09
|
|
:-)
|
|
Join Date: Jun 2003
Location: Toronto, Canada
Posts: 4,449
|
|
Quote:
|
Originally Posted by ansonee
How is it that a column can be both NULL, have a length of zero, but yet when querying WHERE...IS NULL, nothing comes back?
|
You're probably confusing a null value and an empty string; they are not the same. The fact that length(clob_column) equals 0 means that the value in that column is not null; otherwise length(clob_column) would equal NULL as well.
|
|
09-16-04, 14:34
|
|
Registered User
|
|
Join Date: Feb 2002
Location: Minneapolis, MN
Posts: 253
|
|
|
|
Why is it then when I select from that table, NULL is being returned, and not just empty spaces or "empty" string? If it's an empty string, shouldn't it show nothing in the result set (blank)?
No big deal - I can adapt my code for this but it's seems kind of odd
And how does that happen? I've gone through the data and have found that sometimes when there is NULL in for the CLOB column, the length of that column is zero. The majority of the time when there is NULL in for the CLOB column, the length is also NULL.
I don't get it....
__________________
Anthony Robinson
"If I'm curt with you, it's because time is a factor here. I think fast, I talk fast, and I need you guys to act fast if you want to get out of this. So, pretty please - with sugar on top..."
|
Last edited by ansonee; 09-16-04 at 14:37.
|
09-16-04, 16:56
|
|
:-)
|
|
Join Date: Jun 2003
Location: Toronto, Canada
Posts: 4,449
|
|
How do you know if
Quote:
|
NULL is being returned, and not just empty spaces or "empty" string
|
? Where do you "see" it?
I think the only reliable way to know if something is null is by using the "smth IS NULL" clause.
|
|
09-16-04, 17:29
|
|
Registered User
|
|
Join Date: May 2003
Location: USA
Posts: 5,196
|
|
When using static SQL, one can look at the null indicator variables. I am not sure if there is an equivalent in dynamic SQL, maybe in SQLCA or SQLDA.
__________________
M. A. Feldman
IBM Certified DBA on DB2 for Linux, UNIX, and Windows
IBM Certified DBA on DB2 for z/OS and OS/390
|
|
09-16-04, 17:53
|
|
Registered User
|
|
Join Date: Feb 2002
Location: Minneapolis, MN
Posts: 253
|
|
If I do a SELECT COLUMN_NAME FROM TABLE_NAME, a NULL is displayed...
But I'm just going to go with LENGTH() > 0 to work around it...
__________________
Anthony Robinson
"If I'm curt with you, it's because time is a factor here. I think fast, I talk fast, and I need you guys to act fast if you want to get out of this. So, pretty please - with sugar on top..."
|
|
09-16-04, 18:09
|
|
:-)
|
|
Join Date: Jun 2003
Location: Toronto, Canada
Posts: 4,449
|
|
Quote:
|
Originally Posted by ansonee
If I do a SELECT COLUMN_NAME FROM TABLE_NAME, a NULL is displayed...
|
So you're saying that the DB2 CLP displays the string "NULL" in the CLOB column where in fact there is an empty string? What platform are you on? I should avoid using CLP :-) if it displays the query results incorrectly...
However, here is a test case that shows that CLP on Windows (DB2 v8.1) works correctly...
Code:
>db2 create table test (key int, val clob(10))
DB20000I The SQL command completed successfully.
>db2 insert into test (key, val) values (1, 'abcdef')
DB20000I The SQL command completed successfully.
>db2 insert into test (key, val) values (2, '')
DB20000I The SQL command completed successfully.
>db2 insert into test (key) values (3)
DB20000I The SQL command completed successfully.
>db2 select key, val, length(val) from test
KEY VAL 3
----------- ---------- -----------
1 abcdef 6
2 0
3 - -
3 record(s) selected.
>db2 select key, val, length(val) from test where val is null
KEY VAL 3
----------- ---------- -----------
3 - -
1 record(s) selected.
>db2 select key, val, length(val) from test where val is not null
KEY VAL 3
----------- ---------- -----------
1 abcdef 6
2 0
2 record(s) selected.
|
|
09-17-04, 05:49
|
|
Padawan
|
|
Join Date: Jun 2002
Location: UK
Posts: 525
|
|
So the clob is populated with the string "NULL", no?
|
|
09-17-04, 09:44
|
|
:-)
|
|
Join Date: Jun 2003
Location: Toronto, Canada
Posts: 4,449
|
|
I don't think so - Anthony says length(clob) is 0... I suspect he uses some kind of a GUI tool, not CLP to execute queries, and that tool confuses empty strings with NULLs.
See for example a screenshot below - using this tool (WinSQL) it's impossible to distinguish NULLs from empty strings...
|
Last edited by n_i; 09-17-04 at 09:56.
|
09-20-04, 12:50
|
|
Registered User
|
|
Join Date: Feb 2002
Location: Minneapolis, MN
Posts: 253
|
|
wow...thanks for all the help....
So when I want to chaeck for NULL's, I should actually check for an empty string?
What sucks is that they're passing in empty string to begin with. I've said it about a thousand times: send in NULL...
They just don't get it....
__________________
Anthony Robinson
"If I'm curt with you, it's because time is a factor here. I think fast, I talk fast, and I need you guys to act fast if you want to get out of this. So, pretty please - with sugar on top..."
|
|
| Thread Tools |
Search this Thread |
|
|
|
| Display Modes |
 Linear Mode
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
|
dBforums
>
CLOB and NULL
