sqlite & mySql hassles...

[Klaus]

Hi Jim,

I would add a quick check to your handler before I try to open the database for the first time:
...
if there is NOT a file "path to your DB file here" then
answer "DB not present!" & CR & "Will create a new one!" with "Oh DAMN!"
end if
...
So you can at least to be sure if copying the file to the device had been successful or not
and if a new and empty database will be created.


Best

Klaus

[Kaubs]

Some great tools that have helped me get past these errors are iPhone Explorer and FireFox's sqlite db explorer add-on. Are you using the simulator or a real iPhone to test?

My point is that you can verify visually what the iPhone is seeing as far as the file goes using the sqlite db explorer add-on to make sure that you are not creating a blank file. This was the issue I had and it was super frustrating for about 3 days.

[James Little]

Hi Klaus & Kaubs,

Thanks for your suggestions. They were very helpful.

It is now WORKING ... both on the simulator and iPhone. I can even modify the runrevemails.sqlite file with a 3rd party SQLite browser then use Copy Files in the Standalone Application Settings to add it to my app then run the app in the simulator and read data from & store data to the sqlite file.

I now need to eat some humble pie. I found that I had mistyped one variable name as "tDatabaseID", rather than "tDatabasePath". This in addition to not understanding that LiveCode will create a new sqlite file, if it can't find the intended file, seemed to have been my major problems.

As Kaubs suggested, I tried iPhone Explorer and that was very helpful to examine sqlite files within the app.

Along the way, I also discovered "Sqlite Database Browser 2.0 beta1" from SourceForge. It's public domain and easy to use; I'm on a Mac. It seems to work with LiveCode, based on my initial testing, as a basic SQLite browser.

Regards,

Jim L.

[Jellicle]

