Save records to SQLite and MySQL Databases Simultaneously.

[Mahares]

I have an app where immediately after I save the record (about 50 fields) to a local SQLite table on the device, the record is also saved to a remote MySQL database in the website when internet is available. When there is no internet connection the record is not saved to MySQL. How do I keep track of that record not saved to MySQL and send it later when internet is available? I would like to hear few options that were tried and were successful.
Thank you immensely.

 

[Erel]

Add a "date created" field to each record. Also store in a file or the database the last successful upload time. Every x minutes check whether there are any new records in the database and try to upload them. If upload is successful then you should update the last successful upload time.

Make sure that your server doesn't add the same record twice if it already exists.

 

[aklisiewicz]

I wonder how would you match up UID. Every table neds an ID (unique and usually autonumbered). If I create record with ID 27 locally on SQLite, then I want to upload that same record to MySQL thie ID=27 might be already taken (on the MySQL end). another wqords it will require some extra procedure to renumber IDs on SQLite and MySQL to mach on both ends. Of course if the MySQL database is not used by other devices or other users this is irrelevant, but I assume this is scenario in which I am interested to.
I would like to have a remote MySQL database and be able to do the same, with the addition to have several users and devices to access CRUD on MySQL either locally (i.e.LAN) or remotely (from Android device).


Arthur