Running SQLite Database App In The iOS Simulator

[Knightlite]

Hi,

I created a small app that uses a SQLite database, that is based on the Example Code found in the video tutorial. When you run the app in LiveCode the database connects and I can move from record to record with the NEXT and PREVIOUS buttons. It works fine.

But, when I try running it in the iOS simulator, the buttons don't work for moving from one record to the next. The screen is stuck on the last record that was displayed in LiveCode.

Is there something special I have to do to test this app on the Simulator? In the Standalone settings I have the SQLite checkbox checked for the Externals. I don't know what I am missing.

Any help would be greatly appreciated.

Jim

[Jellicle]

Where is the database?

Gerry

[Knightlite]

Hi Gerry,

The project is in a folder on the desktop called Fire1. The database file is in that folder.

The code for connecting is

Code: Select all

   ## Connect to the database
   ## Save the connection id
   put specialFolderPath("desktop") & "/Fire1/Fire2.sqlite" into tDatabasePath
   put revOpenDatabase("sqlite",tDatabasePath,,,,) into sDatabaseID

Jim

[danielroibert]

Hello,
i think that there is no "desktop" folder in iOs.
You have to write some think like

if the platform is "iphone" then
put specialFolderPath("engine") & "/Fire2.sqlite" into tDatabasePath
else
put specialFolderPath("desktop") & "/Fire1/Fire2.sqlite" into tDatabasePath
end if

But, take careful to create or copy the file with the standalone apps.
You have to chose the right folder. maybe "documents" or "engine" ?
take a look at the sample in the "lessons" section.

Best regards,
Daniel ROBERT

[danielroibert]

here is a stuck of code from lessons

command databaseConnect
local tDatabasePath, tDatabaseID

## The database must be in a writeable location
put specialFolderPath("documents") & "/runrevemails.sqlite" into tDatabasePath

## Open a connection to the database
## If the database does not already exist it will be created
put revOpenDatabase("sqlite", tDatabasePath, , , , ) into tDatabaseID

## Store the database id so other handlers can access it
setDatabaseID tDatabaseID
end databaseConnect

[Dixie]

knightlite...

Using the 'copy files' pane in the application settings, add your sqlite database... It will be placed in the 'engine' folder. If you are not going to be adding, updating or deleting records then it could just stay in the 'engine' folder... However, if you want to interact with your database then you will have to move it into ( a copy of it anyway) into the documents folder...

Code: Select all

      put specialFolderPath("documents") & "/yourdatabase.db" into dataBaseFilePath
      if there is not a file documentFilePath then
         put specialFolderPath("engine") & "/yourdatabase.db" into engineFilePath
         put URL ("binfile:" & engineFilePath) into URL ("binfile:" & dataBaseFilePath)
      end if

Hope this helps

Dixie

[Knightlite]

Hi,

I still can't get the app to work in the iOS simulator. It still does not connect to the database file.

Here is my code below. Can anyone see what I might be doing wrong?

Jim

Code: Select all

local sDatabaseID, sRecordSetID

on preopencard
   local tDatabasePath, tSQLQuery
   
   ## Connect to the database
   ## Save the connection id
   if the platform is "ipad" then 
put specialFolderPath("engine") & "/Fire2.sqlite" into tDatabasePath
else 
put specialFolderPath("desktop") & "/Fire1/Fire2.sqlite" into tDatabasePath
end if

    put specialFolderPath("documents") & "/Fire2.sqlite" into dataBaseFilePath
      if there is not a file documentFilePath then
         put specialFolderPath("engine") & "/Fire2.sqlite" into engineFilePath
         put URL ("binfile:" & engineFilePath) into URL ("binfile:" & dataBaseFilePath)
      end if
   
   //put specialFolderPath("desktop") & "/Fire1/Fire2.sqlite" into tDatabasePath
   put revOpenDatabase("sqlite",tDatabasePath,,,,) into sDatabaseID
   
   ## Query all details
   ## Save the recordset id
   put "SELECT * from Questions" into tSQLQuery
   put revQueryDatabase(sDatabaseID,tSQLQuery) into sRecordSetID   

[bn]

Hi Jim,

one thing I noticed is that the platform will never return "iPad", use the machine instead.
From the iOS release notes:

