Subject 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?
Author Nigel Weeks
http://rack2.dnsalias.net/resources/

Inside is a few files. The php file has the connection specific details in it.
Just run the bat file to launch it.

Nige.


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


--
Nigel Weeks

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

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