Android Question JDBC Going round In Circles!

Discussion in 'Android Questions' started by gbzrope, Apr 2, 2019.

Tags:
  1. gbzrope

    gbzrope Member Licensed User

    I have a large data collection app I am developing. It collects "Job Sheets" from a MySQL Server, the users view this and enter a "Daily Return Sheet" which is a list of works done (From a list view) hours worked, mileage plus other info.
    This is then uploaded to the MySQL database.
    I was using http requests and a php script on the server, but it is proving to be problematic, not consistent in responding to requests (the app sends a constructed SQL statement which the php script executes)

    I think that using the jdbc driver and talking directly to the server/database would give me more control and responsiveness.

    But.

    I downloaded the B4A jdbc driver and sample code, modified it to sun a typical query, and it works fine!

    But I have taken this code and am trying to build it into my app, I created a new Service module, and using the same methods and syntax as the (working) example test app, it just hangs.

    the code is called thus:

    Code:
    Sub cmdUpdateDB_Click

    Wait For (CallSub(RoadsJDBC, "GetNewJobs")) Complete (Result As Boolean)
    RoadsJDBC is the name of the service module, and GetNewJobs is a sub within the service



    Code:
    Sub GetNewJobs As ResumableSub
        
    Wait For (Connect) Complete (Success As Boolean)
        
    If Success Then
            
    Try
                sSQL=
    "SELECT * FROM statements_for_client WHERE (Client = " & Main.iUserID & " OR Client = 0) AND ID NOT IN (SELECT ID FROM statements_collected WHERE Client = " &  Main.iUserID & " ) ORDER BY Requested ASC  "
                
    Dim sf As Object = mysql.ExecQueryAsync("mysql", sSQL,Null)
                
    Wait For (sf) mysql_QueryComplete (Success As Boolean, Crsr As JdbcResultSet)
                
    If Success Then
                    
    Do While Crsr.NextRow
                        
    Log(Crsr.GetString("Statement"))
    however as soon as I click on the cmdUpdateDB button it just hangs!

    Help?
     
    Last edited: Apr 2, 2019
  2. DonManfred

    DonManfred Expert Licensed User

    Please use [CODE]code here...[/CODE] tags when posting code.

    [​IMG]
    [​IMG]
    [​IMG]
     
  3. DonManfred

    DonManfred Expert Licensed User

    Do you expect to get help with the info you provided?

    Where is the FULL error?
     
  4. gbzrope

    gbzrope Member Licensed User

    There is no error!
    I put a break point on the first line of cmdUpdateBD_Click code, it breaks, then f8 and it does Nothing!
     
  5. DonManfred

    DonManfred Expert Licensed User

    Remove the try catch block and try again. You are suppressing any error.
     
  6. gbzrope

    gbzrope Member Licensed User

    It does not get that far!
    Click on the cmdUpdateDB button, goes to the line:
    Code:
    Wait For (CallSub(RoadsJDBC, "GetNewJobs")) Complete (Result As Boolean)
    hit f8 to step on and nothing happens!
     
  7. Erel

    Erel Administrator Staff Member Licensed User

    Hard to say without seeing the project.
     
  8. gbzrope

    gbzrope Member Licensed User

    It is a big project, nine screen, 3 code modules ands 2 service modules, here is the relevant code:

    Code:
    '  code from for MainOptions.

    Sub Process_Globals
        
    'These global variables will be declared once when the application starts.
        'These variables can be accessed from all modules.
        Private UpdateDB = "update", JOB_LIST = "jobs", LNKJOBS = "lnkjob", BOQ = "boq", CUST = "cust", JOBCARDS = "jobcards" As String
        
    Private ChargeH ="chargh", GetSQL = "getsql", HEADERID = "headerid", VEHREP = "vehrep", EXPH = "EXPH" As String
        
    Public bMy As Boolean
        
    Dim SQL1 As SQL
        
    Dim Row As Int
        
    Dim Cursor1 As Cursor
        
    Dim CurrentIndex = -1 As Int        ' index of the current entry
        Dim RowNumber = 0 As Int                ' number of rows
        Dim iSecondMan As Int
        
    Dim iVeh As Int
        
    Dim iSelVeh As Int
        
    Dim sDeviceID As String
        
    Dim iLoginID As Int
        
        
    End Sub

    Sub Globals
        
    'These global variables will be redeclared each time the activity is created.
        'These variables can only be accessed from this module.

        
    Dim cmdUpdateDB As Button
        
    Dim cmdDailyVehCheck As Button
        
    Dim cmdListMyJobs As Button
        
    Dim Label1 As Label
        
    Dim spCrewMember As Spinner
        
    Dim sSQL As String
        
    Dim Label3 As Label
        
    Dim spVehicles As Spinner
        
    Dim lblUser As Label
        
    Dim cmdExit As Button
        
    Dim sToday As String
        
    Dim lvwCrew As ListView
        
    Dim iHeader As Int
        
    Dim ProgressBar1 As ProgressBar
    End Sub


    Sub cmdUpdateDB_Click
        
        
    'First - send jobs if exists
        Dim sSQL As String
        
    Dim Cursor1 As Cursor
        
    Dim iID As Int
        
        iID=
    0
        
    ProgressDialogShow("Please wait!")
        
    Try
            sSQL=
    "SELECT QID FROM importlog ORDER BY QID DESC LIMIT 1"
            Cursor1 = SQL1.ExecQuery(sSQL)
            
    If Cursor1.RowCount > 0 Then                        'check if entries exist
                RowNumber = Cursor1.RowCount                    'set the row count variable
                For Row = 0 To RowNumber - 1
                    Cursor1.Position = Row
                    
    Try
                        iID = Cursor1.GetInt(
    "QID")
                    
    Catch
                        iID=
    0
                    
    End Try
                
    Next
            
    End If
            Cursor1.Close
        
    Catch
            iID=
    0
        
    End Try
        ProgressBar1.Visible = 
    True

    '###########Break point on next line, step on and nothing happens
        
        
    Wait For (CallSub(RoadsJDBC, "GetNewJobs")) Complete (Result As Boolean)


        
        ProgressBar1.Visible = 
    False

        
    ProgressDialogHide


    End Sub

        
    '  service module RoadsJDBC:   

    Sub Process_Globals
        
    Public mysql As JdbcSQL
        
    Private driver As String = "com.mysql.jdbc.Driver"
        
        
    Private jdbcUrl As String = "jdbc:mysql://<url>/<databasename>"
        
    Private Username As String = "<username>"
        
    Private Password As String = "<password>"
        
    Private sSQL As String
        
    Private SQL1 As SQL

    End Sub

    Sub Service_Create
        
    'need to disable it as reading from large JdbcResultSet will cause network requests to be sent on the main thread.
        DisableStrictMode
    End Sub

    Sub Service_Start (StartingIntent As Intent)

    End Sub

    Sub DisableStrictMode
        
    Dim jo As JavaObject
        jo.InitializeStatic(
    "android.os.Build.VERSION")
        
    If jo.GetField("SDK_INT") > 9 Then
            
    Dim policy As JavaObject
            policy = policy.InitializeNewInstance(
    "android.os.StrictMode.ThreadPolicy.Builder"Null)
            policy = policy.RunMethodJO(
    "permitAll"Null).RunMethodJO("build"Null)
            
    Dim sm As JavaObject
            sm.InitializeStatic(
    "android.os.StrictMode").RunMethod("setThreadPolicy"Array(policy))
        
    End If
    End Sub

    Sub Connect As ResumableSub
        mysql.InitializeAsync(
    "mysql", driver, jdbcUrl, Username, Password)
        
    Wait For MySQL_Ready (Success As Boolean)
        
    If Success = False Then
            
    ToastMessageShow ("Connection to database has failed, please try again.",False)
            
    Log("Check unfiltered logs for JDBC errors.")
        
    End If
        
    Return Success
    End Sub

    Sub CloseConnection
        mysql.Close
    End Sub

    Sub GetNewJobs As ResumableSub
        
    Wait For (Connect) Complete (Success As Boolean)
        
    If Success Then
            
    Try
                sSQL=
    "SELECT * FROM statements_for_client WHERE (Client = " & Main.iUserID & " OR Client = 0) AND ID NOT IN (SELECT ID FROM statements_collected WHERE Client = " &  Main.iUserID & " ) ORDER BY Requested ASC  "
                
                
    Dim sf As Object = mysql.ExecQueryAsync("mysql", sSQL,Null)
                
    Wait For (sf) mysql_QueryComplete (Success As Boolean, Crsr As JdbcResultSet)
                
    If Success Then
                    
    Do While Crsr.NextRow
                        
    Log(Crsr.GetString("Statement"))
                        
    Do While Crsr.NextRow
                            SQL1.ExecNonQuery(Crsr.GetString(
    "Statement"))
                            sSQL=
    "DELETE FROM importlog"
                            SQL1.ExecNonQuery(sSQL)
                            sSQL=
    "INSERT INTO importlog (QID, Date) VALUES ('" & Crsr.getint("ID") & "', datetime())"
                            SQL1.ExecNonQuery(sSQL)
                            sSQL=
    "INSERT INTO statements_collected(ID, Client, DeviceID) VALUES(" &Crsr.getint("ID") & "," & Main.iUserID & ",'" & MainOptions.sDeviceID & "')"
                            
    Dim sf1 As Object = mysql.ExecQueryAsync("mysql", sSQL,Null)
                            
    Wait For (sf1) mysql_QueryComplete (Success As Boolean)
                            
    If  Success Then
                                
    ToastMessageShow("Update to main database failed",False)
                            
    End If
                        
                        
                        
    Loop
                    
    Loop
                    Crsr.Close
                
    Else
                    
    Log(LastException)
                
    End If
            
    Catch
                Success = 
    False
                
    Log(LastException)
            
    End Try
            CloseConnection
        
    End If
        
    Return Success
    End Sub

    Sub Service_TaskRemoved
        
    'This event will be raised when the user removes the app from the recent apps list.
    End Sub

    'Return true to allow the OS default exceptions handler to handle the uncaught exception.
    Sub Application_Error (Error As Exception, StackTrace As StringAs Boolean
        
    Return True
    End Sub

    Sub Service_Destroy

    End Sub
     
  9. gbzrope

    gbzrope Member Licensed User

    Sorted!
    Very odd - I created a new Service module in the main app, and cut & pasted the code from the example, and this did not work.
    I loaded a copy of the Starter service module into my code and edited the main function in the ListAnimals sub - even changed the name to GetNewJobs - and it worked!

    I have no idea what I have done wrong.
     
Loading...
  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice