Remove spurious entries in BS Reporter

[diddy]

Due to some heavy handed clearing yesterday I have lost the instuctions to delete spurious entries in BS Reporter using  SQLite Database Browser 2.0.

It was something like,

<----->
if Registration is "" or Null then Delete

Any help appreciated

[Anmer]

All my SQL commands are at home on the desktop PC.  Sorry.

I'm sure someone else will know the answer.

[jakems]

Hi

Try:

delete from aircraft where registration is null or registration = '';

note that it is two single quotes without a space just before the ;. Usual rules apply, stop Basestation and make a copy of your database before trying it.

[diddy]

That works, thank you.   

[diddy]

I have now found the original post on Kinetic forum

Thanks to Keith D and SabreJV


Re: Strange Hex codes

Postby Keith D » December 29th, 2011, 9:55 am

Hi

this will remove all your unkown hexcodes from your SQB file inc these you are concerned about

firstly get this one

http://sourceforge.net/projects/sqlitebrowser/

install it :-

then make a back up of your current sqb file !!!!!!!!
then open sqlite browser then open your sqb file you will need to guide it to its location

assuming all ok so far select the tab :- execute SQL

a new window will open :-

you will then need to copy and paste this command exactly as it is into the top window using CTRL C and then CTRL V

DELETE FROM Aircraft
WHERE Registration IS NULL OR LENGTH(Registration) <1

then below click on Execute Query

hopefully in the small window below you will see :- no error

now be careful firstly you need to save your sqb file by clicking on the disc icon 3rd from left
then you need to close your SQB file

drop down file and select close dbase

all should now be done

restart basestation and everything should be working ok

if you need to check its been done :-

open gas populate then select :-

a] populate tab
b] tick in unknowns
c] populate tab below end code

you should get amessage in message window to the effect no records have been processed

hope its all ok

must repeat make a back up of your SQB file first !!!!!!

cheers

K/

Keith D
     
    Posts: 2241
    Joined: September 12th, 2005, 7:17 am

Top
Re: Strange Hex codes

Postby SabreJV » December 29th, 2011, 10:23 am

Keith's code is really removing any aircraft record that has a null or blank in the registration field so I'm not sure that is what you want and the records you mentioned must have no registrations.

To remove only the aircraft records where the hexcode has a lowercase character within the ModeS field I suggest using this SQL first...

SELECT * from Aircraft
WHERE UPPER(ModeS) <> ModeS;

This should bring back a list of 'hexcodes' in your sqb that have lowercase letters in them, then if that list looks ok, then to delete use this SQL...

DELETE from Aircraft
WHERE UPPER(ModeS) <> ModeS;

As was noted by Keith please make a backup copy of your Basestation.sqb file first before trying any SQL command against the database.

regards
Sabrejv

PS The SQL tool that I use is SQLiteSPY and can be found here..
http://www.yunqa.de/delphi/doku.php/pro ... espy/index