Android Question Sqlite and Buffering method

microbox · Oct 25, 2014

Hi, I have a situation where I have 10 clients(PC) connected to my app with Sqlite records. I'm using xbees(wireless) for each clients for communications(because of short distance). I'm seeing problems occurring like a delay in response time or garbage data coming from the android device. I placed a small delay whenever clients request for data for process but it needs more improvement. Is there a way to make the process flow like FIFO but with a delay to let the first query finish before another is rendered? Or buffer all data from sqlite so that incoming request need not have to directly call the database when it's busy processing the first inquiry? Please give advice. I apologize if I'm not clear on what I'm saying.

Thanks in advance..

This the code part when an incoming reuqest from clients

Code: Copy

Sub astreams_NewText(Text As String) 
  
    Dim searchDel,DelSrch,mydelaybuffer As String
    Dim dataReceive As String
    dataReceive = ""   
    dataReceive = Text.Trim
If dataReceive.Length  > 1 AND dataReceive.Length < 9  Then
    If dataReceive.Length  > 3 AND dataReceive.Length < 9  Then
        mydelaybuffer = dataReceive.SubString2(0,3)
        If mydelaybuffer = "@D#" OR  mydelaybuffer = "@D$" OR  mydelaybuffer = "@D%" OR  mydelaybuffer = "@D^" OR  mydelaybuffer = "@D&" OR  mydelaybuffer = "@D*" OR  mydelaybuffer = "@D(" OR  mydelaybuffer = "@D)" OR  mydelaybuffer = "@D-" OR  mydelaybuffer  = "@D+" Then
        Delay(150)
        'Delay(150)
        End If
    End If
End If
If DelSrch = "@D#" Then '--Delete from Counter 1               
            Try
            Dim delp As Map
            Dim Resultp As String
            delp.Initialize           
            delp = DBUtils.ExecuteMap(SQL, "SELECT qp FROM Quep WHERE qp = '"& searchDel.trim & "' ",Null)
            Resultp = delp.GetValueAt(0)
            Delay(250)
            DBUtils.ExecuteMap(SQL, "DELETE FROM Quep WHERE qp = '"& searchDel.trim & "' ",Null)
            astreams.Write("DEL@#" & searchDel.trim & Chr(13))
            Catch
            astreams.Write("NF#" & Resultp & searchDel.trim & Chr(13))   
            Log("Eror deleting")
            End Try           
    Else If DelSrch = "@D$" Then '--Delete from Counter 2
            Try
            Dim delp As Map
            Dim Resultp As String
            delp.Initialize           
            delp = DBUtils.ExecuteMap(SQL, "SELECT qp FROM Quep WHERE qp = '"& searchDel.trim & "' ",Null)
            Resultp = delp.GetValueAt(0)
            Delay(250) ' 250
            DBUtils.ExecuteMap(SQL, "DELETE FROM Quep WHERE qp = '"& searchDel.trim & "' ",Null)
            astreams.Write("DEL@$" & searchDel.trim & Chr(13))
            Catch
            astreams.Write("NF$" & Resultp & searchDel.trim & Chr(13))   
            Log("Eror deleting")
            End Try
           
    Else If DelSrch = "@D%" Then '--Delete from Counter 3 
            Try
            Dim delp As Map
            Dim Resultp As String
            delp.Initialize           
            delp = DBUtils.ExecuteMap(SQL, "SELECT qp FROM Quep WHERE qp = '"& searchDel.trim & "' ",Null)
            Resultp = delp.GetValueAt(0)
            Delay(250)
            DBUtils.ExecuteMap(SQL, "DELETE FROM Quep WHERE qp = '"& searchDel.trim & "' ",Null)
            astreams.Write("DEL@%" & searchDel.trim & Chr(13))
            Catch
            astreams.Write("NF%" & Resultp & searchDel.trim & Chr(13))   
            Log("Eror deleting")
            End Try
    Else If DelSrch = "@D^" Then '--Delete from Counter 4
            Try
            Dim delp As Map
            Dim Resultp As String
            delp.Initialize           
            delp = DBUtils.ExecuteMap(SQL, "SELECT qp FROM Quep WHERE qp = '"& searchDel.trim & "' ",Null)
            Resultp = delp.GetValueAt(0)
            Delay(250)
            DBUtils.ExecuteMap(SQL, "DELETE FROM Quep WHERE qp = '"& searchDel.trim & "' ",Null)
            astreams.Write("DEL@^" & searchDel.trim & Chr(13))
            Catch
            astreams.Write("NF^" & Resultp & searchDel.trim & Chr(13))   
            Log("Eror deleting")
            End Try
    Else If DelSrch = "@D&" Then '--Delete from Counter 5
           Try
            Dim delp As Map
            Dim Resultp As String
            delp.Initialize           
            delp = DBUtils.ExecuteMap(SQL, "SELECT qn FROM Quen WHERE qn = '"& searchDel.trim & "' ",Null)
            Resultp = delp.GetValueAt(0)
            Delay(250)
            DBUtils.ExecuteMap(SQL, "DELETE FROM Quen WHERE qn = '"& searchDel.trim & "' ",Null)
            astreams.Write("DEL@&" & searchDel.trim & Chr(13))
            Catch
            astreams.Write("NF&" & Resultp & searchDel.trim & Chr(13))   
            Log("Eror deleting")
            End Try
    Else If DelSrch = "@D*" Then '--Delete from Counter 6
            Try
            Dim delp As Map
            Dim Resultp As String
            delp.Initialize           
            delp = DBUtils.ExecuteMap(SQL, "SELECT qn FROM Quen WHERE qn = '"& searchDel.trim & "' ",Null)
            Resultp = delp.GetValueAt(0)
            Delay(250)
            DBUtils.ExecuteMap(SQL, "DELETE FROM Quen WHERE qn = '"& searchDel.trim & "' ",Null)
            astreams.Write("DEL@*" & searchDel.trim & Chr(13))
            Catch
            astreams.Write("NF*" & Resultp & searchDel.trim & Chr(13))   
            Log("Eror deleting")
            End Try
           
    Else If DelSrch = "@D(" Then '--Delete from Counter 7         
            Try
            Dim delp As Map
            Dim Resultp As String
            delp.Initialize           
            delp = DBUtils.ExecuteMap(SQL, "SELECT qr FROM Quer WHERE qr = '"& searchDel.trim & "' ",Null)
            Resultp = delp.GetValueAt(0)
            Delay(250)
            DBUtils.ExecuteMap(SQL, "DELETE FROM Quer WHERE qr = '"& searchDel.trim & "' ",Null)
            astreams.Write("DEL@(" & searchDel.trim & Chr(13))
            Catch
            astreams.Write("NF(" & Resultp & searchDel.trim & Chr(13))   
            Log("Eror deleting")
            End Try
           
    Else If DelSrch = "@D)" Then '--Delete from Counter 8
            Try
            Dim delp As Map
            Dim Resultp As String
            delp.Initialize           
            delp = DBUtils.ExecuteMap(SQL, "SELECT qr FROM Quer WHERE qr = '"& searchDel.trim & "' ",Null)
            Resultp = delp.GetValueAt(0)
            Delay(250)
            DBUtils.ExecuteMap(SQL, "DELETE FROM Quer WHERE qr = '"& searchDel.trim & "' ",Null)
            astreams.Write("DEL@)" & searchDel.trim & Chr(13))
            Catch
            astreams.Write("NF)" & Resultp & searchDel.trim & Chr(13))   
            Log("Eror deleting")
            End Try
    Else If DelSrch = "@D-" Then '--Delete from Counter 9
            Try
            Dim delp As Map
            Dim Resultp As String
            delp.Initialize           
            delp = DBUtils.ExecuteMap(SQL, "SELECT qc FROM Quec WHERE qc = '"& searchDel.trim & "' ",Null)
            Resultp = delp.GetValueAt(0)
            Delay(250)
            DBUtils.ExecuteMap(SQL, "DELETE FROM Quec WHERE qc = '"& searchDel.trim & "' ",Null)
            astreams.Write("DEL@-" & searchDel.trim & Chr(13))
            Catch
            astreams.Write("NF-" & Resultp & searchDel.trim & Chr(13))   
            Log("Eror deleting")
            End Try
           
    Else If DelSrch = "@D+" Then '--Delete from Counter 10
           Try
            Dim delp As Map
            Dim Resultp As String
            delp.Initialize           
            delp = DBUtils.ExecuteMap(SQL, "SELECT qw FROM Quew WHERE qw = '"& searchDel.trim & "' ",Null)
            Resultp = delp.GetValueAt(0)
            Delay(250)
            DBUtils.ExecuteMap(SQL, "DELETE FROM Quew WHERE qw = '"& searchDel.trim & "' ",Null)
            astreams.Write("DEL@+" & searchDel.trim & Chr(13))
            Catch
            astreams.Write("NF+" & Resultp & searchDel.trim & Chr(13))   
            Log("Eror deleting")
            End Try
