database works fine in Livecode, not in Android

Creating desktop or client-server database solutions?

Moderators: heatherlaine, Klaus, FourthWorld, robinmiller, kevinmiller

Manu9351
Posts: 19
Joined: Sun Jan 07, 2018 8:48 pm

database works fine in Livecode, not in Android

Post by Manu9351 » Sun Jan 14, 2018 7:52 pm

Hi, my databaseconnection and everything in the app works fine in Livecode. Read, Write, Search, everything is ok when i'm in Livecode on my computer.

On Android, the app says: Database Connection ID is 1, and when i want to do something with the database, theres the error: "revdberr,DatabaseError: no such table: Tablename"
Why? The same thing works fine in livecode.

The database storage on Android is specialFolderPath("Documents")/Databasename.sqlite

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

Re: database works fine in Livecode, not in Android

Post by Klaus » Sun Jan 14, 2018 8:06 pm

Hi Manuel,

please post all relevant scripts and/or pathnames that you are using.
Do you copy that database file from specialfolderpath("resources") to the Docs folder, when the app starts for the first time?

Maybe this is just a typo, but this should read e.g.:
## specialFolderPath("Documents")/Databasename.sqlite
put specialFolderPath("documents") & "/Databasename.sqlite" into tDBFile

Best

Klaus

Manu9351
Posts: 19
Joined: Sun Jan 07, 2018 8:48 pm

Re: database works fine in Livecode, not in Android

Post by Manu9351 » Sun Jan 14, 2018 8:12 pm

Klaus wrote:
Sun Jan 14, 2018 8:06 pm
Hi Manuel,

please post all relevant scripts and/or pathnames that you are using.
Do you copy that database file from specialfolderpath("resources") to the Docs folder, when the app starts for the first time?

Maybe this is just a typo, but this should read e.g.:
## specialFolderPath("Documents")/Databasename.sqlite
put specialFolderPath("documents") & "/Databasename.sqlite" into tDBFile

Best

Klaus
do connect the database, theres a script on the startcard:

Code: Select all

global dbid
on preOpenCard
   put specialFolderPath("documents") & "/MHDManager.sqlite" into dateiPfad
   put revOpenDatabase("sqlite", dateiPfad, , , , ) into dbid
   if dbid = empty then
      hide graphic "anzeigeGruen"
      show graphic "anzeigeRot"
      put "DATABASE ID:    No Connection" into field "field_databaseID"
   else
      hide graphic "anzeigeRot"
      show graphic "anzeigeGruen"
      put "DATABASE ID:    "&dbid into field "field_databaseID"
   end if
end preOpenCard
This works on Livecode, not on android.
i also noticed, that a sound, which i want to play on the first card, doesn't work on android, but in livecode too, the script for the sound:

Code: Select all

on openCard
   put specialFolderPath("documents")&"/startup/tasteWAVE.wav" into klangdatei
   play klangdatei
it seems to me that my app on android knows theres a database, but doesn't be able to read or write something..

quailcreek
Posts: 718
Joined: Sun Feb 04, 2007 11:01 pm
Location: McKenna, WA

Re: database works fine in Livecode, not in Android

Post by quailcreek » Sun Jan 14, 2018 8:59 pm

What Klaus is asking is do you have something like this in your stack script.

Code: Select all

 put specialFolderPath("documents") & "/MHDManager.sqlite" into tDatabaseFile
  
  if there is not a file tDatabaseFile then
    put url("binfile:" & specialFolderPath("engine") & "/MHDManager.sqlite") into url("binfile:" & specialFolderPath("documents") & "/MHDManager.sqlite")
  else
    -- answer "There is a file MHDManager.sqlite"
  end if
Tom
MacBook Pro OS High Sierra 10.13.5

Manu9351
Posts: 19
Joined: Sun Jan 07, 2018 8:48 pm

Re: database works fine in Livecode, not in Android

Post by Manu9351 » Sun Jan 14, 2018 9:08 pm

quailcreek wrote:
Sun Jan 14, 2018 8:59 pm
What Klaus is asking is do you have something like this in your stack script.

Code: Select all

 put specialFolderPath("documents") & "/MHDManager.sqlite" into tDatabaseFile
  
  if there is not a file tDatabaseFile then
    put url("binfile:" & specialFolderPath("engine") & "/MHDManager.sqlite") into url("binfile:" & specialFolderPath("documents") & "/MHDManager.sqlite")
  else
    -- answer "There is a file MHDManager.sqlite"
  end if
