SQLite - OK in Development but not in Standalone

[KimD]

I've got an app, which I want to run on Windows+Android+IOS, to which I want to pass a pre-populated SQLite db.

On PreOpenStack
...
If the environment = "development" then
Put specialFolderPath("desktop") & "/TSER_Docs" into ggDocumentsFolder
Put specialFolderPath("desktop") & "/TSER_Temp" into ggTemporaryFolder
Put specialFolderPath("desktop") & "/TSER_Reso" into ggResourcesFolder
Else
Put specialFolderPath("documents") into ggDocumentsFolder
Put specialFolderPath("temporary") into ggTemporaryFolder
Put specialFolderPath("resources") & "/TSER_Reso" into ggResourcesFolder
end if
put (ggDocumentsFolder & slash & "main.sqlite") into ssDatabaseFile
...


On OnInstallsetup
...
put URL("binfile:" & ggResourcesFolder & slash & "main.sqlite") into URL("binfile:" & ssDatabaseFile)
...

on dbSetup
put revOpenDatabase("sqlite",ssDatabaseFile, , , , ) into ssQueryResult
If ssQueryResult is a number then
Set the dbID of this stack to ssQueryResult
Else
Set the dbID of this stack to "Error"
Answer "Error - Unable to connect to the database"
End if
...

In my Development environment, on first time install
- I can main.sqlite in the resources folder
- I can see main.sqlite in the documents folder
- I can use the following script to see the 15 tables that I'm expecting within the db
put ("SELECT name FROM sqlite_master;") into ssSQLCode
put revDataFromQuery(tab, return, the dbID of this stack, ssSQLCode) into ssQueryResult

When I deploy this as a standalone (on either Windows or Android - I haven't yet tried IOS), on first time install
- I can main.sqlite in the resources folder
- I can see main.sqlite in the standalone's documents folder
- When I use the following script, I see ZERO tables within the db
put ("SELECT name FROM sqlite_master;") into ssSQLCode
put revDataFromQuery(tab, return, the dbID of this stack, ssSQLCode) into ssQueryResult

When I carry on my standalone will happily create, populate and query tables (using the dbID of this stack) as expected; it's just that its not seeing any of the data that was contained in the original ggResourcesFolder & slash & "main.sqlite".

Thanks in advance for any advice

Kim (LC9 on Windows 10)

[KimD]

I went for a long walk on the beach and worked it out.

To fix my missing SQLite db problem I had to:
- Go into Standalone App settings > Copy Files & remove the existing database file; then
- Go into Standalone App settings > Copy Files & add the database file back again.

This had the effect of changing the absolute file path that was in the Copy Files to a relative file path.

I suspect that LC had inserted the absolute file path when I was messing around with the defaultfolder a few days ago.

Anyway - Lesson learnt, spend less time on the internet and more time on the beach.