End if
End if
End Sub

Erel · Oct 26, 2014

How are you managing the multiple connections?

microbox · Oct 26, 2014

I'm using xbee wireless modules that broadcast their keyword command and gets filtered when entering astreams_NewText function.
examples of keyword format and its meaning
@D# - client1 wants to delete a record
@D$ - client2 wants to delete a record
@% - Client3 wants to query a record
and so on...

I'm not really sure if this is the best way...but right now just use a delay for each query. I need a good algorithm that no matter how fast each clients request they are executed in sequence manner. even if the reply have 2-3 sec delays as long as there no garbage data. Please share your opinion on this..

Thanks in advance.

LucaMs · Oct 26, 2014

Sorry, probably I will create problems rather than give solutions.

I do not know XBees and I go out of topic, but I see too much repeated code.

I would create a module which contains all the SQL queries containing placeholders to be replaced using parameters in some routines.

As for the FIFO, as I said, I do not know XBees and to tell the truth I did not have the patience to read well your code

.

Erel · Oct 26, 2014

Are all of these clients connected to the same socket? Are you using prefix mode?

microbox · Oct 26, 2014

@LucaMs - Thanks for the tip, I will try to re code to improve it as you suggested.
@Erel - The clients are connected similar to usb/serial connection but only wireless.

Thanks for giving the time experts..

Erel · Oct 27, 2014

Are you using the same socket to communicate with all clients? How is it possible?

microbox · Oct 27, 2014

Hi Erel... I don't think I'm using a socket to connect to the sqlite server. I'm sorry if I'm no able to express technically how it's connected. But I'm only using the following code to connect to all clients.

Code: Copy

astreams.Initialize(Me, "astreams", usb.GetInputStream, usb.GetOutputStream)

And using my lengthy/not organized codes above

to filter the incoming request. The following image is how the hardware are connected.

I think I figured it out that maybe, that garbage data is produced when sending from client using vbnet in windows 8.1 caused by usb chip driver issue...but not really sure for now. Need more test, just want to eliminate unwanted data during start up and sometimes during operation.

Android Question Sqlite and Buffering method

microbox

Active Member

Erel

B4X founder

microbox

Active Member

LucaMs

Expert

Erel

B4X founder

microbox

Active Member

Erel

B4X founder

microbox

Active Member

Similar Threads