HI Nigel,

The database is not available to the outside world and it runs on Windows Server 2003. If there is anything you could do let me know.

Thanks,
Mike

----- Original Message -----
From: Nigel Weeks
To: firebird-support@yahoogroups.com
Sent: Wednesday, April 01, 2009 11:24 PM
Subject: {Disarmed} Re: {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?


Hmm, not without extending it's operation via some scripting, which won't be easy.

If your database was accessible from the outside world, it could be scanned
quickly and easily.

If you're running on a windows platform, I could package enough PHP to enable
you to run it locally...

Nige.

> 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]
>
>

--
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]