sqlite with android's devices

[nighty974]

Good morning everybody,
im new livecode user and i got some problems...
i use livecode 7.0 and i'm trying to create an app for android smartphone.
i created a sqlite database and i tried to use it with live code.
On my computer, it work fine, but when i test it on my device (samsung s5) , i can connect to the databse but when i try to display the content with this code :

Code: Select all

   put "SELECT * FROM '"&Z&"';" into X
   put revDataFromQuery(, , idBdd, nomTable) into Y
put Y into the field "a field"

it doesnt work.



with Z a table

more explications:

i have 4 button, when i press 1, it goes on the next card and display ( into a field named "choix") all informations about a table (i have 4 differents tables).
when i test the app on my computer, it work fine and every time i press a buton => i go to the next card (named "list" for exemple) and display the good table on the field "choix", then i press a button "return" to back to the previous card and then i press an other button and it goes to the card "list" and it displays an other table into the field "choix" and it's what i want.

but when im testing my app on android, it display one table for all button...
i can press every button, the content in the field won't change, i searched for a solution during a long time and finally i decided to ask for some help in the forum.

thanks for helping me, i apologize for my terrible english and for asking help..

[MaxV]

Did you select "include SQLite libraries" check box of Externals in stand alone application setting / Android?
See point 3 of the following picture:

[nighty974]

hi Maxv,
Thanks for your help !
i selected "SQLite" like in the picture :/

i want to access to a full database (i save the database.sqlite with the apk package with "copy files" in the standalone application setting) and now it look totally empty :/

in a new button in a new stack i write in the script :

Code: Select all

on mouseUp
set the defaultfolder to the specialfolderpath("documents")
put revOpenDatabase("sqlite","mydatabase.sqlite") into X
put "SELECT * FROM mytable" into Y
put revDataFromQuery(tab,return,X,Y) into Z
answer Z
end mouseUp

it work on my computer but in my device it say : no such tables ...

[Simon]

Hi nighty974,
When you use "Copy Files" with mobile devices the files end up in specialFolderPath("engine") from there you have to copy it to specialFolderPath("documents") if you intend to write to it. For just reading you can leave it in the engine folder.

Simon

[nighty974]

HI Simon,
Thanks a lot !!!
now i found my database and im going to do some test

will keep you updated!

[nighty974]

Now, i've got a problem when i try to move a file into an other directory, i used "rename" but it doesn't seem to work fine.
Any idea?

[Simon]

Hi nighty974,
No, it isn't rename.
Do you know how to use "put url" to copy a file from one location to another?

Simon

[nighty974]

No, i don't.
i tried to read database in specialfolderpath("engine")

with this :

set defaultfolder to specialfolderpath("engine")
put revopendatabase("sqlite","database.sqlite") into X
put revdatafromquery(X,"SELECT * FROM mytable") into Y
put Y into the field "myfield"

but there is nothing in my field, and with many test it appear that there are no tables into my database (this database work fine on pc)

[nighty974]

I found it,
thanks for your help guys the "put URL" was the answer
thanks again !

[Klaus]

Hi nighty974,

set defaultfolder to specialfolderpath("engine")
put revopendatabase("sqlite","database.sqlite") into X
put revdatafromquery(X,"SELECT * FROM mytable") into Y
put Y into the field "myfield"

but there is nothing in my field

this does not work because you (your user) does not have write permissions in the ENGINE folder!
So your script silently fails at this point

You should ALWAYS check the result of your actions like this:

Code: Select all

...
## I NEVER mess around with the defaultfolder  :D 
put specialfolderpath("engine") & "/database.sqlite" into tDBFile
put revopendatabase("sqlite",tDBFile) into X
## On success X will be an integer (the actual connection ID) or will contain an error description if the connection failed!
if X is not an integer then
  answer "An ERROR occured:" && X
  ## DON'T continue in this case!
  exit to top
end if
put revdatafromquery(X,"SELECT * FROM mytable") into Y
put Y into the field "myfield"
...

Do you already know these great stacks? Wonderful learning resources!
http://www.hyperactivesw.com/revscriptc ... ences.html

Best

Klaus

[nighty974]

Hi Klaus,
you taught me a lot !
i"m going to use the code u gave me, thanks for the help and thanks for the link !
I'm going to check it right now

[kap1987]

I know this is an old topic but i have a problem as well...

i was able to load the database when on pc on livecode... but on android it seems that it won´t load the data... i don´t get any error - simply no data...

--> put specialFolderPath("documents") & "/runrevemails.sqlite" into tDatabasePath
i used this in my script... my sqlite database was copied via pc to the documents section of my android phone. but nothing happens when i push the button to put the data into the data grid.

[Klaus]

Hi kap,

due to sandboxing every app has its own documents folder, which is obviously different
than "the documents section of your android phone"!

So better add your database to your standalone via the "Copy files" tab in the standalone
builder settings, then check "on openstack" if there already is a sqlite file in THAT docs folder:

Code: Select all

...
## Here you will find all files and folders that you added to your app as described above
put specialfolderpath("resources") & "/runrevemails.sqlite" into tSourceDB

## Only here we have write permission!
## And even opening a database is considered "writing"
put specialfolderpath("documents") & "/runrevemails.sqlite" into tTargetDB

## APP opened for the very first time?
if there is not a file tTargetDB then
   put url("binfile:" & tSourceDB) into url("binfile:" & tTargetDB)
end if
## NOW you can open and access your db!
...

Best

Klaus

[kap1987]

OK i will try it. Is ist possible to Update The Database? I mean that i edit The Data on pc and put The Database Back to The Phone. Is ist possible to Update The Database when The app is open?
For My Manager game i would Like to load a Special Database in game.

[Klaus]

Sorry, I really don't know.