Is it me or is it a bug? (Ghost Function...) Part I

[simon.schvartzman]

I'm developing an App including:

- Sqlite DB
- DataGrid
- Local Notifications

Several strange things are happening that I can't explain / fix for which I will appreciate your help.

Let start from the beginning...

The very simple attached program:

- has One Sqlite dB with Two Tables, Table 1 & Table 2.
- The Tables are almost equal, they have just one column which is different, DateT1 on Table1 and DateT2 on Table2.
- Button "Insert Records" should add one record to each table
- Button "Table1" should retrieve and show Table 1 contentes on the DataGrid
- Likewise Button "Table2" for Table 2

When running on the desktop (Mac):

- Column DateTX only shows its contents after clicking twice on the corresponding button !!!

When running on the simulator (or the actual device):

- Contents never shows up on the datagrid even though records are added to the DB as can easily been seen looking at the dgText contents...

You can see all this with your own eyes by watching it here https://youtu.be/rZJRBoGWUjo

[Klaus]

Hi Simon,

you are accessing your db with this command:

Code: Select all

...
put specialFolderPath("documents") & "/ghost.sqlite" into tDatabasePath
...

But this will ONLY work on your Mac, because specialfolderpath("documents") on a Mac is a completely different folder than on mobile (device or simulator!)!
Despite the fact that the file "ghost.sqlite"" does of course not ecxist in MY documents folder!

So you first need to make sure that you:
1. include your SQLite database file via the "Copy files" tab in the standalone builder settings.
2. Copied that file right after your app starts up to the device/simulator specific specidalfolderpath("documents"), if not yet present!
3. Access the database file from that location.

P.S.
You are opening the database at least two times but never close one of the connections!
That is asking for trouble!


Best

Klaus

[simon.schvartzman]

Hi Klaus, many thanks for your answer...see my comments below

you said

...specialfolderpath("documents") on a Mac is a completely different folder than on mobile (device or simulator!)!

not sure why as according to the Dictionary

On iOS systems, only create files in the "documents", "cache" and "temporary" folders

you said

...the file "ghost.sqlite"" does of course not ecxist..

you are right, but it is created when I click on the "Insert Records" button. By the way I can see the records were in fact added by checking the variable "tData" contents on the retrieveData function. The problem is that I can get tData to be displayed on the DataGrid...

you said

You are opening the database at least two times

Point taken, beginner fault !!!

[Klaus]

Hi Simon,

Hi Klaus, many thanks for your answer...see my comments below

you said

...specialfolderpath("documents") on a Mac is a completely different folder than on mobile (device or simulator!)!

not sure why as according to the Dictionary

On iOS systems, only create files in the "documents", "cache" and "temporary" folders

Same applies to the other mobile platform, Android!
This has to do with "Sandboxing" which means that our apps can only live in its own little world, including their own 1"Documents" folderinside of the application package.

you said

...the file "ghost.sqlite"" does of course not ecxist..

you are right, but it is created when I click on the "Insert Records" button. By the way I can see the records were in fact added by checking the variable "tData" contents on the retrieveData function. The problem is that I can get tData to be displayed on the DataGrid..

Yes, but that is how LC works!
if you issue a "revopendatabase..." and you try to access a database file which is NOT present, then a new and EMPTY datagbase file will be created.

This way you will end with a new and empty database file, like here in my Documents folder , named "ghost.sqlite", which you then fill with the new data in your handler,
which THEN will of course be displayed inside of your datagrid


Best

Klaus

[simon.schvartzman]

Sorry for bother you Klaus but I'm having difficulties to follow your answers.

Can you please give me an example as how should I open/create the file on the iPhone instead of using ...specialfolderpath("documents")...

Many thanks in advance

[Klaus]

Hi Simon,

my pleasure!

Do this:
1. Add your SQLite file via the "Copy files..." TAB in the standalone builder settings.
Then you will find this, and all other added files/folders, here -> specialfolderpath("resources")

2. When your app starts (simulator or device) check if the database file is already there:

Code: Select all

...
put specialfolderpath("resources") & "/ghost.sqlite" into tSourceDB
put specialfolderpath("documents") & "/ghost.sqlite" into tTargetDB