Runtime environment querying
You can fetch numerous pieces of information about the environment in which the current application is running with the following syntax.
To determine what processor an application is running on use the processor. In the simulator this will return i386 and on a real device this will return ARM.

To determine the type of device an application is running on use the machine. This will return one of:
• iPod Touch – the device is one of the iPod Touch models
• iPhone – the device is one of the iPhone models
• iPhone Simulator – the device is a simulated iPhone
• iPad – the device is the iPad
• iPad Simulator – the device is a simulator iPad

later you use "tDatabasePath" to query the database. Since it will have the path to the desktop it will fail.
For the rest of accessing databases I don't know but since it works for you on the desktop it should be ok.


also:

Code: Select all

  put specialFolderPath("documents") & "/Fire2.sqlite" into dataBaseFilePath
   if there is not a file documentFilePath then
      put specialFolderPath("engine") & "/Fire2.sqlite" into engineFilePath
      put URL ("binfile:" & engineFilePath) into URL ("binfile:" & dataBaseFilePath)
   end if

you should enclose this in a conditional e.g. for the iPad and a different for the simulator. If you look into your documents folder on your Mac there should be an empty file "Fire2.sqlite". That is because your statement looks for a file in the documents folder (both on the iPad and the Mac). On the Mac you want to copy from the engine folder which does not exist on the Mac, only on iOS and Android. Now your code wants to copy an inexistent file to documents. It just creates the empty file. On the iPad it should work ok.


Kind regards

Bernd

[bn]

Hi Jim,

you might want to check for "the environment" as a conditional

and additionally "the machine" if you want it iPad or Simulator specific.

Kind regards

Bernd

[Knightlite]

Hi Bernd,

Thank you for taking the time to try to help me.

I guess I am having trouble understanding what you mean.

You build a LiveCode app that uses a database and should be able to test in the iOS simulator. Thats the whole idea of having the simulator. I don't have an account with Apple as of yet, so I can't test it on the device itself.

I downloaded the Recordset example from LiveCode. It is just a simple recordset example of email addresses. I copied it to a folder in the Documents folder on the Mac called "test". I added your code, but I guess I am doing something wrong, because that does not work in the simulator either.

The code for the stack is -

Code: Select all

local sDatabaseID, sRecordSetID

on preopencard
   local tDatabasePath, tSQLQuery
   
   ## Connect to the database
   ## Save the connection id
   put specialFolderPath("documents") & "/test/emailaddresses.sqlite" into tDatabasePath
   put revOpenDatabase("sqlite",tDatabasePath,,,,) into sDatabaseID
   
   put specialFolderPath("documents") & "/test/emailaddresses" into dataBaseFilePath
   if there is not a file documentFilePath then
      put specialFolderPath("engine") & "/test/emailaddresses.sqlite" into engineFilePath
      put URL ("binfile:" & engineFilePath) into URL ("binfile:" & dataBaseFilePath)
   end if
   
   ## Query all details
   ## Save the recordset id
   put "SELECT * from contact_details" into tSQLQuery
   put revQueryDatabase(sDatabaseID,tSQLQuery) into sRecordSetID   
   
   ## Clear the fields
   put empty into field "name"
   put empty into field "email"
   
   ## Display the first record
   displayRecord
end preopencard

on showNextRecord
   ## Move to the next record in the record set id and show it
   revMoveToNextRecord sRecordSetID
   displayRecord
end showNextRecord

on showPreviousRecord
   ## Move to the previous record in the record set id and show it
   revMoveToPreviousRecord sRecordSetID
   displayRecord
end showPreviousRecord

on displayRecord
   ## Shows the details of the current record
   put revDatabaseColumnNamed(sRecordSetID,"name") into field "name"
   put revDatabaseColumnNamed(sRecordSetID,"email") into field "email"
   
   ## Disable and enable the previous and next buttons as appropriate
   if revCurrentRecordIsFirst(sRecordSetID) then
      disable button "Previous"
   else
      enable button "Previous"
   end if
   
   if revCurrentRecordIsLast(sRecordSetID) then
      disable button "Next"
   else
      enable button "Next"
   end if
end displayRecord

How would you change the code so that it would run on the simulator?

you might want to check for "the environment" as a conditional

and additionally "the machine" if you want it iPad or Simulator specific.

Sorry, I don't know what you mean by the above. I am brand new to LiveCode and still trying to understand it. How do you check for "the environment"?


