+1 for parameterized queries as
@Erel has described above. I prefer separate the steps. There may be better ways to do it, this is just my way. For me, it's easier and more maintainable to break up each step into a function.
In the code snippet below, I've pared down the number of columns for brevity. In reality I have 25+ columns. There is no way I could maintain the code building it like you have done above with 25 columns (sure, maybe I need to normalize my tables a little...that is another discussion). Much easier to maintain and troubleshoot....in my opinion (you know what they say about opinions
). You *Could* change/validate your data in the data array builder (add tics/null checks etc.) but I'm guessing this is not best practice. The data should be validated when it goes into the Type object (GameRow in this case). Before the call to update the database.
Additional benefit to this method is, it's much easier to change this structure if you add remove columns from the data table. Sure you have to change it in 3 places, but also, you don't have to spend 1/2 a day finding the syntax errors in a massively long function call when you make a change.
The way I implement parameterized queries it is as follows. It requires....
1. A Type object that is the same structure as your data row (Type GameRow in the code snippit below)
2. A SQL String builder (GetUpdateGameRecordString in the snippit below) to get the update string
3. A data array builder (GetUpdateGameRecordArray in the snippit below) to build the data array
****Then just call ExecNonQuery2 with the objects
Hope this helps.
' This example uses a datatype object for row mapping
Type GameRow(rowId As Int, playResult as String, playScore as Int)
'Accept new values, build type object and update the db
Private Sub UpdateRow(rowId as Int, playResult as String, playScore as Int)
Dim tempRow as GameRow
tempRow.Initialize
tempRow.rowId = rowId
tempRow.playResult = playResult
tempRow.playScore = playScore
UpdateGameRecord(tempRow, "GameTable")
End Sub
Private Sub UpdateGameRecord(gr As GameRow, tblName As String)
Dim sqlString As String = GetUpdateGameRecordString(tblName)
Dim dataArray() As String = GetUpdateGameRecordArray(gr)
B4XPages.MainPage.SQLGame.BeginTransaction
Try
'Execute the sql statements.
B4XPages.MainPage.SQLInstance.ExecNonQuery2(sqlString, dataArray)
B4XPages.MainPage.SQLInstance.TransactionSuccessful
Catch
log(LastException.Message)
End Try
B4XPages.MainPage.SQLGame.EndTransaction
End Sub
Private Sub GetUpdateGameRecordString(tblName As String) As String
Private sb As StringBuilder
sb.Initialize
sb.Append("UPDATE ")
sb.Append(tblName)
sb.Append(" Set ")
sb.Append("playResult = ?,")
sb.Append("playScore = ? ")
sb.Append("WHERE rowId = ?")
Return sb.ToString
End Sub
Private Sub GetUpdateGameRecordArray(gr As GameRow) As String()
Dim tempArray(3) As String
tempArray(0) = gr.playResult ' You could tweak/validate data here (add tics, null checks etc.) but probably bad practice.
tempArray(1) = gr.playScore ' Data should be good when it goes into the GameRow type object, not on the the database update call.
tempArray(2) = gr.rowId
Return tempArray
End Sub