Board index » delphi » Not null column contains null

Not null column contains null

    Hi all,
    could anyone picture a situation where a NOT NULL column contains a NULL
value (that's what we have)?. Could it be a corrupted DB?

    TIA,
    Alphonsus.

 

Re:Not null column contains null


Quote
>     could anyone picture a situation where a NOT NULL column contains a
NULL
> value (that's what we have)?. Could it be a corrupted DB?

What makes you think it has a NULL in it?
What client tool are you using to determine that?

Have you done a test to isolate out any client using a statement such as:

SELECT COUNT( * )
FROM TABLE
WHERE COLUMN IS NULL

to see if there was anything there?

If you are using the API directly one thing you need to be aware of is how
the SQLVAR buffers are populated by the API. If a column is a NOT NULL
column you have to massage the SQLInd null indicator since it simply doesn't
touch it upon calling isc_dsql_fetch. Which could appear to make a column
NULL if you are not aware of this.

Perhaps you are using an ODBC driver that didn't properly take this into
account.

HTH,
Jason Wharton
CPS - Mesa AZ
http://www.ibobjects.com

Re:Not null column contains null


Quote
Alphonsus wrote:

>     Hi all,
>     could anyone picture a situation where a NOT NULL column contains a NULL
> value (that's what we have)?.

  Hi, Alphonsus.

  Three types of situation:

1. Column with not null declaration was added to table with data and
was'nt properly filled. Database is'nt corrupted but unrestorable.
Decision: find and fill.
2. Using some advanced tool or manually system tables were changed to
add Not Null flag to existing column containing nulls. Decision like 1.
3. Corrupted database. Usually such a rows in corrupted table contains
all nulls or (after gfix -v, gfix -m) 0-s, empty chars, dates from 19
century etc. Decision: find and delete, then backup/restore database.
Never make any recovery actions not having copy of original database and
restore into another file.

Best regards.

Re:Not null column contains null


Quote
> What makes you think it has a NULL in it?
> What client tool are you using to determine that?

> Have you done a test to isolate out any client using a statement such as:

> SELECT COUNT( * )
> FROM TABLE
> WHERE COLUMN IS NULL

    Thanks for the answer Jason.
    Client is sending over the database so we can take a closer look. I'll
get back as soon as it arrives.

    Best Regards,
    Alphonsus.

Other Threads