Hi, I'm a PhD student and have been attempting to use SQLite as a database in PAMGuard.

I have loaded an SQLite database module into PAMGuard, but when the analysis is finished, opeing the database in SQLite Studio shows no data (all fields are empty) even when I can see the detector modules are making detections. I have also attempted opening the SQLite database in PAMGuard Viewer after PAMGuard has finished running, but all of these fields are also empty.

Do any PAMGuard users have suggestions as to why I am not able to view the output in my SQLite database? Any and all insight would be greatly appreciated!

This sounds more like a PAMGuard issue than an SQLite one. If I had to throw a dart blindfolded, I'd guess it's not committing transactions. Maybe there's a Save button/option/whatever that you need to hit? You'll have better luck if you ask for PAMGuard support elsewhere.

Hi Barry, thank you for taking the time to respond my post.

Yes, to my understanding an “SQLite database module” is the same as an “SQLite database”, a ‘module’ is the terminology used in the PAMGuard user manual to describe an element added to the PAMGuard interface.

Yes, by “database” I am referring to a database intended to be accessed within PAMGuard. I added the database by following the instructions described in the PAMGuard help manual which directed that I designate SQLite as the chosen database system in the database selection menu. I then created an SQLite database file (.sqlite3). I need the database to store count data generated by PAMGuard when it analyses .wav audio files.

Yes, the expected tables are present, but they do not display the count data expected. I apologise for my lack of convention terminology; I am still attempting to learn the appropriate terminology as I familiarise myself with these programs for the first time. Yes, by ‘field’ I am referring to tuples in the table rows.

The detector module in PAMGuard is counting the number of whistles present in an audio file, and I am attempting to have the sum of that count data displayed in the SQLite database file. When the whistle is present in the audio file, I can visually see PAMGuard is detecting it with the real-time spectrogram, but that count data is not shown anywhere in the SQLite database file.

I believe your first assumption is correct, the effect of the PAMGuard run on the database is not as expected; the tables are generated but the count data is absent.

I’m confident “the output” is not an empty set as I can visually see that PAMGuard is detecting the whistles in the audio file correctly, and therefore I believe count data should be displayed in the SQLite database file.

Another piece of information that may help is that when I open the SQLite database file in SQLite Studio is receive the following error messages:

Could not restore window 'Whistle_and_Moan_Detector (TowST)', because database TowST could not be resolved. Could not restore window 'Whistle_and_Moan_Detector_Localised_Contours (TowST)', because database TowST could not be resolved. Could not restore window 'PamguardModules (TowST)', because database TowST could not be resolved. Could not restore window 'Sound_Acquisition (TowST)', because database TowST could not be resolved.

Does this shed any light on what my issue could be? That is, that the count data from the PAMGuard processing is absent from the SQLite database file despite visual confirmation that there should be count data present?

Thank you again for your time.

Please don't use SQLite Studio for debugging. We don't know how it works and we don't know what its error messages mean. Instead, please use the SQLite Command Line Shell:

https://sqlite.org/cli.html

Which you can download as part of the 'Precompiled Binaries' package from

https://sqlite.org/download.html

This program was written by the same team which wrote SQLite itself. If it does anything wrong, they know whether the problem is with that program or with SQLite itself.

Make sure your PAMGuard program – and everything else – doesn't have the SQLite database open. Then use the command line program to open the database and examine it using commands like .schema and .table and by just typing SQL commands. Does that program report that the database file contains what you expected ?

[lots of PAMGuard-specific moans] because database TowST could not be resolved. ...

Does this shed any light on what my issue could be?

It sheds the same light as your previous post which appeared to show that "the database" of which you spoke was not well defined. That is why I advised: you need to specify what, specifically, you wrote/did/clicked etc. to designate an actual database file for the SQLite library to open and operate upon.

A problem we often see in this forum is this: A user specifies a filename for the database file but does not know where in their filesystem(s) that file actually resides. Then they get confused because their program's effect(s) upon "the database¹" cannot be observed when they use another tool to examine "the database²", which happens because database¹ is a different byte sequence than database².

Did you specify an absolute path as "the database"? If not, your problem is highly likely to be the same above-described one we see here often. Until I see an answer to this question, I will assume you are suffering that same problem. The solution is to specify an absolute path to PAMGuard for its database, then specify the same absolute path to whatever other tool you use to observe PAMGuard's output data.