Hello,Hi all,
I wanna store the data in both internet and intranet database.
scenario:
Now, we enter the user details, It will be stored in local database. And when we turn on the internet, the stored local database data will automatically store in the internet database.
otherwise when we work on offline the entered data will be stored on local database.
Is there any tutorials?
Anybody Help?
Regards
Praveen
Sub Process_Globals
Dim Sql1 As SQL
Dim LinkWS As String
Dim Timer1 As Timer
End Sub
Sub Service_Create
Sql1.Initialize(File.DirInternal,"posdb.db",False)
LinkWS = "Cloud Database Web Service Path"
Timer1.Initialize("Timer1",10000)
Timer1.Enabled = True
End Sub
Sub Timer1_Tick
Try
Timer1.Enabled = False
Check_Local_Data_Sales
Check_Data_Locally_Returns
Catch
Log(LastException)
End Try
End Sub
Sub Check_Local_Data_Sales
Try
Timer1.Enabled = False
Dim c As Cursor
c = Sql1.ExecQuery("select * from SalesOrder")
For i = 0 To c.RowCount -1
c.Position = i
LogColor("Started The Sync - SH",Colors.Blue)
Dim j As HttpJob
Dim str As String
str = "INSERT INTO [SalesOrder] " _
&" ([InvoiceDate] " _
&" ,[TotalAmount],[SalesOrderNotes],[InvTypeID],[AccountID],[SalesmanID] " _
&" ,[DebitID],[CashID],[UserID],[intRegisterID],[SalesOrderIDOffline] ) " _
&" VALUES " _
&" ('"& c.GetString("InvoiceDate") &"' " _
&" ,'"& c.GetString("TotalAmount") &"','Android POS Sales','"& c.GetString("InvTypeID") &"','"& c.GetString("AccountID") &"',0,0,'"& c.GetString("CashID") &"','"& c.GetString("UserID") &"','"& c.GetString("intRegisterID") &"' , '"& c.GetString("SalesOrderID") &"') ; SELECT SCOPE_IDENTITY() as LastHID "
Dim invID As String
invID = c.GetString("SalesOrderID")
If str = "" Then
Else
j.Initialize("j1",Me)
j.PostString(LinkWS,str)
Wait For (j) JobDone(j As HttpJob)
If j.Success Then
Sql1.BeginTransaction
Sql1.ExecNonQuery("delete from SalesOrder where SalesOrderID=" & invID)
Sql1.TransactionSuccessful
Sql1.EndTransaction
'------ Get HttpJob Results
Dim res As String
res = j.GetString
Dim parser As JSONParser
parser.Initialize(res)
Dim Data As List
Data = parser.NextArray
If Data.Size > 0 Then
For i = 0 To Data.Size - 1
Dim m As Map
m = Data.Get(i)
Dim LastHID As String
LastHID = m.Get("LastHID")
Load_Data_LocallySalesD(invID,LastHID)
Next
End If
End If
j.Release
End If
Next
c.Close
Catch
Log(LastException)
End Try
End Sub
Sub Check_Data_Locally_Returns
Try
Dim c As Cursor
c = Sql1.ExecQuery("select * from SalesReturn")
For i = 0 To c.RowCount -1
c.Position = i
LogColor("Started The Sync - RH",Colors.Blue)
Dim j As HttpJob
Dim str As String
str = "INSERT INTO [SalesReturn] " _
&" ([InvoiceDate] " _
&" ,[TotalAmount],[SalesOrderNotes],[InvTypeID],[AccountID],[SalesmanID] " _
&" ,[DebitID],[CashID],[UserID],[intRegisterID],[SalesReturnIDOffline] ) " _
&" VALUES " _
&" ('"& c.GetString("InvoiceDate") &"' " _
&" ,'"& c.GetString("TotalAmount") &"','Android POS Returned Sales','"& c.GetString("InvTypeID") &"','"& c.GetString("AccountID") &"',0,0,'"& c.GetString("CashID") &"','"& c.GetString("UserID") &"','"& c.GetString("intRegisterID") &"' , '"& c.GetString("SalesReturnID") &"') ; SELECT SCOPE_IDENTITY() as LastHID "
If str = "" Then
Else
Dim invID As String
invID = c.GetString("SalesReturnID")
j.Initialize("j1",Me)
j.PostString(LinkWS,str)
Wait For (j) JobDone(j As HttpJob)
If j.Success Then
Sql1.BeginTransaction
Sql1.ExecNonQuery("delete from SalesReturn where SalesReturnID=" & invID)
Sql1.TransactionSuccessful
Sql1.EndTransaction
'------ Get HttpJob Results
Dim res As String
res = j.GetString
Dim parser As JSONParser
parser.Initialize(res)
Dim Data As List
Data = parser.NextArray
If Data.Size > 0 Then
For i = 0 To Data.Size - 1
Dim m As Map
m = Data.Get(i)
Dim LastHID As String
LastHID = m.Get("LastHID")
Load_Data_LocallyReturnsD(invID,LastHID)
Next
End If
End If
j.Release
End If
Next
c.Close
Timer1.Enabled = True
Catch
Log(LastException)
Timer1.Enabled = True
End Try
End Sub
Sub Load_Data_LocallySalesD(InvID As String,LastHID As String)
Try
Dim str As String
Dim c As Cursor
c = Sql1.ExecQuery("select * from SalesOrderD where SalesOrderHID="&InvID)
For i = 0 To c.RowCount -1
c.Position = i
LogColor("Started The Sync - SD",Colors.Blue)
str = str & " INSERT INTO [SalesOrderD] " _
&" ([ItemsID] " _
&" ,[SalesOrderHID] " _
&" ,[ItemQty],[ItemPrice],[Discount] " _
&" ,[TaxSales],[SalesOrderHIDOffline]) " _
&" VALUES " _
&" ('"& c.GetString("ItemsID") &"' " _
&" ,'"& LastHID &"' " _
&" ,'"& c.GetString("ItemQty") &"' " _
&" ,'"& c.GetString("ItemPrice") &"' " _
&" ,'"& c.GetString("Discount") &"' " _
&" ,'"& c.GetString("TaxSales") &"' , '"& InvID &"' ); "
Next
c.close
If str = "" Then
Else
Dim j As HttpJob
j.Initialize("j1",Me)
j.PostString(LinkWS,str)
Wait For (j) JobDone(j As HttpJob)
If j.Success Then
Sql1.BeginTransaction
Sql1.ExecNonQuery("delete from SalesOrderD where SalesOrderHID=" & InvID)
Sql1.TransactionSuccessful
Sql1.EndTransaction
End If
j.Release
End If
Catch
Log(LastException)
End Try
End Sub
Sub Load_Data_LocallyReturnsD(InvID As String,LastHID As String)
Try
Dim str As String
Dim c As Cursor
c = Sql1.ExecQuery("select * from SalesReturnD where SalesReturnHID="&InvID)
For i = 0 To c.RowCount -1
c.Position = i
LogColor("Started The Sync - RD",Colors.Blue)
str = str & " INSERT INTO [SalesReturnD] " _
&" ([ItemsID] " _
&" ,[SalesReturnHID] " _
&" ,[ItemQty],[ItemPrice],[Discount] " _
&" ,[TaxSales],[SalesReturnHIDOffline]) " _
&" VALUES " _
&" ('"& c.GetString("ItemsID") &"' " _
&" ,'"& LastHID &"' " _
&" ,'"& c.GetString("ItemQty") &"' " _
&" ,'"& c.GetString("ItemPrice") &"' " _
&" ,'"& c.GetString("Discount") &"' " _
&" ,'"& c.GetString("TaxSales") &"' , '"& InvID &"' ); "
Next
c.close
If str = "" Then
Else
Dim j As HttpJob
j.Initialize("j1",Me)
j.PostString(LinkWS,str)
Wait For (j) JobDone(j As HttpJob)
If j.Success Then
Sql1.BeginTransaction
Sql1.ExecNonQuery("delete from SalesReturnD where SalesReturnHID=" & InvID)
Sql1.TransactionSuccessful
Sql1.EndTransaction
End If
j.Release
End If
Catch
Log(LastException)
End Try
End Sub
Sub Service_Start (StartingIntent As Intent)
Service.StopAutomaticForeground 'Call this when the background task completes (if there is one)
End Sub
Sub Service_Destroy
Timer1.Enabled = False
End Sub
Great JobHello,
I have been using this technique a lot in some of my apps, Data-Sync method :
below is the code i use in a service and i start the service in main (check first if it's paused = false)
1- First i save the data in a local database (Sqlite)
2- I check if there is an internet available and if it's available then send the data to the cloud database and delete the local database entries that was sent before.
Below an example :
B4X:Sub Process_Globals Dim Sql1 As SQL Dim LinkWS As String Dim Timer1 As Timer End Sub Sub Service_Create Sql1.Initialize(File.DirInternal,"posdb.db",False) LinkWS = "Cloud Database Web Service Path" Timer1.Initialize("Timer1",10000) Timer1.Enabled = True End Sub Sub Timer1_Tick Try Timer1.Enabled = False Check_Local_Data_Sales Check_Data_Locally_Returns Catch Log(LastException) End Try End Sub Sub Check_Local_Data_Sales Try Timer1.Enabled = False Dim c As Cursor c = Sql1.ExecQuery("select * from SalesOrder") For i = 0 To c.RowCount -1 c.Position = i LogColor("Started The Sync - SH",Colors.Blue) Dim j As HttpJob Dim str As String str = "INSERT INTO [SalesOrder] " _ &" ([InvoiceDate] " _ &" ,[TotalAmount],[SalesOrderNotes],[InvTypeID],[AccountID],[SalesmanID] " _ &" ,[DebitID],[CashID],[UserID],[intRegisterID],[SalesOrderIDOffline] ) " _ &" VALUES " _ &" ('"& c.GetString("InvoiceDate") &"' " _ &" ,'"& c.GetString("TotalAmount") &"','Android POS Sales','"& c.GetString("InvTypeID") &"','"& c.GetString("AccountID") &"',0,0,'"& c.GetString("CashID") &"','"& c.GetString("UserID") &"','"& c.GetString("intRegisterID") &"' , '"& c.GetString("SalesOrderID") &"') ; SELECT SCOPE_IDENTITY() as LastHID " Dim invID As String invID = c.GetString("SalesOrderID") If str = "" Then Else j.Initialize("j1",Me) j.PostString(LinkWS,str) Wait For (j) JobDone(j As HttpJob) If j.Success Then Sql1.BeginTransaction Sql1.ExecNonQuery("delete from SalesOrder where SalesOrderID=" & invID) Sql1.TransactionSuccessful Sql1.EndTransaction '------ Get HttpJob Results Dim res As String res = j.GetString Dim parser As JSONParser parser.Initialize(res) Dim Data As List Data = parser.NextArray If Data.Size > 0 Then For i = 0 To Data.Size - 1 Dim m As Map m = Data.Get(i) Dim LastHID As String LastHID = m.Get("LastHID") Load_Data_LocallySalesD(invID,LastHID) Next End If End If j.Release End If Next c.Close Catch Log(LastException) End Try End Sub Sub Check_Data_Locally_Returns Try Dim c As Cursor c = Sql1.ExecQuery("select * from SalesReturn") For i = 0 To c.RowCount -1 c.Position = i LogColor("Started The Sync - RH",Colors.Blue) Dim j As HttpJob Dim str As String str = "INSERT INTO [SalesReturn] " _ &" ([InvoiceDate] " _ &" ,[TotalAmount],[SalesOrderNotes],[InvTypeID],[AccountID],[SalesmanID] " _ &" ,[DebitID],[CashID],[UserID],[intRegisterID],[SalesReturnIDOffline] ) " _ &" VALUES " _ &" ('"& c.GetString("InvoiceDate") &"' " _ &" ,'"& c.GetString("TotalAmount") &"','Android POS Returned Sales','"& c.GetString("InvTypeID") &"','"& c.GetString("AccountID") &"',0,0,'"& c.GetString("CashID") &"','"& c.GetString("UserID") &"','"& c.GetString("intRegisterID") &"' , '"& c.GetString("SalesReturnID") &"') ; SELECT SCOPE_IDENTITY() as LastHID " If str = "" Then Else Dim invID As String invID = c.GetString("SalesReturnID") j.Initialize("j1",Me) j.PostString(LinkWS,str) Wait For (j) JobDone(j As HttpJob) If j.Success Then Sql1.BeginTransaction Sql1.ExecNonQuery("delete from SalesReturn where SalesReturnID=" & invID) Sql1.TransactionSuccessful Sql1.EndTransaction '------ Get HttpJob Results Dim res As String res = j.GetString Dim parser As JSONParser parser.Initialize(res) Dim Data As List Data = parser.NextArray If Data.Size > 0 Then For i = 0 To Data.Size - 1 Dim m As Map m = Data.Get(i) Dim LastHID As String LastHID = m.Get("LastHID") Load_Data_LocallyReturnsD(invID,LastHID) Next End If End If j.Release End If Next c.Close Timer1.Enabled = True Catch Log(LastException) Timer1.Enabled = True End Try End Sub Sub Load_Data_LocallySalesD(InvID As String,LastHID As String) Try Dim str As String Dim c As Cursor c = Sql1.ExecQuery("select * from SalesOrderD where SalesOrderHID="&InvID) For i = 0 To c.RowCount -1 c.Position = i LogColor("Started The Sync - SD",Colors.Blue) str = str & " INSERT INTO [SalesOrderD] " _ &" ([ItemsID] " _ &" ,[SalesOrderHID] " _ &" ,[ItemQty],[ItemPrice],[Discount] " _ &" ,[TaxSales],[SalesOrderHIDOffline]) " _ &" VALUES " _ &" ('"& c.GetString("ItemsID") &"' " _ &" ,'"& LastHID &"' " _ &" ,'"& c.GetString("ItemQty") &"' " _ &" ,'"& c.GetString("ItemPrice") &"' " _ &" ,'"& c.GetString("Discount") &"' " _ &" ,'"& c.GetString("TaxSales") &"' , '"& InvID &"' ); " Next c.close If str = "" Then Else Dim j As HttpJob j.Initialize("j1",Me) j.PostString(LinkWS,str) Wait For (j) JobDone(j As HttpJob) If j.Success Then Sql1.BeginTransaction Sql1.ExecNonQuery("delete from SalesOrderD where SalesOrderHID=" & InvID) Sql1.TransactionSuccessful Sql1.EndTransaction End If j.Release End If Catch Log(LastException) End Try End Sub Sub Load_Data_LocallyReturnsD(InvID As String,LastHID As String) Try Dim str As String Dim c As Cursor c = Sql1.ExecQuery("select * from SalesReturnD where SalesReturnHID="&InvID) For i = 0 To c.RowCount -1 c.Position = i LogColor("Started The Sync - RD",Colors.Blue) str = str & " INSERT INTO [SalesReturnD] " _ &" ([ItemsID] " _ &" ,[SalesReturnHID] " _ &" ,[ItemQty],[ItemPrice],[Discount] " _ &" ,[TaxSales],[SalesReturnHIDOffline]) " _ &" VALUES " _ &" ('"& c.GetString("ItemsID") &"' " _ &" ,'"& LastHID &"' " _ &" ,'"& c.GetString("ItemQty") &"' " _ &" ,'"& c.GetString("ItemPrice") &"' " _ &" ,'"& c.GetString("Discount") &"' " _ &" ,'"& c.GetString("TaxSales") &"' , '"& InvID &"' ); " Next c.close If str = "" Then Else Dim j As HttpJob j.Initialize("j1",Me) j.PostString(LinkWS,str) Wait For (j) JobDone(j As HttpJob) If j.Success Then Sql1.BeginTransaction Sql1.ExecNonQuery("delete from SalesReturnD where SalesReturnHID=" & InvID) Sql1.TransactionSuccessful Sql1.EndTransaction End If j.Release End If Catch Log(LastException) End Try End Sub Sub Service_Start (StartingIntent As Intent) Service.StopAutomaticForeground 'Call this when the background task completes (if there is one) End Sub Sub Service_Destroy Timer1.Enabled = False End Sub
Hello sir,Hello,
I have been using this technique a lot in some of my apps, Data-Sync method :
below is the code i use in a service and i start the service in main (check first if it's paused = false)
1- First i save the data in a local database (Sqlite)
2- I check if there is an internet available and if it's available then send the data to the cloud database and delete the local database entries that was sent before.
Below an example :
B4X:Sub Process_Globals Dim Sql1 As SQL Dim LinkWS As String Dim Timer1 As Timer End Sub Sub Service_Create Sql1.Initialize(File.DirInternal,"posdb.db",False) LinkWS = "Cloud Database Web Service Path" Timer1.Initialize("Timer1",10000) Timer1.Enabled = True End Sub Sub Timer1_Tick Try Timer1.Enabled = False Check_Local_Data_Sales Check_Data_Locally_Returns Catch Log(LastException) End Try End Sub Sub Check_Local_Data_Sales Try Timer1.Enabled = False Dim c As Cursor c = Sql1.ExecQuery("select * from SalesOrder") For i = 0 To c.RowCount -1 c.Position = i LogColor("Started The Sync - SH",Colors.Blue) Dim j As HttpJob Dim str As String str = "INSERT INTO [SalesOrder] " _ &" ([InvoiceDate] " _ &" ,[TotalAmount],[SalesOrderNotes],[InvTypeID],[AccountID],[SalesmanID] " _ &" ,[DebitID],[CashID],[UserID],[intRegisterID],[SalesOrderIDOffline] ) " _ &" VALUES " _ &" ('"& c.GetString("InvoiceDate") &"' " _ &" ,'"& c.GetString("TotalAmount") &"','Android POS Sales','"& c.GetString("InvTypeID") &"','"& c.GetString("AccountID") &"',0,0,'"& c.GetString("CashID") &"','"& c.GetString("UserID") &"','"& c.GetString("intRegisterID") &"' , '"& c.GetString("SalesOrderID") &"') ; SELECT SCOPE_IDENTITY() as LastHID " Dim invID As String invID = c.GetString("SalesOrderID") If str = "" Then Else j.Initialize("j1",Me) j.PostString(LinkWS,str) Wait For (j) JobDone(j As HttpJob) If j.Success Then Sql1.BeginTransaction Sql1.ExecNonQuery("delete from SalesOrder where SalesOrderID=" & invID) Sql1.TransactionSuccessful Sql1.EndTransaction '------ Get HttpJob Results Dim res As String res = j.GetString Dim parser As JSONParser parser.Initialize(res) Dim Data As List Data = parser.NextArray If Data.Size > 0 Then For i = 0 To Data.Size - 1 Dim m As Map m = Data.Get(i) Dim LastHID As String LastHID = m.Get("LastHID") Load_Data_LocallySalesD(invID,LastHID) Next End If End If j.Release End If Next c.Close Catch Log(LastException) End Try End Sub Sub Check_Data_Locally_Returns Try Dim c As Cursor c = Sql1.ExecQuery("select * from SalesReturn") For i = 0 To c.RowCount -1 c.Position = i LogColor("Started The Sync - RH",Colors.Blue) Dim j As HttpJob Dim str As String str = "INSERT INTO [SalesReturn] " _ &" ([InvoiceDate] " _ &" ,[TotalAmount],[SalesOrderNotes],[InvTypeID],[AccountID],[SalesmanID] " _ &" ,[DebitID],[CashID],[UserID],[intRegisterID],[SalesReturnIDOffline] ) " _ &" VALUES " _ &" ('"& c.GetString("InvoiceDate") &"' " _ &" ,'"& c.GetString("TotalAmount") &"','Android POS Returned Sales','"& c.GetString("InvTypeID") &"','"& c.GetString("AccountID") &"',0,0,'"& c.GetString("CashID") &"','"& c.GetString("UserID") &"','"& c.GetString("intRegisterID") &"' , '"& c.GetString("SalesReturnID") &"') ; SELECT SCOPE_IDENTITY() as LastHID " If str = "" Then Else Dim invID As String invID = c.GetString("SalesReturnID") j.Initialize("j1",Me) j.PostString(LinkWS,str) Wait For (j) JobDone(j As HttpJob) If j.Success Then Sql1.BeginTransaction Sql1.ExecNonQuery("delete from SalesReturn where SalesReturnID=" & invID) Sql1.TransactionSuccessful Sql1.EndTransaction '------ Get HttpJob Results Dim res As String res = j.GetString Dim parser As JSONParser parser.Initialize(res) Dim Data As List Data = parser.NextArray If Data.Size > 0 Then For i = 0 To Data.Size - 1 Dim m As Map m = Data.Get(i) Dim LastHID As String LastHID = m.Get("LastHID") Load_Data_LocallyReturnsD(invID,LastHID) Next End If End If j.Release End If Next c.Close Timer1.Enabled = True Catch Log(LastException) Timer1.Enabled = True End Try End Sub Sub Load_Data_LocallySalesD(InvID As String,LastHID As String) Try Dim str As String Dim c As Cursor c = Sql1.ExecQuery("select * from SalesOrderD where SalesOrderHID="&InvID) For i = 0 To c.RowCount -1 c.Position = i LogColor("Started The Sync - SD",Colors.Blue) str = str & " INSERT INTO [SalesOrderD] " _ &" ([ItemsID] " _ &" ,[SalesOrderHID] " _ &" ,[ItemQty],[ItemPrice],[Discount] " _ &" ,[TaxSales],[SalesOrderHIDOffline]) " _ &" VALUES " _ &" ('"& c.GetString("ItemsID") &"' " _ &" ,'"& LastHID &"' " _ &" ,'"& c.GetString("ItemQty") &"' " _ &" ,'"& c.GetString("ItemPrice") &"' " _ &" ,'"& c.GetString("Discount") &"' " _ &" ,'"& c.GetString("TaxSales") &"' , '"& InvID &"' ); " Next c.close If str = "" Then Else Dim j As HttpJob j.Initialize("j1",Me) j.PostString(LinkWS,str) Wait For (j) JobDone(j As HttpJob) If j.Success Then Sql1.BeginTransaction Sql1.ExecNonQuery("delete from SalesOrderD where SalesOrderHID=" & InvID) Sql1.TransactionSuccessful Sql1.EndTransaction End If j.Release End If Catch Log(LastException) End Try End Sub Sub Load_Data_LocallyReturnsD(InvID As String,LastHID As String) Try Dim str As String Dim c As Cursor c = Sql1.ExecQuery("select * from SalesReturnD where SalesReturnHID="&InvID) For i = 0 To c.RowCount -1 c.Position = i LogColor("Started The Sync - RD",Colors.Blue) str = str & " INSERT INTO [SalesReturnD] " _ &" ([ItemsID] " _ &" ,[SalesReturnHID] " _ &" ,[ItemQty],[ItemPrice],[Discount] " _ &" ,[TaxSales],[SalesReturnHIDOffline]) " _ &" VALUES " _ &" ('"& c.GetString("ItemsID") &"' " _ &" ,'"& LastHID &"' " _ &" ,'"& c.GetString("ItemQty") &"' " _ &" ,'"& c.GetString("ItemPrice") &"' " _ &" ,'"& c.GetString("Discount") &"' " _ &" ,'"& c.GetString("TaxSales") &"' , '"& InvID &"' ); " Next c.close If str = "" Then Else Dim j As HttpJob j.Initialize("j1",Me) j.PostString(LinkWS,str) Wait For (j) JobDone(j As HttpJob) If j.Success Then Sql1.BeginTransaction Sql1.ExecNonQuery("delete from SalesReturnD where SalesReturnHID=" & InvID) Sql1.TransactionSuccessful Sql1.EndTransaction End If j.Release End If Catch Log(LastException) End Try End Sub Sub Service_Start (StartingIntent As Intent) Service.StopAutomaticForeground 'Call this when the background task completes (if there is one) End Sub Sub Service_Destroy Timer1.Enabled = False End Sub
The server side is built in Microsoft ASP.net webservice + Microsoft SQL DatabaseHello sir,
Thanks for your information. It looks great.
What about server side code? Did you use JRDC2?
Can I have your server side code?
Thanks
Praveen
We use cookies and similar technologies for the following purposes:
Do you accept cookies and these technologies?
We use cookies and similar technologies for the following purposes:
Do you accept cookies and these technologies?