Thank you again for your help.

Jim

[bn]

Hi Jim,

sorry if I confused you. Could you indicate where exactly you downloaded the recordset from?
I can not find it and would like to test before I go on.

Meanwhile could you test your code with this:

Code: Select all

local sDatabaseID, sRecordSetID
on preopencard
    local tDatabasePath, tSQLQuery
   if the environment is "mobile" then 
      put specialFolderPath("engine") & "/Fire2.sqlite" into tDatabasePath
      put specialFolderPath("documents") & "/Fire2.sqlite" into dataBaseFilePath
            if there is not a file documentFilePath then
                  put specialFolderPath("engine") & "/Fire2.sqlite" into engineFilePath
                  put URL ("binfile:" & engineFilePath) into URL ("binfile:" & dataBaseFilePath)
            end if
   else 
      put specialFolderPath("desktop") & "/Fire1/Fire2.sqlite" into tDatabasePath
   end if
      put revOpenDatabase("sqlite",tDatabasePath,,,,) into sDatabaseID
   
   ## Query all details
   ## Save the recordset id
   put "SELECT * from Questions" into tSQLQuery
   put revQueryDatabase(sDatabaseID,tSQLQuery) into sRecordSetID

this is the code snippet you posted in your first post above changed so it might/might not work. Don't forget to include the sqLite externals in your Standalone Application Settings.(tick the box)

Anyway please read the iOS release notes in the help menu.

Unfortunately I am not good at sql/databases at all. So I don't feel comfortable trying to help you with that. But getting a file from the engine folder copied to the document folder should work.

Kind regards

Bernd

[Knightlite]

Hi Bernd,

I downloaded the code from this url:

http://www.runrev.com/products/academy/livecode-academy


It was under Lesson 5 for the database examples.


I figured I would just start with their example code, and see if I can get that to work inside the simulator.

I will try your code above, and get back to you.

Thanks so much for all your help.

Jim

[bn]

Hi Jim,

please delete your username and password. It is in the open. You can edit posts by clicking the edit button.

Kind regards

Bernd

[bn]

Hi Jim,

I got the stack and the code I posted works in the simulator and as a stack on the Mac.
I used this code and used your pathes. Assuming your file "Fire2.sqlite" is still in the folder Fire1 on your desktop
And you included the file Fire2.sqlite in the standalone Application setting

and you checked the sqLite tick

Bildschirmfoto 2012-02-14 um 21.54.08.png (45.67 KiB) Viewed 10678 times

this code works in the preopencard handler of the first card of the stack

Code: Select all

local sDatabaseID, sRecordSetID

on preopencard
   local tDatabasePath, tSQLQuery
   
   ## Connect to the database
   ## Save the connection id
   put specialFolderPath("desktop") & "/Fire1/Fire2.sqlite" into tDatabasePath
   
   
   
   if the environment is "mobile" then
            put specialFolderPath("engine") & "/Fire2.sqlite" into tDatabasePath
            put specialFolderPath("documents") & "/Fire2.sqlite" into dataBaseFilePath
                  if there is not a file documentFilePath then
                           put specialFolderPath("engine") & "/Fire2.sqlite" into engineFilePath
                           put URL ("binfile:" & engineFilePath) into URL ("binfile:" & dataBaseFilePath)
                  end if
      else
            put specialFolderPath("desktop") & "/Fire1/Fire2.sqlite" into tDatabasePath
      end if
   
   
   put revOpenDatabase("sqlite",tDatabasePath,,,,) into sDatabaseID
   
   ## Query all details
   ## Save the recordset id
   put "SELECT * from contact_details" into tSQLQuery
   put revQueryDatabase(sDatabaseID,tSQLQuery) into sRecordSetID   
   
   ## Clear the fields
   put empty into field "name"
   put empty into field "email"
   
   ## Display the first record
   displayRecord
end preopencard

It actually copies the Fire2.sqlite file from the engine folder to the documents folder on the Simulator. Uses the desktop folder on the Mac in the IDE.

It should work for you also.

Anything beyond this that has to do with sqLite I am no help at all.

Kind regards

Bernd

[Knightlite]

Bernd you're a genius!!! That worked perfectly! Thank you so much for taking the time to help me and to create such a helpful explanation.

I really appreciate your help.

Again thank you so much.

Jim