Android Question JDBC Going round In Circles!

[gbzrope]

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:

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

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?

 

[DonManfred]

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

 

[DonManfred]

however as soon as I click on the cmdUpdateDB button it just hangs!

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

Where is the FULL error?

 

[gbzrope]

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

Where is the FULL error?

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!

 

[DonManfred]

There is no error!

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

 

[gbzrope]

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

It does not get that far!
Click on the cmdUpdateDB button, goes to the line:

Wait For (CallSub(RoadsJDBC, "GetNewJobs")) Complete (Result As Boolean)

hit f8 to step on and nothing happens!

 

[Erel]

Hard to say without seeing the project.

 

[gbzrope]

Hard to say without seeing the project.

It is a big project, nine screen, 3 code modules ands 2 service modules, here is the relevant 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 String) As Boolean
    Return True
End Sub

Sub Service_Destroy

End Sub

 

[gbzrope]

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.