Results 1 to 10 of 10

Thread: CLOB and NULL

  1. #1
    Join Date
    Feb 2002
    Location
    Minneapolis, MN
    Posts
    253

    Unanswered: 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..."

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    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.

  3. #3
    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....
    Last edited by ansonee; 09-16-04 at 15:37.
    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..."

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    How do you know if
    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.

  5. #5
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    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

  6. #6
    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..."

  7. #7
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    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.

  8. #8
    Join Date
    Jun 2002
    Location
    UK
    Posts
    525
    So the clob is populated with the string "NULL", no?

  9. #9
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    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...
    Attached Thumbnails Attached Thumbnails sql.jpg  
    Last edited by n_i; 09-17-04 at 10:56.

  10. #10
    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..."

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •