Android Question Unknown problem while using ExecNonQueryBatch, JdbcSQL [Solved]

[Anser]

Hi,

I am trying to read a Sqlite Table data from the phone and then trying to write that data to a remote MySQL table.
I am using ExecNonQueryBatch to do a batch insert to remote MySQL server.

The problem that I am facing is that

• I am not getting any error
• ExecNonQueryBatch status is returning True.
• Unfortunately, the data is not written to the remote database

How do you check for any possible errors ?

I am using JdbcSQL to connect to remote MySQL database

Here is the code I used to do the above task.

Sub Globals
    'These global variables will be redeclared each time the activity is created.
    'These variables can only be accessed from this module.    Private cScanDate As String
    Private mysql As JdbcSQL
    
End Sub
Sub Upload_Click

    Dim Batch As List
    Batch.Initialize
    
    Dim MyCurSor As Cursor

    MyCurSor = Starter.Sql1.ExecQuery2("SELECT * FROM local_table , null)

    Dim cSql As String
    For i = 0 To MyCurSor.RowCount -1
        MyCurSor.Position = i
        
        cSql = $"INSERT  INTO remote_table
                  ( Col1, Col2, Col3 )
             VALUES( [Col1Value], '[Col2Value]', '[Col3Value]' ) "$

        cSql = cSql.Replace("[Col1Value]", MyCurSor.GetInt("Col1") )
        cSql = cSql.Replace("[Col2Value]", MyCurSor.GetString("Col2") )
        cSql = cSql.Replace("[Col3Value]", MyCurSor.GetString("Col3") )

        Batch.Add(cSql)
        mysql.AddNonQueryToBatch(cSql,Null)
    Next

    If mysql.IsInitialized = False Then
        Log("not connected, hence reconnecting")
        Wait For (Connect) Complete (Success As Boolean)
        If Success = False Then
            MsgboxAsync("Unable to connect to the Database server", "Failed to upload, check internet connectivity")
            Return
        End If
    End If
    Log("Connected to Remote DB, now about to upload")
                
    Dim SenderFilter As Object = mysql.ExecNonQueryBatch("SQLUpload")
    Wait For (SenderFilter) SQLUpload_NonQueryComplete (Success As Boolean)
    
    Upload_Completed(Success)
    CloseConnection 'Closing the MySQL connection

End Sub

Sub Upload_Completed(Success As Boolean    )
    Log("Inside Upload Completed")
    
    'Log(LastException.Message) 'This line ie LastException is showing error saying Exception not initialized, hence commented
    If Success Then ' I always get the value of Success as True
        MsgboxAsync("Successfully uploaded to remote database","Success")
    Else
        MsgboxAsync("Could not upload the Scanned bill details","Error")
    End If
End Sub

I checked Log(Batch) and I got the below output

(ArrayList) [INSERT  INTO remote_table
                  ( Col1, Col2, Col3 )
                 VALUES( 1, 'MyDataA1', 'MyDataB1' ) , INSERT  INTO remote_table
                  ( Col1, Col2, Col3 )
                 VALUES( 2, 'MyDataA2', 'MyDataB2' ) , INSERT  INTO remote_table
                  ( Col1, Col2, Col3 )
                 VALUES( 3, 'MyDataA3', 'MyDataB3' ) ]

If I directly try the above SQL command on MySQL server after removing the square brackets and (ArrayList) it will throw error because there is a comma between each INSERT statement generated from the List named Batch

Is there any way that I can know what is happening? Is the MySQL server throwing an error? If there is an error then what is the error ?

 

[DonManfred]

cSql = $"INSERT INTO remote_table ( Col1, Col2, Col3 ) VALUES( [Col1Value], '[Col2Value]', '[Col3Value]' ) "$

You forget to close the sql-statements with a ;

cSql = $"INSERT INTO remote_table ( Col1, Col2, Col3 ) VALUES( [Col1Value], '[Col2Value]', '[Col3Value]' );"$

Also not using parametrized queries is a mistake.

 

[Anser]

Yes. I tried adding a ";" at the end of the SQL. Unfortunately, It is not making any difference.

The Log(Batch) now gives the output as given below

(ArrayList) [INSERT  INTO remote_table
                  ( Col1, Col2, Col3 )
                 VALUES( 1, 'MyDataA1', 'MyDataB1' ) ; , INSERT  INTO remote_table
                  ( Col1, Col2, Col3 )
                 VALUES( 2, 'MyDataA2', 'MyDataB2' ) ; , INSERT  INTO remote_table
                  ( Col1, Col2, Col3 )
                 VALUES( 3, 'MyDataA3', 'MyDataB3' ) ;]

 

[aeric]

How do you check for any possible errors ?

Use Try-Catch and Log any LastException.

 

[aeric]

If mysql.IsInitialized = False Then Log("not connected, hence reconnecting") Wait For (Connect) Complete (Success As Boolean) If Success = False Then MsgboxAsync("Unable to connect to the Database server", "Failed to upload, check internet connectivity") Return End If End If Log("Connected to Remote DB, now about to upload")

I don't see this is a good approach to put this code here.

You are calling mysql before the previous initialization.

mysql.AddNonQueryToBatch(cSql,Null)

 

[Anser]

Use Try-Catch and Log any LastException.

I am not getting any error. The data does not appear on the remote DB

        Dim SenderFilter As Object = mysql.ExecNonQueryBatch("SQLUpload")
        Try
            Wait For (SenderFilter) SQLUpload_NonQueryComplete (Success As Boolean)
        Catch
            Log("Catch error exception")
            Log(LastException)
        End Try

I always get the value of the variable Success as True from SQLUpload_NonQueryComplete (Success As Boolean)

 

[Anser]

I don't see this is a good approach to put this code here.

You are calling mysql before the previous initialization.

In the same activity, before calling this routine, I am connecting to MySQL to fetch some data from a table. The connection is already established. I am checking it here just to ensure that the connection is active or not, if not then I will try to connect once again.

 

[aeric]

The connection may be already broken.
I think better make a new connection and close after using it everytime.
After connection is established then only add the query to the mysql object.

 

[Anser]

The connection may be already broken.
I think better make a new connection and close after using it everytime.
After connection is established then only add the query to the mysql object.

Yes. This was the issue.
The connection was closed and I was creating the Batch.

Problem solved. Thank you