I use Navicat SQLite (http://www.navicat.com) on OS X to manage SQLite databases - I've found it to be more capable and stable than all the freeware apps I've tried.

Gerry

[James Little]

Another Gotcha ... sqlite file names are CASE SENSITIVE in iOS.

I can confirm this case sensitivity issue, as Sturgis warned in earlier post on this thread.

I use Copy Files in the Standalone Application Settings to copy a sqlite file ("mytest.sqLite") to the iOS build or to the simulator. In the application script to load the database I write: put specialFolderPath("documents") & "/mytest.sqlite" into tDatabasePath

This fails both in the simulator and on the iPhone because of the upper case "L" in the copied file and lower case "l" in the script. Instead, the app creates a new sqlite database file with a lower case "l".

HTH,

Jim L.

[FireWorx]

I can't figure out why the sqlite database won't copy over with data in it. It copies over from engine to documents folder but the same query that works in the IDE will not pull data from the sqlite database from within IOS simulator. Any ideas? I have done quiet a bit of error checking and no luck.

global sDatabaseID
-------------------------------- Database Scripts Below -------------------------------
on copyMyFilesOver
put specialFolderPath("documents") & "/mydatabase.sqlite" into TFilePath
 put specialFolderPath("engine") & "/mydatabase.sqlite" into engineFilePath
if there is a file engineFilePath then
answer "The database was found in the engine folder" -- WE ARE GOOD HERE
else
answer "oops no file found in the engine folder guess you will get a blank database."
end if
if there is not a file TFilePath then
answer "time to copy over the database to the docs folder" -- WE ARE GOOD HERE
put URL ("binfile:" & engineFilePath) into URL ("binfile:" & TFilePath)
answer "I think we are done" -- WE ARE GOOD HERE
end if
end copyMyFilesOver

command databaseConnect
if the environment is "mobile" then
put specialFolderPath("documents") & "/mydatabase.sqlite" into tDatabasePath
else
put "/Users/fireworxsoftware/Desktop/mergMK-2/mydatabase.sqlite" into tDatabasePath -- WE ARE GOOD HERE IN THE IDE
end if
put revOpenDatabase("sqlite", tDatabasePath, , , , ) into sDatabaseID
end databaseConnect

command databaseGetData
answer "getting hydrant info"
put "SELECT * from APTOS" into tSQL -- WE ARE GOOD HERE RETURNS RECORDS IN THE IDE BUT NOT IN THE IOS SIMULATOR
put revDataFromQuery(tab,return,sDatabaseID,tSQL) into tRecords
answer tRecords
end databaseGetData

[SparkOut]

I'm guessing that the dB file you are copying from the engine folder does not contain any records. When did you import that file? If it was early on in the development before you made your data tests in the IDE that may account for it. The dB file you are using in the non mobile environment is in the fireworxsoftware/blah/ folder which does not get overwritten. If you import that file into the engine path and compile again does that make a difference?

[terryho]

Hi,

The following is my code, which using in my apps "Livecode dictionary" in both Android Play market and Ios Apps Store. Please note that the following code make you to find your db in Mac, Window, Ios and Android platform. Except for windows unicode path.

Pre requirement for the following code :

1. It need prepare an create a Sqlite database in the stack folder,
2, in the standalone setting -> copy file, to copy the "LcDict.sqlite" file


Global tDBID

on preopenstack
if the platform is "iphone" then
iphoneUseDeviceResolution "true" ,"true"
end if
set the fullscreenmode of this stack to "exactFit"
if the environment is mobile then
put specialFolderPath("engine") & "/sys/" into gimgpath
if the platform is "iphone" then
put specialFolderPath("engine") & "/database/LCDict.sqlite" into tDatabasePath
else
 put "/storage/sdcard0/Android/" into tsdpath
if there is a folder tsdpath then
put "/storage/sdcard0/LiveCodeDict/LCDict.Sqlite" into tDatabasePath
if there is not a folder "/storage/sdcard0/LiveCodeDict/" then
create folder "storage/sdcard0/LiveCodeDict/"
end if
else
put specialFolderPath("documents") & "/LCDict.sqlite" into tDatabasePath
end if
if there is not a file tDatabasePath then
put specialFolderPath("engine") & "/database/LCDict.sqlite" into eDatabasePath
put URL ("binfile:" & eDatabasePath) into URL ("binfile:" & tDatabasePath) 
 end if
end if
put revOpenDatabase("sqlite", tDatabasePath, , , , ) into tDBID
set the pdbID of me to tDBID
else
set itemDel to "/"
put item 1 to -2 of (the effective fileName of this stack) into tCurrentPath
put tCurrentpath & "/LCDict.sqlite" into tDatabasePath
put tCurrentpath & "/sys/" into gimgpath
put revOpenDatabase("sqlite", tDatabasePath, , , , ) into tDBID
set the pdbID of me to tDBID
end if

if tDBID is a number then
//answer tDBID
else
answer "Can not find database"
end if

end preopenstack

Hope this help.

Regards

Terry Ho

[FireWorx]

Thanks for trying to help out. Just for grins I reworked your pre-open stack handler and yes it copies over the database and table configuration OK but no data in the table copies over. I pull data out of the sqlite database (he same one I copied over to the engine folder) and display data from the table in the IDE but when I delete the app from the simulator and reload it and go through the openstack handler It copies it over to the documents folder and I get a good connection ID. and I check and the table does exists on the database in the documents folder but no data is in the table. oh well

global sDatabaseID

on preopenstack
if the environment is mobile then
put specialFolderPath("engine") & "/database/mydatabase.sqlite" into eDatabasePath
put specialFolderPath("documents") & "/mydatabase.sqlite" into tDatabasePath
if there is not a file tDatabasePath then
put URL ("binfile:" & eDatabasePath) into URL ("binfile:" & tDatabasePath) 
 end if
end if
put revOpenDatabase("sqlite", tDatabasePath, , , , ) into sDatabaseID
set the pdbID of me to sDatabaseID

if sDatabaseID is a number then
answer sDatabaseID ## I GET A GOOD CONNECTION ID
else
answer "Can not find database"
end if
end preopenstack


command databaseConnect
if the environment is "mobile" then
put specialFolderPath("documents") & "/mydatabase.sqlite" into tDatabasePath
else
put "/Users/fireworxsoftware/Desktop/mergMK-2/database/mydatabase.sqlite" into tDatabasePath
end if
put revOpenDatabase("sqlite", tDatabasePath, , , , ) into sDatabaseID
end databaseConnect WORKS GOOD BOTH IN THE IDE and THE SIMULATOR

command databaseGetHydrantInfo
if "APTOS" is among the lines of revDatabaseTableNames(sDatabaseID) then
answer "I found the table APTOS" ## FINDS THE TABLE OK IN BOTH THE IDE and THE SIMULATOR
else
answer "no such table"
end if
answer "getting hydrant info"
put "SELECT * from APTOS" into tSQL
put revDataFromQuery(tab,return,sDatabaseID,tSQL) into tRecords
answer tRecords ##RETURNS A LIST OF 1000 RECODS IN THE IDE but EMPTY in the Simulator ;-( SAD FACE
end databaseGetHydrantInfo

Guess I'll just have to import my data into a field and load the sqlite database from within the app. weird !!!
Dave

[Mikey]

While the most straightforward method will be to put the data into a field for transport, there are a couple of other ways to make this happen. IMHO, the EASIEST way is to use Dropbox and either Gugliermo's dropbox library or Monte's dropbox externals to read the files and import the data. You can also put the files on a website and have the app load the url's for the files. Both of these methods work great.

This next method is a little more exotic, but it also works: You can use John Craig's sQuiRt to create a 2d barcode with the relevant data and Monte's MergZX to read the barcode via the camera. Yes, I've used this method, too. While the data it can realistically import is limited to a couple hundred characters, that might be enough for you.

[terryho]

Hi

Please attach your stack. It is hard to find out your problem, under your description. because some miss setting will have different result.

I had use my code to work with many sqlite database in both and Android, IOS, Mac and Win platform.

I had problem on Sqlite database while I use Livecode in windows platform and access the sqlite database in Chinese directory.

Regards

Terry Ho