not yet, can you explain me what the code means? i don't understand it.. i'm sorry..

quailcreek
Posts: 718
Joined: Sun Feb 04, 2007 11:01 pm
Location: McKenna, WA

Re: database works fine in Livecode, not in Android

Post by quailcreek » Sun Jan 14, 2018 10:18 pm

No problem, don't be sorry for asking questions. I guess I need to first ask if you have attached your database in Copy Files of the standalone settings? If not you need to.

When you attach files in the Copy Files they are put in the "engine", also call "resources", folder of the app. Therefore it is not available for the app ti use. You must copy the file to the apps document folder so it can see it. That's what the code I posted does. Because the sqlite database is a binary file it needs to be copied as binfile.
Tom
MacBook Pro OS High Sierra 10.13.5

Manu9351
Posts: 19
Joined: Sun Jan 07, 2018 8:48 pm

Re: database works fine in Livecode, not in Android

Post by Manu9351 » Sun Jan 14, 2018 10:23 pm

quailcreek wrote:
Sun Jan 14, 2018 10:18 pm
No problem, don't be sorry for asking questions. I guess I need to first ask if you have attached your database in Copy Files of the standalone settings? If not you need to.

When you attach files in the Copy Files they are put in the "engine", also call "resources", folder of the app. Therefore it is not available for the app ti use. You must copy the file to the apps document folder so it can see it. That's what the code I posted does. Because the sqlite database is a binary file it needs to be copied as binfile.
ok, it works, i don't understand whats the different between that and the option to save the database manually at the documents location. however. it works. i don't have to understand anything :D

the last question from me: sqlite is a binfile.. which one is my .wav data? what do i have to write in the code, so that the player plays the .wav file on stackstart?

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

Re: database works fine in Livecode, not in Android

Post by Klaus » Mon Jan 15, 2018 6:10 pm

Hi Manuel,

you better understand everything, believe me, you are the developer now and repsonsible for everything!8)

Here some very important basics:
1. specialfolderpath("documents")
This folder is in your USER folder on your Mac or PC and completely different on iOS/Android !
There this is a folder inside of the application package (the Standalone) and is
a. completely EMPTY unless we fill it and
b. the ONLY folder on Mobile where we have write permissions!

2. specialfolderpath("resources")
In the IDE this is the folder where the current stack resides in.
In a standalone this is the folder where you will find all the data (database files, images, sounds, videos etc.) you have added in the "Standalone Builder Settings" via the "Copy files..." tab.

3. So in order to work with your database, you need to copy it to the users DOCUMENTS folder and open it from there. And since we do not want to overwrite the database file here, we need to check if we already copied the database from RESOURCES to DOCUMENTS!

4. Sounds and videos can be played directly from within the resources folder, no need to copy them to DOCS folder, so this will work:
...
put specialFolderPath("resources") & "/startup/tasteWAVE.wav" into klangdatei
play klangdatei
...

Best

Klaus

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

Re: database works fine in Livecode, not in Android

Post by Klaus » Mon Jan 15, 2018 6:16 pm

Was hier bei Dir passiert ist, ist Folgendes:
...
put specialFolderPath("documents") & "/MHDManager.sqlite" into dateiPfad
put revOpenDatabase("sqlite", dateiPfad, , , , ) into dbid
...
Zu dem Zeitpunkt war die Datei MHDManager.sqlite nicht am angegebenen Ort vorhanden, also hat LC mit dem Befehl -> revOpenDatabase("sqlite", dateiPfad, , , , ) eine neue und LEERE Datenbankdatei an dem Ort erstellt! Und diese Datanbankdatei hat noch keine TABLEs etc...

Manu9351
Posts: 19
Joined: Sun Jan 07, 2018 8:48 pm

Re: database works fine in Livecode, not in Android

Post by Manu9351 » Tue Jan 16, 2018 7:00 pm

Klaus wrote:
Mon Jan 15, 2018 6:10 pm
3. So in order to work with your database, you need to copy it to the users DOCUMENTS folder and open it from there. And since we do not want to overwrite the database file here, we need to check if we already copied the database from RESOURCES to DOCUMENTS!
ok i understand. but under "documents" i can't find the database file? is there any hidden folder i can't see.. It's necessary to know, because i wan't a build-in Backup function for the database. So that i can save weekly, monthly whatever the database to another folder, or to my laptop, in case the phone gets broken..

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

