Saving data on closing app

[jessamy99]

Hi Ender and everyone!

I should say Happy New Year - sorry it is a bit late!

I have a problem - I am doing something silly as usual!
Please help me, you are all so good at that!
I have an sqlite database and I can read from it with no problem.
But I can't remember how to write to it.
The database is stored in cache in the iPhone or iPad.
See the script below...
What do I do to write to it?
Thanking you in advance for your help!
All the best,
Jessamy.

function getLoc pDirectory
if the environment is "mobile" then
put specialFolderPath("cache") into tLoc
else
put the filename of me into tLoc
set the itemDelimiter to slash
delete item -1 of tLoc
end if
if pDirectory is empty then
put "/theContent/" after tLoc
return tLoc
else
put "/theContent/" & pDirectory & slash after tLoc
return tLoc
end if
end getLoc

[joel.epsteinBUS31vi]

Howdy -

Below, I'll give you a rundown of how I've been doing just what you're asking. It's working well for me. Perhaps you can modify for your purposes.

First, you need to make sure the database is in a writable location. On iOS, the best place for this is the documents directory. But, you may want to start with an existing database, and not create one on the fly. To do this, you need to include the database in the "copy files" pane of your standalone application settings.

In my case, I put my database (swpw.sqlite) in the directory "db" and copied the entire directory. Then, on startup, I check to see if the database is in the documents directory. If not, I copy it from the copied "db" directory. Then, I can use regular SQL commands to communicate to the database in the new location. Here's some code:

to copy the existing database to the documents directory:

Code: Select all

command databaseConnect
   if the environment="mobile" then 
      if there is not a file (specialFolderPath("Documents") & "/swpw.sqlite") then
         put (specialFolderPath("engine") & "/db/swpw.sqlite") into spath
         put url ("binfile:" & spath) into tDatabasePath
         put tDatabasePath into url ("binfile:"& (specialFolderPath("Documents") & "/swpw.sqlite"))  
      end if
   else
   end if
   put specialFolderPath("documents") & "/swpw.sqlite" into tDatabasePath
   put revOpenDatabase("sqlite", tDatabasePath, , , , ) into tDatabaseID
   setDatabaseID tDatabaseID
end databaseConnect

an example of then writing to the database

Code: Select all

function updateData tableName, whichColumn, oldValue, newValue
   replace "'" with "''" in oldValue
   replace "'" with "''" in newValue
   put getDatabaseID() into tDatabaseID
   put "UPDATE " &  tableName & " SET " & whichColumn & " = '" & newValue & "' WHERE " & whichColumn & " = '" & oldValue & "';" into tSQL
   revExecuteSQL tDatabaseID, tSQL 
   return the result
   // to call in a script: put updateData(tableName, whichColumn, oldValue, newValue) into tUpdateData
end updateData

Hope this helps.

Joel

[Klaus]

Hi jessamy99,

what Joel wrote!

Important:
Do not store anything in the CACHE folder, a CACHE can be emptied by the OS if neccessary, and then you are erm... licked!
The docs say that the CACHE folder is for "...any transient data..."!
Copy your database to the documentes folder when the app starts the first time as Joel showed in his script.

And the rest is just a matter of some more SQL and not really Livecode problem!
This page got me started with SQL (and I read it up every time I use database access in my projects )
http://www.w3schools.com/sql/default.asp


Best

Klaus

[jessamy99]

Hi Joel and Klaus,

Thankyou so much for your help.
My request was very badly written.
I can write and read from a database in documents.
But Apple wanted the databases kept in a content folder.
Then it gets copied from engine to cache in the iPhone.
I am not sure how to write to the database there.

Is it ok to write a database to documents in an app?
I thought it was no longer accepted.

I really appreciate your input.
I was stuck with an app that won't remember data when it gets closed and reopened..
And although I read what others had put, it didn't answer my question.
You are such stars!!

[jessamy99]

Hi again,

I forgot to ask, what is the syntax when you want to put a variable into a database?
For example, in the line below I want to put in a variable called tfish instead of Angelfish.png.
I have tried countless combinations.... (Tearing hair out...)

put "INSERT into travel VALUES ('1','Angelfish.png','Angel Fish','Blue and yellow');" into tSQL

Thank you guys!
Jessamy

[joel.epsteinBUS31vi]

You can just type the variable name in the comma delimited list.

For example:

put "INSERT into travel VALUES ('1', tFish ,'Angel Fish','Blue and yellow');" into tSQL

However, you must be careful that the variable doesn't contain any special characters that may break the SQL syntax (e.g., ampersands, quotes, etc.). If it does, you should encode them prior to trying to insert them into the SQL command.

Hope that helps.

Joel

[Klaus]

Hi Jessamy,

For example, in the line below I want to put in a variable called tfish instead of Angelfish.png.
I have tried countless combinations.... (Tearing hair out...)

DON'T!

First of all, put these two little functions into your stack script, VERY handy when dealing with database and other stuff!

Code: Select all

## Quote a string -> "
function q tString
  return QUOTE & tString & QUOTE
end q

## Single quote a string -> '
function q2 tString
  return "'" & tString & "'"
end q2

I think you can guess what they do!

Then you can do something like:
...
put "INSERT into travel VALUES ('1'," & q2(tFish) & ",'Angel Fish','Blue and yellow');" into tSQL
...


Best

Klaus

[jessamy99]

Thankyou both so much!
I managed to work it out with your help!
Hurrah, another mystery solved!

I do so love solving problems, even if I almost ran out of hair to pull out.

Thanks again!
Jessamy