LiveCode Forums.

Hi...

Has anyone got mySQL or sqlite databases working on iOS ?

I have a mySQL database that runs well in the iphone simulator, but crashes on a real device... No problem, I thought... I'll make a sqlite database and run it from within the app...

The sqlite database runs fine on the desktop, but in the simulator throws an error... "revdberr,Database Error: no such table: towns"... I am a little stumped and confused...

If anyone has databases playing nicely, I would appreciate some pointers to how you have gone about getting it to work ... as I would really like to find out why the mySQL db runs in the simulator but falls over on an iphone... and err, why it tells me the table is not present in the sqlite db..

thanks

Dixie

Dixie

I've got it working 99.99999999998% of the time - 3 of my users have received that error, requiring a re-install of the app. I've not been able to work out why and I've put it down to some obscure corruption of the db.

Are you sure your path to the sqlite db is valid on iOS?

Gerry

Gerry..

Yes, I am sure that the path to the sqlite.db is correct as it returns a connection ID... It is good to hear that someone has got it working...
Where have you placed your database file... in the 'engine' or 'documents' folder ?
be well

Dixie

Dixie wrote:Where have you placed your database file... in the 'engine' or 'documents' folder ?

Documents. I copy it over from the engine when the app first launches.

Dixie, I am currently working on a sqlite app as well. I have the app create the sqlite database when it launches which seems to be pretty stable on the iPhone 4 and in the simulator. It has crashed only once on me so far out of probably 30 tests. Not to jack your thread at all but out of curiosity my issue is that I cannot for the life of me get the phone to create a table in the db. It works fine on the pc but as soon as I port it to the phone it won't create a table in the db. Is this similar to your issue?

I am sorry. I must have completely misread your thread....you already have a created db. Just saw that.

Kaubs...

No, I think that I have a different problem to you.. but, you have given me an idea when you mentioned that you are creating the database when the app launches...

But to your problem for a moment... Are you trying to create the db in the 'engine', as that would be a 'no,no'... or are you creating it in the 'documents' folder ?

be well,

Dixie

Certainly in the documents folder. Im using iPhone Explorer to verify the DB is being written as well. Its there just seems my code to create the tables is broken at the moment.

Dixie did you ever fix this issue? I am still working with databases and have run in to some other issues that may interest you.

Kaubs...

Half the problem is sorted...I first set up an sqlite database and ran it in a stack. There was no problem whatsoever... however, when running in the simulator, the database would not play at all, continually saying that the table, in this case 'towns' that I was looking for did not exist.... I have always been lazy and used the sqlManager in Firefox to build sqlite databases and it seems that this was the cause of the problem, as when I create a sqlite database by script then there is no problem and everything is fine...

But SQL databases in liveCode mobile... I set one up and just got it to return the contents of the table 'towns'. As expected it runs in the simulator, returning the requested data and populating a group through which a user could scroll... but I cannot get it to run on an actual device, the app just crashes and I am afraid I do not have a clue as to why.

be well

Dixie

My experience so far:

If I create the database entirely using live code on the device including tables and rows I have no issue populating a data grid based off of what live code has created.

If I create a database (or copy the same database from the device above) and use the copy files feature under Standalone Application Settings then proceed to copy that database on the preopenstack from the engine to the documents folder the .sqlite file is corrupted and contains no content or tables. It may as well be creating a new sqlite database. Strange huh?

Does this make sense?

step
1) create a sqlite db in the document directory with 1 table klanten (customers) and 2 columns.
2) create a scrolling field names FLD_klanten
3) create a button and on mousedown call databasegetklanten
4) make sure the database is in de copyfiles option in the standalone application

notice : some of the code comes from livecode examples.

#card
function databasegetklanten
put getDatabaseID() into tdatabaseID
put "select * from klanten" into tSQL
put revDataFromQuery(tab,return,tdatabaseID,tSQL) into tRecords

if item 1 of trecords = "revdberr" then
answer error "klanten tabel niet gevonden"
exit to top
end if
return tRecords
end databasegetklanten

#stack
local sDatabaseID
on preopenstack
# we openen altijd de database
databaseconnect
end preopenstack

on closestack
databasedisconnect
end closestack

on databasedisconnect
put getDatabaseID() into tdatabaseID
revclosedatabase tdatabaseid
end databasedisconnect

on DatabaseConnect
local tDatabasePath, tDatabaseID
put specialFolderPath("documents") & slash & "wbsotest.dbf" into tdatabasePath

put revOPenDatabase("sqlite",tdatabasePath, , , ,) into tdatabaseID

if tdatabaseid is a number then
answer info "Database wbsotest.dbf gevonden"
setDatabaseID tdatabaseID
else
put empty into tdatabaseid
answer error "WBSOTest bestaat niet"
end if
end databaseConnect

command setDatabaseID pDatabaseID
   put pDatabaseID into sDatabaseID
end setDatabaseID

function getDatabaseID
return sDatabaseID
end getDatabaseID

Hi All,

I too am having sqlite hassles.

I can complete the Sqlite Lesson in LiveCode Mobile Tasks. I can save data (name, email address) to the table (contact_details) in the sqlite database (runrevemails.sqlite). I can also retrieve that data to a datagrid in a LiveCode stack. This works in both the IDE and the iOS simulator.

I can then use Copy Files in Standalone Application Settings to attach this sqlite database to a new mainstack. In the "mobile" setting, I copy this file to specialFolderPath("documents"). In the iOS simulator, I can open this database and it returns the databaseID and returns "SQLITE" when I query revDatabaseType. It returns nothing when I query revDatabaseTableNames. In the iOS simulator, when I attempt to retrieve data ("SELECT * from contact_details"), I get the same error reported by Dixie. revdberror, Database Error: no such table: contact_details

It seems that the table name or all the data is corrupted, even though this sqlite database was created by the LiveCode IDE.

Any new insights? Any suggestions on how to proceed with this? Should we report this to the Quality Control Center as a bug?

Regards,

Jim L.

Just wanted to mention on this thread, if there is something wrong with the path to the dbfile, and you're not manually creating the file, the tables, and shoving the data in, then livecode will create an empty file for you.

Meaning if you try to open a db file that is in /my/expected/path/dbfile.db, but its actually in /my/slightlydifferent/path/dbfile.db it will CREATE /my/expected/path/dbfile.db. There will be no error, this is just the way it works, and it would cause you to receive the table not found errors as described. This also would apply to CASE errors. I think in IOS case does matter, so if the name of the file is actual DBFILE.DB and you try to open dbfile.db, you get the same issue. An empty file created, no tables or data.

Not sure if this applies to the problems, but thought I'd bring it up.

Hi Sturgis,

Thanks for the insights. With that info, I now think that engine is creating a new database, in some of my instances, when it can't find the copied sqlite file. But I think I've fixed that and I've been cautious about case sensitivity and I still get the error, "revdberr ... no such table ..."

I will continue to explore this but I would greatly appreciate any additional perspectives.

Thanks,

Jim L.