Greetings Nigel,

I do not have access to PHP. Is there anyway to handle this in Database Workbench?

Thanks,
Mike

----- Original Message -----
From: Nigel Weeks
To: firebird-support@yahoogroups.com
Sent: Wednesday, April 01, 2009 11:07 PM
Subject: {Disarmed} Re: {Disarmed} Re: [firebird-support] How can I find a record in a table that has a null value where the field does not allow a null value?


Here's one I whipped up in PHP
------------------
#!/usr/local/bin/php

<?php

$conn = ibase_connect("localhost:/raid/db/passport.fdb","sysdba","masterkey");

// Obtain a list of non-system tables, and their field's makeup
$fieldsql = "select
a.rdb\$relation_name AS RN,
a.rdb\$field_name AS FN,
a.rdb\$null_flag AS NULLFLAG,
c.rdb\$type_name AS FTYPE,
b.rdb\$field_length AS FLEN,
b.rdb\$field_scale,
a.rdb\$default_source
from
rdb\$relation_fields a
JOIN rdb\$fields B
ON A.rdb\$field_source = B.rdb\$field_name
JOIN rdb\$types C
ON B.rdb\$field_type = C.rdb\$type
where
c.rdb\$field_name = 'RDB\$FIELD_TYPE'
AND A.rdb\$relation_name NOT LIKE 'RDB\$%'
AND a.rdb\$null_flag = 1";

$fieldrec = ibase_query($fieldsql) or die("bad field query");
while($fieldobj = ibase_fetch_object($fieldrec)){
$fieldobj->RN = trim($fieldobj->RN);
$fieldobj->FN = trim($fieldobj->FN);
$fieldobj->NULLFLAG = trim($fieldobj->NULLFLAG);

//echo $fieldobj->RN.".".$fieldobj->FN.": ".$fieldobj->NULLFLAG."\n";
if($fieldobj->NULLFLAG != ""){
// Inspect the field, and count any records that have nulls
$nsql = "select sum(1) from ".$fieldobj->RN." where ".$fieldobj->FN." is null";
$nrec = ibase_query($nsql);
$nrow = ibase_fetch_row($nrec);
if($nrow[0] > 0){
echo "Illegal Nulls found in ".$fieldobj->RN.".".$fieldobj->FN.":
".$nrow[0]."\n";
}
} // End of null counter

} // End of table while loop

?>

---------------------

And it returned a bug (I added a column with a not null definition onto a table
with 4 records in it)

nweeks@server0001:~> ./nullscan.php

Illegal Nulls found in TBL_CONFIG.STR_TEST: 4

Let me know if you want it run over a database, in case you don't have access to
PHP

Nige.

--
Nigel Weeks

Prism9 Technology
e: nweeks@...
m: 0408 133 738
w:http://prism9.com

fb:http://facebook.com/profile.php?id=604592926




[Non-text portions of this message have been removed]