Extracting audio files from the database

[WB1980]

Is there some way to extract multiple audio files from the database directly? There was some bad weather in my area yesterday and I would like to keep a copy of all the transmissions but there are over 2,000 of them and doing it one at a time through the web client would be too much of a pain.

[mivok]

If you're using sqlite as your database, you can use the writeFile function to write out the files from the rdioScannerCalls table in the database, such as by running the following on the command line:

sqlite3 rdio-database.db "select writeFile('call_' || talkgroup || '_' || datetime || audioname, audio) from rdioScannerCalls limit 1;"

Change the limit 1 to something like where talkgroup = '1234' and datetime > '2023-10-03 00:00:00' to filter out which calls you want to write out. The first parameter in the writeFile command controls the filename, so you can change it as needed if the format isn't to your liking (it prints the date with spaces in each filename).

[WB1980]

Thanks for replying. I've never used SQLite before, so I downloaded the precompiled command line tools from sqlite.org. When I first tried running the command you suggested it gave an error because the name of the database file I have is rdio-scanner.db. After changing it to that it appears to accept the command, but nothing happens. If you have any further suggestions, I'd be glad to hear them.

Thanks again for your help.

[mivok]

Oops, I typed the wrong filename for the database. rdio-scanner.db is the correct one.

As for nothing happening. When I run the command above, it prints out a number (or several numbers if I tell it to dump more than once call), which is the size of the file written in bytes, and I end up with an additional file in the current directory:

❯ ls
rdio-scanner.db
❯ sqlite3 rdio-scanner.db "select writeFile('call_' || talkgroup || '_' || datetime || audioname, audio) from rdioScannerCalls limit 1;"
7626
❯ ls
'call_9421_2023-09-27 00:15:38 +0000 UTC.m4a'   rdio-scanner.db

Try running sqlite without any commands and exploring the database:

❯ sqlite3 rdio-scanner.db
SQLite version 3.43.1 2023-09-11 12:01:27
Enter ".help" for usage hints.
sqlite> select * from rdioScannerCalls limit 1;
934019||.m4a|audio/mp4|2023-09-27 00:15:38 +0000 UTC|[]|859462500|[]|0|[]|389|9421

I'm betting the issue is somewhere in the first parameter to writefile that generates a filename to be written. I did try to format the date using strftime and wasn't able to get it to work immediately. When it broke I got the same issue as you - no output. Maybe try manually specifying a filename with something like writeFile("someFile.m4a", audio) in the command instead of generating the filename automatically. If that works, you know the problem is somewhere in how the filename is generated and you can try something else. I originally named the files based on talkgroup and date, so they were unique, but you can choose any field you want, as long as you don't end up with duplicates.

[WB1980]

When I specify a filename it saves the file and is playable. I tried removing datetime from the parameters and that also works. So the datetime seems to be the culprit. Is there some other way to generate a unique filename? Thanks again for your help!