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 using JdbcSQL to connect to remote MySQL database
Here is the code I used to do the above task.
I checked Log(Batch) and I got the below output
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 ?
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
I am using JdbcSQL to connect to remote MySQL database
Here is the code I used to do the above task.
B4X:
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
B4X:
(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' ) ]
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 ?