Data synchronizing

badal405

Member
Licensed User
Longtime User
Dear Erel/ Klaus :sign0085:
I badly needed the solution for data synchronizing from sqllite to web db like mysql. I got a library from the forum called QCDBSync. As i understand from that is it can only synchronize data from webdb(mysql) to local db(sqllite). I didn't find any option to do vice versa. I am desperately looking for the solution.
I was thinking to use HttpJob and HttpUtils2Service for synchronizing.
Is it possible to execute synchronizing by HttpJob and HttpUtils2Service?
Let me explain in details.

I have a table in sqllite called for example "AA". The same table structure
also exist in mysql too.
In the AA table i have around 50data. All this data i want to transfer to mysql table. So what i am planning to do like i will make query from sqllite.
and will run a loop. And inside the loop i will pass the data by using HttpJob and HttpUtils2Service.
Is it feasible?
Waiting for your quick reply.
Thanks in advance for your cooperation.
 

badal405

Member
Licensed User
Longtime User
It is feasible. You can also add a timestamp column to your data and then only upload / download the new data.

B4X:
InsUrl="http://xxx.com/sdi_sync.php"
cCursor=dbSQL.ExecQuery("Select * from AA where syn=1")
ProgressDialogShow2("Data synchronizing. Please wait...", False)
Dim values(ccursor.ColumnCount*2) As String
For j=0 To cCursor.RowCount-1
    cCursor.Position=j
    l=k
    For k=0 To cCursor.ColumnCount-1

         values(l)= cCursor.GetColumnName(k)
         l=l+1
         values(l)= cCursor.GetString2(k)
         l=l+1
    Next
    l=0
    k=0
    job.Initialize("Synchronize", Me)
    job.Download2(InsUrl,Array As String("action","add",values(0),values(1),values(2 ),values(3),values(4),values(5),values(6),values(7  ),values(8),values(9),values(10),values(11),values (12),values(13),values(14),values(15),values(16),v alues(17),values(18),values(19),values(20),values( 21),values(22),values(23),values(24),values(25),va lues(26),values(27),values(28),values(29),values(3 0),values(31),values(32),values(33),values(34),val ues(35),values(36),values(37),values(38),values(39 ),values(40),values(41),values(42),values(43),valu es(44),values(45),values(46),values(47),values(48) ,values(49),values(50),values(51) ))
Next
 
Last edited:
Upvote 0

sorex

Expert
Licensed User
Longtime User
I tend to use timestamps aswell but I noticed these issues before (not always Android related tho)

webserver time is behind/ahead 20 minutes or more (not all providers seems to be able to set their ntp stuff right)

when traveling the phone might adjust time automatically when getting connected to a new provider/carrier

the ToSync option mentioned here is an option (although it's a one way sync) but what if you add a new customer and want to fill in some data when it's not sync'ed yet? (I mean like adding a customer contact, or customer support ticket that also creates other records based on the customer id)

will you give it the temp id from the android sqlite and replace it when you add the customer and then the additional records to the remote mysql?

to do this you'll need to do your inserts in a specific order or you'll mess everything up, especially when more than 1 people is working in that database.
 
Last edited:
Upvote 0

badal405

Member
Licensed User
Longtime User
It is feasible. You can also add a timestamp column to your data and then only upload / download the new data.

B4X:
InsUrl="http://xxx.com/sdi_sync.php"
cCursor=dbSQL.ExecQuery("Select * from AA where syn=1")
ProgressDialogShow2("Data synchronizing. Please wait...", False)
Dim values(ccursor.ColumnCount*2) As String
For j=0 To cCursor.RowCount-1
    cCursor.Position=j
    l=k
    For k=0 To cCursor.ColumnCount-1

         values(l)= cCursor.GetColumnName(k)
         l=l+1
         values(l)= cCursor.GetString2(k)
         l=l+1
    Next
    l=0
    k=0
    job.Initialize("Synchronize", Me)
    job.Download2(InsUrl,Array As String("action","add",values(0),values(1),values(2 ),values(3),values(4),values(5),values(6),values(7  ),values(8),values(9),values(10),values(11),values (12),values(13),values(14),values(15),values(16),v alues(17),values(18),values(19),values(20),values( 21),values(22),values(23),values(24),values(25),va lues(26),values(27),values(28),values(29),values(3 0),values(31),values(32),values(33),values(34),val ues(35),values(36),values(37),values(38),values(39 ),values(40),values(41),values(42),values(43),valu es(44),values(45),values(46),values(47),values(48) ,values(49),values(50),values(51) ))
Next

Dear Erel,
The above code is not working properly. As i mentioned before that it is only inserting the last record for number loop(For exmaple, same rows 50 times). I was waiting for your reply but noticed that i just published the code only and didn't write anything.
I debug the code line by line.
B4X:
job.Download2(InsUrl,Array As String("action","add",values(0),values(1),values(2),values(3),values(4),values(5),values(6),values(7),values(8),values(9),values(10),values(11),values(12),values(13),values(14),values(15),values(16),values(17),values(18),values(19),values(20),values(21),values(22),values(23),values(24),values(25),values(26),values(27),values(28),values(29),values(30),values(31),values(32),values(33),values(34),values(35),values(36),values(37),values(38),values(39),values(40),values(41),values(42),values(43),values(44),values(45),values(46),values(47),values(48),values(49),values(50),values(51) ))
It enter to do the Download2 function and and reach to the end of the function.
B4X:
Public Sub Download2(Link As String, Parameters() As String)
   mLink = Link
   Dim sb As StringBuilder
   sb.Initialize
   sb.Append(Link)
   If Parameters.Length > 0 Then sb.Append("?")
   Dim su As StringUtils
   For i = 0 To Parameters.Length - 1 Step 2
      If i >=0 Then sb.Append("&")
      sb.Append(su.EncodeUrl(Parameters(i), "UTF8")).Append("=")
      sb.Append(su.EncodeUrl(Parameters(i + 1), "UTF8"))
      
   Next
   Log(sb.ToString)
   req.InitializeGet(sb.ToString)
   CallSubDelayed2(HttpUtils2Service, "SubmitJob", Me)
when it's come to the
B4X:
CallSubDelayed2(HttpUtils2Service, "SubmitJob", Me)
line,
It should enter to the SubmitJob function of HttpUtils2Server.
But when i press F8 the cursor jump to the Main module and call the next loop. After end of the loop it started to sending data to the webdb. When i checked with the web db i found that the same record inserted 50 times.
Need your help to get rid from here.
Waiting for your quick reply.
Thanks and best regards.
 
Upvote 0

Erel

B4X founder
Staff member
Licensed User
Longtime User
You should simplify your code.
Something like:
B4X:
For j=0 To cCursor.RowCount-1
 Dim values As List
 values.Initialize
 values.Add("action")
 values.Add("add")
    cCursor.Position=j
    For k=0 To cCursor.ColumnCount-1
         values.add(cCursor.GetColumnName(k))
         values.Add(cCursor.GetString2(k))
    Next
    job.Initialize("Synchronize", Me)
 dim valuesArr(values.Length) as String
 For i = 0 to values.Size - 1
   valuesArr(i) = values.Get(i)
 Next
    job.Download2(InsUrl, valuesArr)
Next
 
Upvote 0

badal405

Member
Licensed User
Longtime User
You should simplify your code.

Dear Erel,
Thanks for your advice. It's good idea. But the problem is not there. That way also working. The problem is here
B4X:
job.Download2(InsUrl, valuesArr)
It always inserting the last record according to number of loop.
Waiting for your advice regarding this issues.
Thanks and best regards.
 
Upvote 0

badal405

Member
Licensed User
Longtime User
Note sure that I understand. What is the output of Log(Values)?

Suppose table A has 3 rows as below..
ID Name
1 X
2 Y
3 Z
I want to send this three data from app to web database.
So what i did, i ran a loop like..
B4X:
For j=0 To cCursor.RowCount-1
   cCursor.Position=j
        values.add(cCursor.GetColumnName(k))
        values.Add(cCursor.GetString2(k))
   job.Initialize("Synchronize", Me)
   job.Download2(InsUrl,values)
Next
Now the loop is running for 3 times right? And each time Download2 function are calling with 3 different values.
But when i checked with the web database i found it only inserted the last row 3 times as below...
3 Z
3 Z
3 Z
But it should be
1 X
2 Y
3 Z
Hope you understand now.
thanks and regards.
 
Upvote 0

badal405

Member
Licensed User
Longtime User
'0
http://xxx.com/sdi_sync.php?&action...6:05:02&Update_Date=2013-03-10+16:05:02&Syn=1


startService: class b4a.example.httputils2service


sending message to waiting queue of uninitialized activity (submitjob)



'1
http://xxx.com/sdi_sync.php?&action...8:10:37&Update_Date=2013-03-10+16:05:02&Syn=1


startService: class b4a.example.httputils2service


sending message to waiting queue of uninitialized activity (submitjob)



'2
http://xxx.com/sdi_sync.php?&action...5:56:02&Update_Date=2013-03-10+16:05:02&Syn=1


startService: class b4a.example.httputils2service


sending message to waiting queue of uninitialized activity (submitjob)

this is the logs value
 
Upvote 0

mc73

Well-Known Member
Licensed User
Longtime User
So what i did, i ran a loop like..
B4X:
For j=0 To cCursor.RowCount-1
    cCursor.Position=j
        values.add(cCursor.GetColumnName(k))
        values.Add(cCursor.GetString2(k))
    job.Initialize("Synchronize", Me)
    job.Download2(InsUrl,values)
Next

Is this the actual code you are using? It's quite different from the one suggested by Erel, which, by the way, should do its job. Where's the 'k' loop? Why are you giving a list instead of an array? The list should be redimmed in every single step. Just notes, in case this is the actual code. Otherwise, please disregard my message and try posting the actual code.
 
Last edited:
Upvote 0

badal405

Member
Licensed User
Longtime User
Is this the actual code you are using? It's quite different from the one suggested by Erel, which, by the way, should do its job. Where's the 'k' loop? Why are you giving a list instead of an array? The list should be redimmed in every single step. Just notes, in case this is the actual code. Otherwise, please disregard my message and try posting the actual code.

Here below you will find the actual code.
B4X:
Dim job As HttpJob
Dim InsUrl As String
Dim j,k,l As Int
Dim values As List
   InsUrl="http://www.xxx.com/sdi_sync.php"
   cCursor=dbSQL.ExecQuery("Select * from Mod1SecA where syn=1")
   ProgressDialogShow2("Data synchronizing. Please wait...", False)
   job.Initialize("Synchronize", Me)
   For j=0 To cCursor.RowCount-1
      values.Initialize
      values.Add("action")
       values.Add("add")
   
      cCursor.Position=j
      For k=0 To cCursor.ColumnCount-1
         values.Add(cCursor.GetColumnName(k))
         values.Add(cCursor.GetString2(k))

      Next
       Dim valuesArr(values.Size) As String
       For l = 0 To values.Size - 1
         valuesArr(l) = values.Get(l)
       Next
      
                'job.Initialize("Synchronize", Me)
      job.Download2(InsUrl,valuesArr)
      
   Next

It should work but very unfortunately still not working. Need a big hand involvement from b4a expertise.

Best regards.
 
Upvote 0

badal405

Member
Licensed User
Longtime User
I already solve the problem and it's working fine now. Thanks guys for your helping attitude. If anyone need this help feel free to contact me.
Thanks and best regards.
Shaukat
 
Upvote 0
Top