android app not able to save to sql lite db

[cusingerBUSCw5N]

I have used part of the ticked off application on my app. It is supposed to save task entries to a sql lite database - but it is failing.

When I debug it, it can find the cDatabaseID (which is 1) - it shows that the correct query is going in
(which is: insert into tasks values ('1','tasks 1','','false','tasks','','task')

but it doesn't work, because when it does a query, there are no tasks.

It works on the development version on my desktop.

I am stumped...

[Klaus]

Hi,

did you add the database file via the "Copy files" tab in the standalone builder?
If yes, then that file can be found in -> specialfolderpath("engine"), but you do not have
WRITE PERMISSIONS in that folder, so nothing will get into the database!

Please read the "File and folder handling" section in the "Adroid Release Notes" -> LiveCode: Menu: Help

The trick is to copy that file to a folder (maybe -> specialfolderpath("documents")) where
you DO have write permissions. Then adding data to the database will not fail anymore.

Do a search in the iOS and ANDROID forum, this has been discussed a couple of times before.


Best

Klaus

[cusingerBUSCw5N]

I will dig some more, but when I tried testing the TickedOff application on my Android, it works... so why would it work and my other app not, when I built it using the same stack code?

[Klaus]

I have no idea what the "Ticked Off" application is!
Can you post its relevant parts/scripts?

Guessing is ineffective!

[cusingerBUSCw5N]

The Ticked Off application is the sample used in the getting started series... http://www.runrev.com/developers/docume ... rs-course/ - under # 6 if you download the materials, there is completed stack. It's a task list. I used it as a base for one of the cards of my app. Anyway, when I test it on my android, it works - you can add and delete tasks. But when I try my app, it doesn't. Makes no sense.

However, moving on...
First, I added my database in the standalone application settings. That seemed to have worked a bit - because now when I open the application, the data appears.

Unfortunately, it still won't access the database - either to read or to write. It doesn't seem to understand specialfolderpath(26)...(nor did it understand specialfolderpath("documents")) - it just shows ""/files/tasks.sqlite"....and skips the part about specialfolderpath. Because it can't find the database, it is always trying to create a new one - unsuccessfully.

My database is tasks.sqlite

Code: Select all

on preOpenStack
   ## Connect to the database
   
## Files that you add via the "copy files" tab in the standalone builder can be found in hte specialfolder("engine") on iOS
put specialfolderpath("engine") & "/tasks.sqlite" into tHiScoreFile
put specialfolderpath(26) & "/files/tasks.sqlite" into tHiScoreFileUser
## No suffix neccessary, but won't hurt of course :-)

## Check if the files have already been copied before, if not, copy them:
if there is NOT a file tHiScoreFileUser then
   answer tHiScoreFile
   answer tHiScoreFileUser
put url("binfile:" & tHiScoreFile) into url("binfile:" & tHiScoreFileUser)
end if
answer "try"

when I run this, tHiScoreFile is: data/app/com.runrev.disaster_app_1.apk/tasks.sqlite
tHiScoreFilerUser is /files/tasks.sqlite (skipping whatever specialfolderpath(26) is..)

...which doesn't work...I don't know how specialfolderpath(26) fits it...or what it is... or how to create it.

Bottom line, I don't get it.

Just for the record...here are the codes I am using to access the database and writing to it:

Code: Select all

on databaseConnect
   local tDatabasePath, tDatabaseID
   ## The database must be in a writeable location

   put specialFolderPath(26) & "/tasks.sqlite" into tDatabasePath                                                                                                                                                                                                                                                                                                                                                                                                                                  
   ## Open a connection to the database
   ## If the database does not already exist it will be created and we will add the table
   if there is not a file tDatabasePath then

      ## Store the database id as a custom property so other handlers can access it
      set the cDatabaseID of me to tDatabaseID
 
      databaseCreateTable
   else
      put revOpenDatabase("sqlite", tDatabasePath, , , , ) into tDatabaseID
      
      ## Store the database id as a custom property so other handlers can access it
      set the cDatabaseID of me to tDatabaseID
    

   end if
   put the cDatabaseID of me into tbob
   answer tbob
answer tDatabaseID
end databaseConnect

(when I run the code above,and use "documents" instead of 26, I get 1for tbob - but that doesn't help me because no data appears and I can't add data.
when I use 26, I get a blank for tbob)

Code: Select all

on addTask pTaskID, pTitle, pKind

   put the short name of the current card into tusename
   put "INSERT into tasks VALUES ('" & pTaskID & "','" & pTitle & "','', 'false','" & tusename & "','','" & pKind & "')"into tusethis

   revExecuteSQL the cDatabaseID of me, tusethis

    put "SELECT count(*) from tasks"  into tSQL
    put revDataFromQuery(,,the cDatabaseID of me,tSQL) into tDetails

   return tDetails
 
end addTask

[Klaus]

I think it is a very bad idea to use something like specialfoderpath(26) without having the slightest idea what it is!

specialfoderpath(26) returns the "Application Data" folder for the current user on WINDOWS and nowhere else!
I see you copied my script from the "Installer Problem" thread, which was Windows specific and where I explained what that means.

The example lesson uses -> specialfolderpath("documents") and that should work on Android actually...

Best

Klaus

[Klaus]

OK, after a second look I see your problem!
If there is NOT a database file yet, you need to create it, but you don't!

Code: Select all

on databaseConnect
   local tDatabasePath, tDatabaseID
   ## The database must be in a writeable location
   put specialFolderPath("documents") & "/tasks.sqlite" into tDatabasePath
   ## Open a connection to the database
   ## If the database does not already exist it will be created and we will add the table
   if there is not a file tDatabasePath then

      ## !!!!!!!!This will create a new and empty database
       put revOpenDatabase("sqlite", tDatabasePath, , , , ) into tDatabaseID
      ## !!!!!!!!

      ## Store the database id as a custom property so other handlers can access it
      set the cDatabaseID of me to tDatabaseID

     ## Now create tables in the new database!
      databaseCreateTable
   else
  ...

So to answer your earlier question:

so why would it work and my other app not, when I built it using the same stack code?

You did not use the same stack code


Best

Klaus

[cusingerBUSCw5N]

solved.

First, the line that inadvertently got erased was put back - thank you.

Still didn't work - but found that it was picking up the tasks.sql database from the TickedOff application that I had tested! Basically, I just tested the other application without changing the settings so it was putting everything in the same folder - and since the databases were named the same thing, it wasn't creating a new one.

The database for my new app had more fields than the TickedOff one - so it was failing because it was trying to get data from fields that didn't exist. Even before I tried the TickedOff application on the Mobile device, it didn't work I forgot one of the fields when I created the database. It worked on my computer because I had manually set up the database..

Solved it by naming the database tasks4.sql Used specialFolderPath("documents") - and it worked fine - no need for extra script at the top. Everything cleared up

Anyway...all my fault... Bottom line: The database fields have to match exactly...The database name has to be unique among your other applications OR you have to reset your settings each time you test so that it looks for the database in another folder.. .... Pretty straightforward
Thanks for your help and patience.

[Klaus]

OK, glad you got it sorted out!