Path to database

Creating desktop or client-server database solutions?

Moderators: Klaus, FourthWorld, heatherlaine, kevinmiller, robinmiller

redfield
Posts: 58
Joined: Thu Apr 04, 2019 1:41 pm

Path to database

Post by redfield » Mon Sep 23, 2019 8:21 pm

Hi guys,
I am creating a little app that uses an SQLite database (first time). It works fine in the LC environment but I don't understand how to define the path to the database so that it's found in the standalone. For developing purposes I put the database in the same folder as the LC Stack, so I just give the database name:

Code: Select all

put revOpenDatabase("sqlite","db_test",,,,,,)
In the standalone though I then get this error:
revdberr,Database Error: no such table: test_mail
Obviousely the database is not found and I have no idea which path to specify :? . I tried with:

Code: Select all

put item 1 to -2 of the longFilePath of me into thepath
put revOpenDatabase("sqlite","thepath/db_test",,,,,,)
This doesn't work either though.

bangkok
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 890
Joined: Fri Aug 15, 2008 7:15 am

Re: Path to database

Post by bangkok » Mon Sep 23, 2019 10:44 pm

Let's say the file will be located in "my documents".

Code: Select all

put specialFolderPath("documents") & "/db_test.sqlite" into tDatabasePath

put revOpenDatabase("sqlite", tDatabasePath, , , , ) into tID

Klaus
Posts: 11730
Joined: Sat Apr 08, 2006 8:41 am
Location: Germany
Contact:

Re: Path to database

Post by Klaus » Tue Sep 24, 2019 6:29 pm

Hi redfield,

if you add your database to your standalone via the "Copy files" tab in the "Standalone Applicartion Settings",
then you will find it in your runtime in -> specialfolderpath("resources")
That specialfolderpath() does also work in the IDE and will return to the path with the current stack.

CAVEAT:
We are not allowed to write in the Application folder on any platform!, and just opening a database here is
considered WRITING so you will need to copy the database to the users documents folder when the app
starts the very first time and open and use it from there.

Do like this "on pre-/openstack":

Code: Select all

...
## LC puts it here when creating the runtime:
put specialfolderpath("resources") & "/your_db.db" into tSourceFile

## But we need to copy and open it here first:
put specialfolderpath("documents")  & "/your_db.db" into tTargetFile

## Now check if we need to copy the DB:
if there is not a file tTargetFile then
  put url("binfile:" & tSourceFile) into url("binfile:" & tTargetFile)
end if

## Now this is the path to the final DB -> specialfolderpath("documents")  & "/your_db.db"
put tTargetFile into tDBPath
...
Best

Klaus

bogs
Posts: 4615
Joined: Sat Feb 25, 2017 10:45 pm

Re: Path to database

Post by bogs » Tue Sep 24, 2019 7:52 pm

Klaus wrote:
Tue Sep 24, 2019 6:29 pm
We are not allowed to write in the Application folder on any platform!,
Say what!?!?
Image

Klaus
Posts: 11730
Joined: Sat Apr 08, 2006 8:41 am
Location: Germany
Contact:

Re: Path to database

Post by Klaus » Tue Sep 24, 2019 8:06 pm

bogs wrote:
Tue Sep 24, 2019 7:52 pm
Klaus wrote:
Tue Sep 24, 2019 6:29 pm
We are not allowed to write in the Application folder on any platform!,
Say what!?!?
Sorry, I'll add an "...UNLESS we have admin permissions!". 8)
Valid for Mac/Win/iOS/Android, no idea about Linux.

bogs
Posts: 4615
Joined: Sat Feb 25, 2017 10:45 pm

Re: Path to database

Post by bogs » Tue Sep 24, 2019 9:30 pm

As always, there are exceptions to any rule. While it is a truly bad idea to mess with your Mac folders permissions to any real degree, in the case of Windows and Linux it depends on where the application is placed as to whether or not you can write to the application folder. Android has reserved places to write to for sure.

On 'nix, if an application is anywhere in your 'Home' folder for a given user, it is fair game. While I haven't explored Windows past 7, I am pretty sure that you can indeed write to the applications folder if it were in any of the default locations. Of course, that may have changed from 8 -> 10, because Ms really started playing with that UAC stuff pretty heavily.
Klaus wrote:
Tue Sep 24, 2019 6:29 pm
...copy the database to the users documents folder when the app
starts the very first time and open and use it from there.
For sure, if you want the best advice, what Klaus wrote is it.

Not only for those operating systems that know better than you do, but for standardizing / compacting your code as well. It is sure easier than a series of if/then or case statements :P
Image

AxWald
Posts: 421
Joined: Thu Mar 06, 2014 2:57 pm

Re: Path to database

Post by AxWald » Wed Sep 25, 2019 11:41 am

Hi,
bogs wrote:
Tue Sep 24, 2019 9:30 pm
While I haven't explored Windows past 7, I am pretty sure that you can indeed write to the applications folder if it were in any of the default locations. Of course, that may have changed from 8 -> 10, because Ms really started playing with that UAC stuff pretty heavily.
At least the current Win10 (using default UAC settings) considers the content of the program folder(s) as "no-write" for the user.

Have fun!
Livecode programming until the cat hits the fan ...

bogs
Posts: 4615
Joined: Sat Feb 25, 2017 10:45 pm

Re: Path to database

