Updating MySql Server

Harris

Expert
Licensed User
Longtime User
Example:
My ForServer table has numerous records since the last time it connected to the server and sent the list of records.

The issue is ExecuteRemoteQuery (as demonstrated in MySQL tutorial) fires off each record without waiting for a response in JobDone (they come in time... and any order). It performs a select statement and returns a JSON string if the select query was found.

What I wish to achieve is to determine, for each record, if the query returned a match. Seems this is obvious because a JSON result was returned - but for which query sent (cause order is not guaranteed)?

I have read the many posts/pages concerning HttpUtils2 (some posts eluding to an answer) but have yet to find a solution.

If a Job.PostString would wait for a response, then one could deal with the request at hand.

I have introduced at timer that waits for 5 seconds after the request (with DoEvents as not to block) before proceeding to the next record - with mixed results (not shown below).

some code:

B4X:
 Dim c as cursor

  c = Defs.SQL1.ExecQuery("Select * from ForServer WHERE Sent = 0") ' sent = false 
  c.Position = 0
  For i = 0 To c.RowCount -1
    c.Position = i
    ExecuteRemoteQuery("SELECT * FROM Tasks WHERE PID ='"& c.GetLong("PK") & "' AND DT ='" &c.GetLong("DT") &"' AND Comp ='" & c.GetLong("Comp")&"'" ,COUNTRIES_LIST)
  Next
  c.close

Now here is where the fun begins: determine which result we must deal with...(and mark it as already sent before I execute the INSERT method which shall Send all new records to the remote DB)



Wish there was an ExecuteRemoteQueryandWaitForResponse(Wait as milliseconds)


Thanks again... I so stumped
 

Harris

Expert
Licensed User
Longtime User
The solution is to send the first query and then in JobDone send the next one.

That would imply handling two records.
What about ten? Handle the next 9 in JobDone?
JobDone is where we handle the result of the Select request...?

Difficult to understand.
Does anyone have an example?

Thanks
 
Upvote 0

mc73

Well-Known Member
Licensed User
Longtime User
I would go for sending all open requests (from what I understand, it's all about insertions, right?) and then let php handle and reject the matched ones.
 
Upvote 0

Harris

Expert
Licensed User
Longtime User
I would go for sending all open requests (from what I understand, it's all about insertions, right?) and then let php handle and reject the matched ones.

Sure, but this will get wasteful very quickly. Today I sent 10 records, tomorrow another 10 (now 20) since I didn't remove yesterday's since I didn't confirm they existed (and so on..).

In the end, I would like all devices synchronized by the server. All users in the group need to know what needs to be done, what has been done according to the central server. In this model, we shall deliver all new (or changed) records to the server. The server will bundle all table's records that are Status "open" (and for lets say 1 month back) compress and send to the device. The device will empty its' tables and replace each with contents from server.

Anyone have a better / safer / more efficient model of device / server sync? This is just my kick at the cat....

Thanks
 
Upvote 0

abner69

Member
Licensed User
Longtime User
The best way I have found to handle this problem is is that I assign a unique serial number for each record sent for insert/update. Then, I have the php return a status code with then Serial included.

in the JobDone, you will have the Serial Number returned... you just delete the record with the matching Serial Number from the database on your device on success.

...Pablo
 
Upvote 0

mc73

Well-Known Member
Licensed User
Longtime User
Sure, but this will get wasteful very quickly. Today I sent 10 records, tomorrow another 10 (now 20) since I didn't remove yesterday's since I didn't confirm they existed (and so on..).

No. Sending your first 10 records, will get you a successful reply from the server, and you have to update your local db accordingly.
 
Upvote 0

Harris

Expert
Licensed User
Longtime User
The best way I have found to handle this problem is is that I assign a unique serial number for each record sent for insert/update. Then, I have the php return a status code with then Serial included.

in the JobDone, you will have the Serial Number returned... you just delete the record with the matching Serial Number from the database on your device on success.

...Pablo

This sounds great!

This would solve having to query the DB before sending the "new" list of records. I already have it well serialized. My Primary key - (Autoinc, DateTime and CompanyID).

Do you have an example php script I could follow? Not well versed in php... I would be truly grateful.

I used Erel's example in MySQL tutorial and modified it (removed some code) to handle inserts since it was designed for Select queries returning a JSON result.

Any comment on my server sync method?

Thanks kindly.
 
Upvote 0

Harris

Expert
Licensed User
Longtime User
No. Sending your first 10 records, will get you a successful reply from the server, and you have to update your local db accordingly.

I am logging the Job.GetString response but all I see is True. Like the suggestion above, if I could return the PK in the php, I would safely know which record to delete - since the return result is not consistently the order in which they were sent. I saw this when executing the Select list query.

I need to learn php (or be taught) to understand how to deal with this.

Thanks for your guidance.
 
Upvote 0

Harris

Expert
Licensed User
Longtime User
You can check Job.JobName to find out which job has finished.

Ok, I think I get it.
For each Job I submit to ExecuteRemoteQueryQuery As String, JobName As String), I can pass the table Record ID (pkey) as the jobname then resolve (remove the record) in JobDone.
 
Upvote 0
Top