Re: database works fine in Livecode, not in Android

Post by Klaus » Tue Jan 16, 2018 7:04 pm

Hi Manuel,

I don't own a cellphone or something, so I cannot tell you where specialfolderpath("documents") actually is on your device, but should be a folder inside of the application bundle.

Just add a button to your app and check it yourself on the devide:

Code: Select all

on mouseup
  answer specialfolderpath("documents")
end mouseup
Best

Klaus

SparkOut
Posts: 1789
Joined: Sun Sep 23, 2007 4:58 pm

Re: database works fine in Livecode, not in Android

Post by SparkOut » Tue Jan 16, 2018 10:39 pm

specialFolderPath("documents") is a sandboxed location that will not be visible to anything but your application unless you have rooted the phone and/or made some other unsafe changes.

If you look at the location: /mnt/sdcard/ you should be able to make a publicly accessible folder there to copy the database to - but you will need to ensure the standalone settings include the correct permissions.

Manu9351
Posts: 19
Joined: Sun Jan 07, 2018 8:48 pm

Re: database works fine in Livecode, not in Android

Post by Manu9351 » Thu Jan 25, 2018 8:04 pm

SparkOut wrote:
Tue Jan 16, 2018 10:39 pm
specialFolderPath("documents") is a sandboxed location that will not be visible to anything but your application unless you have rooted the phone and/or made some other unsafe changes.

If you look at the location: /mnt/sdcard/ you should be able to make a publicly accessible folder there to copy the database to - but you will need to ensure the standalone settings include the correct permissions.
thanks for your reply. if i understood correctly the default user documents folder should be a writeable location.
so normally this code should work, but it doesn't

Code: Select all

global targetDatabaseFolder
on mouseUp
   put the documents folder & "/MHDManager.sqlite" into targetDatabaseFolder
   put url("binfile:" & specialFolderPath("documents") & "/MHDManager.sqlite") into url("binfile:" & targetDatabaseFolder)
end mouseUp
Did i make any mistakes?

the standalone settings include the permission "Write external storage"..

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

Re: database works fine in Livecode, not in Android

Post by Klaus » Thu Jan 25, 2018 8:23 pm

Hi Manuel,

specialfoderpath("documents") = the documents folder!
So you are trying to copy a file to itself and LC has no humor obviously. :-)

I think you mean: specialfoderpath("resources")

You should check if the file is not already there!

Code: Select all

...
if there is NOT a file targetDatabaseFolder then
  put url("binfile:" & specialFolderPath("resources") & "/MHDManager.sqlite") into url("binfile:" & targetDatabaseFolder)
end if
...
Best

Klaus

Manu9351
Posts: 19
Joined: Sun Jan 07, 2018 8:48 pm

Re: database works fine in Livecode, not in Android

Post by Manu9351 » Thu Jan 25, 2018 8:44 pm

Klaus wrote:
Thu Jan 25, 2018 8:23 pm
Hi Manuel,

specialfoderpath("documents") = the documents folder!
So you are trying to copy a file to itself and LC has no humor obviously. :-)

I think you mean: specialfoderpath("resources")

You should check if the file is not already there!

Code: Select all

...
if there is NOT a file targetDatabaseFolder then
  put url("binfile:" & specialFolderPath("resources") & "/MHDManager.sqlite") into url("binfile:" & targetDatabaseFolder)
end if
...
Best

Klaus
No, i thought specialFolderPath("documents") and the Users documents folder (which i can browse via filemanager) are different things?

Because in my case the sFP("Documents") is: /data/user/0/com.navratek.filialtools/files
and the user documents folder is: /storage/emulated/0/Documents

But the second one is a different location on each smartphone, so i want a global solution on every smartphone

so i want to copy and make the databasefile visible in my normal storage folder. do you know what i mean?

ps. thats the script when i start the app:

Code: Select all

on preOpenStack
   set the fullscreenmode of me to "exactFit"
   put specialFolderPath("documents") & "/MHDManager.sqlite" into tDatabaseFile
   
   if there is not a file tDatabaseFile then
      put url("binfile:" & specialFolderPath("engine") & "/MHDManager.sqlite") into url("binfile:" & specialFolderPath("documents") & "/MHDManager.sqlite")
   else
      -- answer "There is a file MHDManager.sqlite"
   end if

Post Reply

Return to “Databases”