Post by bogs » Wed Sep 25, 2019 11:58 am

Yah, that was the way it looked like it was going, heh. I'd still say it is valid to point out that in the case of *Windows*, it might go either way considering there are still XP boxes in production use (i.e. business use, such as Hospitals and the like). Kinda frightening really :twisted:
Image

redfield
Posts: 58
Joined: Thu Apr 04, 2019 1:41 pm

Re: Path to database

Post by redfield » Sun Sep 29, 2019 10:02 am

Thanks all,
I played with Klaus's suggestion and got it working now more or less. I'm on Linux and seemingly can only use the 'home' directory with specialFolderPath().
My script now looks like this:

Code: Select all

on openStack
   put specialfolderpath("home") & "/newfolder" into targetFolder  
   if there is not a folder targetFolder then
      create folder specialfolderpath("home") & "/newfolder"
   end if
   put specialfolderpath("resources") & "/db_test" into tSourceFile
   put specialfolderpath("home") & "/newfolder/db_test" into tTargetFile 
   if there is not a file tTargetFile then
      put URL("binfile:" & tSourceFile) into URL("binfile:" & tTargetFile)
   end if
end openStack

Problem now is when I run the standalone, the database is empty and there are no tables. Do I have to create them within the standalone?

bogs
Posts: 4615
Joined: Sat Feb 25, 2017 10:45 pm

Re: Path to database

Post by bogs » Sun Sep 29, 2019 11:51 am

redfield wrote:
Sun Sep 29, 2019 10:02 am
Problem now is when I run the standalone, the database is empty and there are no tables. Do I have to create them within the standalone?
My understanding is that there are different ways this goes ~
1.) the db isn't created yet or isn't at the location of the path
2.) the db is at the location of the path, but is empty
3.) the db is at the location and has information already

1.) In the code in your program, you should *always* check to see if a file exists or not, then branch the code to either create it, edit it, or notify the user. This can be done with either a case/select or if/then structure. If your in the IDE testing, you can find out with just the messagebox almost anything you'd need to know, such as the path, default folder, tables in the db, etc.

2.) The only way I see this happen is if *1.) is true, and there was no code to construct the db.

3.) This goes back to *1.), if the file is there and setup (which you checked :twisted: ) then you need to have the format for information setup to insert/modify/delete.
Image

redfield
Posts: 58
Joined: Thu Apr 04, 2019 1:41 pm

Re: Path to database

Post by redfield » Sun Sep 29, 2019 3:42 pm

Thanks bogs.
bogs wrote:
Sun Sep 29, 2019 11:51 am
1.) the db isn't created yet or isn't at the location of the path
false
bogs wrote:
Sun Sep 29, 2019 11:51 am
2.) the db is at the location of the path, but is empty
true
bogs wrote:
Sun Sep 29, 2019 11:51 am
3.) the db is at the location and has information already
false
:D
bogs wrote:
Sun Sep 29, 2019 11:51 am
2.) The only way I see this happen is if *1.) is true, and there was no code to construct the db.
Do I understand well, that the database created and used in the IDE, will not be "transferred", when turning the app into a standalone, but rather has to be newly created within the standalone? If so, does it make sense to create it within the openStack handler?

bogs
Posts: 4615
Joined: Sat Feb 25, 2017 10:45 pm

Re: Path to database

Post by bogs » Sun Sep 29, 2019 3:54 pm

redfield wrote:
Sun Sep 29, 2019 3:42 pm
Do I understand well, that the database created and used in the IDE, will not be "transferred", when turning the app into a standalone
Not at all, but you do have to add it to either the copy files in standalone settings (I think, someone else may have a better way), or you need to manually copy it before you send out the program to the location it has to be.
Image

Klaus
Posts: 11730
Joined: Sat Apr 08, 2006 8:41 am
Location: Germany
Contact:

Re: Path to database

Post by Klaus » Sun Sep 29, 2019 5:42 pm

...if you add your database to your standalone via the "Copy files" tab in the "Standalone Applicartion Settings",
then you will find it in your runtime in -> specialfolderpath("resources")
Did you do this?
And did you really add your complete (with tables etc.) database file this way?

redfield
Posts: 58
Joined: Thu Apr 04, 2019 1:41 pm

Re: Path to database

Post by redfield » Sun Sep 29, 2019 6:16 pm

Klaus wrote:
Sun Sep 29, 2019 5:42 pm
...if you add your database to your standalone via the "Copy files" tab in the "Standalone Applicartion Settings",
then you will find it in your runtime in -> specialfolderpath("resources")
Did you do this?
Nope - never paid attention to the 'Copy files' setting up to now :oops: . I thought the database is copied automatically, because of the "Externals" folder with some kind of SQLite stuff that is (automatically) created... :mrgreen: :oops: . Thanks anyway for the help guys, it's working now :) .

bogs
Posts: 4615
Joined: Sat Feb 25, 2017 10:45 pm

Re: Path to database

Post by bogs » Sun Sep 29, 2019 6:21 pm

I thought the database is copied automatically, because of the "Externals" folder with some kind of SQLite stuff that is (automatically) created...
bogs wrote:
Sun Sep 29, 2019 11:51 am
1.) the db isn't created yet or isn't at the location of the path
2.) the db is at the location of the path, but is empty
:P
Image

Post Reply

Return to “Databases”