Converting MySQl to SQLite

[kenroy.roach]

This is my first attempt at a LiveCode app. The app is a company directory app. I wantthe users to be able sync on open (if connections settings permit) but primarily operate in offline mode. I've gone through all the tutorials with databases as well as forum topics and I am aware that I cannot pull from variable holding the tab an cr data from the MySQL and put it into the SQLite. The MySQL is working; the SQLite database is created but how do I parse the data from MySQL to put it into the SQLite database?

[Mikey]

This is similar to something we are doing in multiple apps, but ours is a little more complicated, because we have several DBMS's that we are using on multiple platforms. Our process runs at least once per hour, but you can vary the time to meet your needs. You will have to use the intermediate step of exporting the data from your mySQL database, then putting it in a shared data store, where the mobile apps can access it and put the data into the SQLite database (and the mobiles have to be able to go the other way, too, putting their data in the shared location to update the mySQL database). We use a variety of means to do this, including dropbox, box, google docs, and some others.

[kenroy.roach]

Thanks so much Mikey I was figuring I may have to go that route. But get this In messing around this weekend I stumbled across a way that seems to work without exporting to a file. Not sure if this is recommended but here's the code.

--get data from MySQL database
put "directory" into tTableName
put "SELECT * FROM " & tTableName into tSQL
put revDataFromQuery(tab, cr, gConnectionID, tSQL) into tData

-- check the result and display the data or an error message

if item 1 of tData = "revdberr" then

answer error "There was a problem querying the database:" & cr & tData

else

--get database ID from SQLite database (previously created)
put getDatabaseID() into gDatabaseID

--replace the characters to make the MYSQL imported data readable to the SQLite insert query.
replace cr with return in tData
replace tab with "','" in tData --comma inside single quotes inside double quotes to mimic regular insert query format

--insert all the data in all fields line by line
repeat for each line temp in tData
put "INSERT into directory VALUES ('"&temp &"');" after tSQL
end repeat
revExecuteSQL gDatabaseID, tSQL

--display results from SQLite database in field just to confirm results
put "SELECT * from directorylite" into tSQL2
put revDataFromQuery(tab,return,gDatabaseID,tSQL2) into field "dataarea"

It works pretty cleanly. Since I'm testing with a small database (500 records) its very quick, I'm not sure how long it would take with big data. Hope this helps anyone else trying to do this.

[Klaus]

Hi Kenroach,

Code: Select all

...
## Unneccessary:
## replace cr with return in tData
...

CR is a synonym for RETURN in Livecode!


Best

Klaus

[kenroy.roach]

Thanks for that Klaus. I should also mention that your data has to be sanitized for this to work. If you have data like mikey's (with the apostrophe) it breaks down.

[Mikey]

Right. Normally you would use parameters, anyway, to avoid sql injection and other bad things that can happen.