## VERY first app start, so we need to copy the db file to the documents folder, where we have WRITE permission, VERY important!
if there is NOT a file tTargetDB then
   put url ("binfile:" & tSourceDB) into url("binfile:" & tTargetDB)
end if

## Now use that new path to open the file:
put revOpenDatabase("sqlite", tTargetDB,,,,) into tResult
...

3. Done, now use the dB in DOCS folder as desired!


Best

Klaus

[simon.schvartzman]

Many thanks Klaus

[simon.schvartzman]

Dear Klaus, still trying to understand what I'm doing wrong, I have to questions, but feel free to not to answer if you think I'm being to lazy to search for the answers on the DB Docs.

1 - I don't get why do I need to copy the sqLite DB file from the desktop to the device if it can be locally created (on openCard) and populated (button Insert Records)

2 -On the desktop, how to explain the need to click twice in sequence, the same button in order for the DataGrid to properly display all its columns contentes.

Cheers!

[Klaus]

Hi Simon,

1 - I don't get why do I need to copy the sqLite DB file from the desktop to the device if it can be locally created (on openCard) and populated (button Insert Records)

You do not need this explicitely!

I had the impression that your database file already contains data, that's why I advised to copy that file to the users docs folder first.
If you only need an empty database (with no tables, fields and data!) you can of course simply create it with "revopendatabase..."

2 -On the desktop, how to explain the need to click twice in sequence, the same button in order for the DataGrid to properly display all its columns contentes.

What is exactly the problem?
Where do I have to click?


Best

Klaus

[simon.schvartzman]

Thanks Klaus, see explanation below for

What is exactly the problem?
Where do I have to click?

Run the App and click "Insert Records"

You should get this

Untitled 2.jpg (13.14 KiB) Viewed 10420 times

Data Grid gets updated but Column DateT2 is empty!!!

Untitled 3.jpg (17.86 KiB) Viewed 10420 times

If you Click button "Table2" again, then DateT2 gets its content correctly.

Now if you click button "Table1" should see the same thing happening with columns DateT1

Hope is clear enough

[simon.schvartzman]

Klaus, I found the solution, I hope you can explain why...

On the retrieveData function I originally had:

Code: Select all

   
   -- get the data
   put "SELECT * FROM " & tTableName into tSQL
   put revDataFromQuery(tab, cr, sDatabaseID, tSQL) into tData
   set the dgText of group "DataGrid 1" of card "Alerts" to tData
   
   
   -- Get the Column Titles
   put revDatabaseColumnNames (sDatabaseID, tTableName ) into tColumnTitles
   replace comma with return in tColumnTitles
   set the dgProp[ "columns" ]  of group "DataGrid 1" of card "Alerts" to tColumnTitles
  

If I retrieve the Column Titles before the Data then the problem disappears.

Code: Select all

  
   -- Get the Column Titles
   put revDatabaseColumnNames (sDatabaseID, tTableName ) into tColumnTitles
   replace comma with return in tColumnTitles
   set the dgProp[ "columns" ]  of group "DataGrid 1" of card "Alerts" to tColumnTitles
   
    -- get the data
   put "SELECT * FROM " & tTableName into tSQL
   put revDataFromQuery(tab, cr, sDatabaseID, tSQL) into tData
   set the dgText of group "DataGrid 1" of card "Alerts" to tData
   

Does it make sense? Did I miss the instructions on the DOCs?

Now I need to make it work on the device...(it is not...)

Best!

[simon.schvartzman]

Klaus, It seems it is a bug after all!

I just Deleted the DataGrid from my card and then added a "fresh" one with the same name and it worked like a charm both on the Simulator / iPhone exactly the same as in the desktop.

p.s.: I saved a copy of the original faulty stack and will open a bug report.

Many thanks for your help.

[Klaus]

Hi Simon,

must tested and it works as advertised!?

I see data from table 2 immediately in the datagrid!
I even quit and restarted LC, same result/success.


Best

Klaus

[simon.schvartzman]

Just to close the post: bug was reported and confirmed here:

http://quality.livecode.com/show_bug.cgi?id=18057

[Klaus]

Hi Simon,

oh, sorry, I obviously missed the "simulator" part!


Best

Klaus