offline sync method for mySQL tables
Scott Ferguson
*** DRAFT ***
If your App Inventor app can modify an online data table, you may wish
to add the ability to temporarily store requested changes offline in the
event that an internet connection is temporarily unavailable.
These
example snippets are used in an app that INSERTs rows in a mySQL table
and are designed to temporarily store mySQL queries in TinyDB until
until the internet is available, then upload those saved queries.
(I have attempted to handle any anticipated potential error conditions, but if you see something that I have not accounted for please let me know so I can correct the blocks.)
Global variables and Screen1.Initialize:
uploading_saved_queries is a logic variable to tell the error handling blocks whether mySQL queries are currently stored in TinyDB1 waiting to be uploaded.
unsaved_queries temporarily holds the list of unsaved queries stored in TinyDB1.
Screen1.Initialize contains blocks to determine if the TinyDB1 tag unsynced_queries contains a list of -- unsaved queries.
If so, the uploading_saved_queries value is set to true and sync_clk.Timer Enabled to attempt to upload each stored query.
If there are no more saved queries then the TinyDB1 tag unsynced_queries will be set to empty text and the uploading_saved_queries variable set to false in order to tell the Web1.GotText block and the Screen1.ErrorOccurred blocks that in this case there were either no queries to save or that they saved successfully.
If there are more saved queries, attempt to upload the first one in the list using the send_query procedure.
If the query was uploaded successfully, then the sync_clk.Timer will continue to execute otherwise it will be disabled in the sql_web.GotText or the Screen1.ErrorOccurred block.
The send_query block sends the values needed to gain permission to INSERT a row in the table and the current saved query using the sql_web.Get block.
Since these values are passed as arguments attached to the URL of a PHP script, they are encoded.
If the Get block was successful in sending the INSERT row request, then the sql_web.GotText block will return a 200 responseCode and 'OK' responseContent.
In that case, if uploading_saved_queries then delete the first item in the list otherwise display a confirmation message.
If anything else is returned such as 'server busy' then if uploading_saved_queries has not completed, then sync_clk.Timer will be disabled otherwise display a confirmation message add the query to the unsaved_queries list (an attempt to upload the saved queries will be made at the start of the next session)
If the internet is not available when the sql_web.Get block is executed, the Screen1.ErrorOccurred block will be triggered.
The error returned will be 1101 for 'no response from server' in that case.
But regardless of the error, if the uploading_saved_queries is true then the timer should be disabled otherwise the query must be added to the list and a confirmation message displayed.
END