Inserting rows works fine, but I can't get BeginTransaction and TransactionSuccessful to make it faster. I've tried updating my SQL DataBase with and without these commands and it takes the same amount of time either way. I update 200 rows and it locks up my program (Not Responding) for about 30 seconds each time.
Also if I stop the program during the Not Responding time, I get a partial write to the DataBase so it doesn't seem to be writing all or none. Can anyone see what I'm doing wrong?
B4X:
Sub SQL_Write_AMT
If SQL1.IsInitialized = False Then 'in case the connection closes
SQL1.Initialize2("com.mysql.jdbc.Driver", "jdbc:mysql://mywebsite/site", "username", "password")
End If
SQL1.BeginTransaction
Try
For i = 0 To AMT_Size-1 '199
SQL1.ExecNonQuery("INSERT IGNORE INTO amt_test_bot SET trade_id = '" & AMT_Trade_ID(i) & "', date_time = '" & AMT_Date_Time(i) & _
"', coin_pair = '" & AMT_CoinPair(i) & "', pri_coin = '" & AMT_Pri_Coin(i) & "', sec_coin = '" & AMT_Sec_Coin(i) & _
"', market_id = '" & AMT_MarketID(i) & "', trade_type = '" & AMT_Trade_Type(i) & "', price = '" & AMT_Trade_Price(i) & _
"', quantity = '" & AMT_Quantity(i) & "', total = '" & AMT_Total(i) & "', price_deviation = '" & Price_Deviation(AMT_MarketID(i)) & _
"', loss_coin = '" & Loss_Coin(AMT_MarketID(i)) & "'")
Next
SQL1.TransactionSuccessful
Catch
Log(LastException)
SQL1.Rollback
End Try
End Sub
Also if I stop the program during the Not Responding time, I get a partial write to the DataBase so it doesn't seem to be writing all or none. Can anyone see what I'm doing wrong?