Trouble Shooting with SQLite Database

Please start a new thread for each new topic. Topics may end up in the FAQ ...
User avatar
Administrator
Site Admin
Posts: 310
Joined: Mon Jul 21, 2014 5:48 am

Trouble Shooting with SQLite Database

Postby Administrator » Wed Feb 17, 2016 10:58 pm

Hi guys,
Gary had a problem with his SQLite database - so I'm starting this thread to handle trouble shooting for those using the SQLite database option.

Information on TransTRBO's Databases
Documentation for the TransTRBO Databases can be downloaded from here: http://www.transtrbo.com/files/MySQL_Creates.doc
Notes: http://www.transtrbo.com/forums/viewtopic.php?f=12&t=40

SQLite - Vn 3
SQLite is Freeware and open source and can be found at http://www.sqlite.org. It is compiled into the base application and thus it is "built in" to TransTRBO.

You can download their utilities from their website or from this link to a zip file that also contains my *.bat Files (I use them 'cos I'm lazy - you just have to replace my database file name with yours in the *.bat files. BTW: :idea: the good part is if you do that you can double click go.bat file in Explorer and it will automatically start the Command Line Window).
http://www.transtrbo.com/files/sqlite_utils.zip

Steps for Maintenance
  1. Determine where the existing Database is stored
    1. Start TransTRBO & log in
    2. Select Configure System
      Image
    3. Select Database Settings
      Image
    4. Press the database browse button
      (If you know how - you can select all of the File Path, copy it to clipboard , save it to Notepad and Exit from TransTRBO)
      Image
    5. Press the symbol pointed at by the Yellow indicator to see where the SQLite folder is and take a note of the path
      Image
  2. Take a note of the path and also the *.DB file which is the SQLite database file.
  3. Cancel out from here and Exit TransTRBO
  4. In Windows Explorer find the Database folder and make a backup copy of the database file
  5. Download the SQLite utility files
  6. Copy the sqlite3.exe file (unzip if required) to the Database Folder containing your database file (from above)
  7. Start a Command Line Window in the same Database Folder (google it - if need be)
  8. In the Command Line type: sqlite3 your-database-name
    (In my case I type the following)

    Code: Select all

    sqlite3 TT_038TMC4558.DB
  9. At the sqlite> prompt: type each line and press Enter (see screenie)

    Code: Select all

    drop table GpsRequestLineTable;
    drop table GpsRequestHeaderTable;

    Image
  10. Press Ctrl-C to exit
  11. Restart TransTRBO and try to create a Poll List again

Well hopefully that will give you a bit of an idea - most SQL command lines are very similar - all those "drop" commands really just delete those tables and they'll be recreated when TransTRBO starts again
cheers
Will
Geriatrics Generate Methane.
Grafitti Clifton Hill 1974
Living proof 2016

GaryL245
Posts: 37
Joined: Mon May 25, 2015 9:50 pm

Re: Trouble Shooting with SQLite Database

Postby GaryL245 » Thu Feb 18, 2016 1:49 pm

Will, I was able to download and open SQLite3 and go through the above procedure. Everything went as described above. re-opened TransTRBO and attempted to load a GPS table. When I saved it, I got the same error message as before. "looking for 13 fields, receive 11 fields". I'm using a Current version of (0.1.1.6) TransTRBO, Motorola 7550 radio, Wndows 7 laptop, cable connected radio. I'm able to read the user list and zone/channel list OK. (I did have to add the zone names into the list manually though..) This GPS list seems to be the last obvious hurtle (so far..) in getting TransTRBO running. -- Gary

User avatar
Administrator
Site Admin
Posts: 310
Joined: Mon Jul 21, 2014 5:48 am

Re: Trouble Shooting with SQLite Database

Postby Administrator » Thu Feb 18, 2016 8:10 pm

Hi Gary,

Please follow the procedure again (just from the "launch command line window").
After entering the 2 x "drop" statements ...
Enter them again.
Then grab a screen shot of the command line window and post it in here please.

cheers
Will
Geriatrics Generate Methane.
Grafitti Clifton Hill 1974
Living proof 2016

GaryL245
Posts: 37
Joined: Mon May 25, 2015 9:50 pm

Re: Trouble Shooting with SQLite Database

Postby GaryL245 » Thu Feb 18, 2016 8:32 pm

Will, sorry could not get a screen shot. Working on that. In both cases it came back and said the tables did not exist. It looks like when I deleted them the first time they got deleted (earlier this morning.), but when the program restarted (this morning) SQLite did not generate new tables. -- Gary

User avatar
Administrator
Site Admin
Posts: 310
Joined: Mon Jul 21, 2014 5:48 am

Re: Trouble Shooting with SQLite Database

Postby Administrator » Fri Feb 19, 2016 12:07 am

Hi Gary,

I've just tested it and I found that had a problem .. immediately after I had performed the 2 x "drop" command
  1. TT must not be running
  2. from command window running sqlite3
  3. performed the 2 x "drop" commands
  4. did them again (using up arrow) and they had "no such table" - which was expected
  5. left command line window and sqlite3 running
  6. restarted TT -> login -> connect -> location -> Update Lists = Empty
  7. Add new (poll list) name:junk + a couple of radios -> Save -> Exit
  8. disconnect -> exit TT
  9. in command line window -> sqlite3
  10. select * from gpsrequestheadertable;
    "Junk" appeared
  11. performed the 2 x "drop" commands
  12. did them again (using up arrow) and they had "no such table" - which was expected
  13. restarted TT -> login -> connect -> location -> Update Lists = Empty
  14. Add new (poll list) name:More Junk + a couple of radios -> Save -> Exit
  15. in command line window -> sqlite3
  16. select * from gpsrequestheadertable;
    "More Junk" appeared
  17. disconnect -> exit TT

In the command line window while sql3 is running typ:

Code: Select all

.tables

A list of tables will be shown
Image

Please send a screenie.

cheers
Will
Geriatrics Generate Methane.
Grafitti Clifton Hill 1974
Living proof 2016

GaryL245
Posts: 37
Joined: Mon May 25, 2015 9:50 pm

Re: Trouble Shooting with SQLite Database

Postby GaryL245 » Mon Feb 22, 2016 9:18 pm

Will, I got tired of messing with the database and deleted the whole thing. when I restarted TT it looks like it fixed the problem. I did notice that it did not copy the zone names to the zone/channel table. I also noticed that it does not load the user radio ID to the unknown user table. Is this normal? I have about 500 users in the radio and 44 zones with around 10 channels (talk groups) per zone. So far, other than that it seems to be operation correctly. I'll add more as I go through it.