Page 1 of 1
Distribute sqlite database with Windows standalone
Posted: Wed Jul 28, 2010 3:40 pm
by matt
Hi, I've just built a stack which I planned to distribute along with as a Windows standalone application with a sqlite database. I can build it for the Mac platform, and also run the stack through the IDE with no problems.
The trouble is that the Windows standalone can't connect to the sqlite database. Initially I found that the sqlite database was not being copied into the folder alongside the exe, as I thought it would. I thought it might be an issue of having an absolute path in the database query builder, which I used in my program. My stack file is located in the same folder as the sqlite file, so I tried just deleting the path info from the database query builder options. Unfortunately it just reappears. The other thing I tried was going into the standalone settings and choosing copy files to copy the sqlite database file. I tried this with Copy Referenced Files both checked and unchecked. A sqlite file is copied, but it's only 4 kb in size and should be 5 mb.
Thanks for any suggestions. I tried doing a search of the forums and looking through the users guide. Aside from the issue of relative / absolute paths and the copy files options within standalone application settings, I didn't find a solution.
Re: Distribute sqlite database with Windows standalone
Posted: Wed Jul 28, 2010 4:05 pm
by bangkok
You should perhaps make a test using a direct connection, instead of using the DB query builder.
It could help you to "fine tune" your script and find the cause of the problem. Furthermore, a direct connection will give you more freedom.
Code: Select all
put revOpenDatabase("sqlite", "C:/yoursqlitefile.db", , , , ) into dbID
if dbID is not a number then
put "error "&dbid
exit to top
end if
put "SELECT * from yourtable" into dbSQL
put revDataFromQuery(,,dbID,dbSQL) into theData
answer theData
revCloseDatabase dbID
Re: Distribute sqlite database with Windows standalone
Posted: Wed Jul 28, 2010 8:47 pm
by matt
I'll give that a try. I had tried this before, but ran into problems and then found that the query builder met my needs (except when I try to distribute it as a standalone with the database file).
So, with the direct connection approach and revOpenDatabase, does the C:/ path information need to be included if the sqlite database is distributed in the same folder as the exe? Every bit of sample code I can find for sqlite on a local machine seems to have C:/, but again I wasn't successful using a direct connection before I switched to the query builder. Thanks again for your help.
Re: Distribute sqlite database with Windows standalone
Posted: Thu Jul 29, 2010 5:57 am
by Janschenkel
The SQLite database driver needs a full path to the .db file - it doesn't care which directory your stack is in. To convert a relative path to an absolute path, based on the directory where your stack resides in, add something like this to your stack script:
Code: Select all
function AbsolutePathFromStack pRelativePath
local tAbsolutePath
put the effective fileName of this stack into tAbsolutePath
set the itemDelimiter to slash
put pRelativePath into the last item of tAbsolutePath
return tAbsolutePath
end AbsolutePathFromStack
Assuming that your database file is named "smurf.db" and lives right next to your stack, use the function like this to get the absolute path:
Code: Select all
put AbsolutePathFromStack("smurf.db") into tDbFilePath
put revOpenDatabase("sqlite", tDbFilePath, , , , ) into tDbConnection
Your problem with standalones using the Query Builder is that it stores the full path to the SQLite database file in its connection information; so once you move the application to adifferent computer, and don't place it in exactly the same spot, it won't find the file. Look at
this topic for more information on how to dynamically alter the 'Host' database location for all automated queries.
HTH,
Jan Schenkel.
Re: Distribute sqlite database with Windows standalone
Posted: Fri Jul 30, 2010 6:06 pm
by matt
Your problem with standalones using the Query Builder is that it stores the full path to the SQLite database file in its connection information; so once you move the application to adifferent computer, and don't place it in exactly the same spot, it won't find the file.
Yes, that's exactly what is happening. I'm going to try reworking my application tonight as you suggested. Thank you!
Re: Distribute sqlite database with Windows standalone
Posted: Thu Aug 05, 2010 10:36 pm
by matt
Everything seems to be working now, but only if I manually copy the sqlite database folder into the package file on OSX or into the folder for the Windows standalone. Otherwise I find that RunRev just places a 4 kb file there instead of the full database. I checked Copy Files under Standalone Application Settings, and I've chosen the correct sqlite file and selected Copy Referenced Files. Is there something else that I'm missing?
Re: Distribute sqlite database with Windows standalone
Posted: Fri Aug 06, 2010 6:17 am
by Janschenkel
Hi Matt,
A few things come to mind:
- Don't assume that the user has write privileges in the directory where your stack resides, so don't put the database file there
- If all users of the computer need to access the same data, store it in a proper shared location
- If every user of the computer needs to access their own data, store it in a user specific location
- Also consider what needs to happen when you can't find the database file (upon first install, upon user switch)
Look at Ken Ray's excellent list of
specialFolderPath parameters:
http://www.sonsothunder.com/devres/revo ... ile010.htm
Jan Schenkel.