The purpose of this code snippet is the generation of a command to update records in a MySQL database table using the MSMySQL library. To build your update sql command, you use maps for the fields to update and another map for the where clause. The assumption are that the field types are strings, but this works even if its numeric fields though...
The FixOperator sub just fixes the where clause stuff, I kept on forgetting to include an equal sign on my map.. This just ensures that where uses an equal sign if the operator is not specified.
Usage...
B4X:
Public Sub MSMySQL_UpdateHelper(tblName As String, iMap As Map, wMap As Map) As String
' pass a map with field names and field values to
' build an update SQL string for MSMySQL
' assuming all values to insert are string
Dim sb As StringBuilder
Dim fTot As Int
Dim fCnt As Int
Dim colName As String
Dim colValue As String
' find the size of the map
fTot = iMap.size - 1
' start building the sql query
sb.initialize
sb.Append("UPDATE ").Append(tblName).Append(" SET ")
For fCnt = 0 To fTot
' get field name
colName = iMap.GetKeyAt(fCnt)
' get field value
colValue = iMap.GetValueAt(fCnt)
sb.append(colName)
sb.append(" = '")
sb.append(colValue)
sb.append("'")
If fCnt <> fTot Then sb.Append(", ")
Next
' build where clause
fTot = wMap.Size - 1
sb.Append(" WHERE ")
For fCnt = 0 To fTot
' get field name
colName = wMap.GetKeyAt(fCnt)
colName = FixOperator(colName)
' get field value
colValue = wMap.GetValueAt(fCnt)
sb.append(colName)
sb.append("'")
sb.append(colValue)
sb.append("'")
If fCnt <> fTot Then sb.Append(" AND ")
Next
sb.Append(";")
Return sb.tostring
End Sub
The FixOperator sub just fixes the where clause stuff, I kept on forgetting to include an equal sign on my map.. This just ensures that where uses an equal sign if the operator is not specified.
B4X:
Sub FixOperator(sValue As String) As String
Dim iOp As Int = 0
sValue = sValue.Trim
If sValue.EndsWith("=") = True Then iOp = iOp + 1
If sValue.EndsWith(">=") = True Then iOp = iOp + 1
If sValue.EndsWith("<=") = True Then iOp = iOp + 1
If sValue.EndsWith(">") = True Then iOp = iOp + 1
If sValue.EndsWith("<") = True Then iOp = iOp + 1
If sValue.EndsWith("<>") = True Then iOp = iOp + 1
If iOp = 0 Then
sValue = sValue & " = "
Else
sValue = sValue & " "
End If
Return sValue
End Sub
Usage...
B4X:
Dim nSite as Map
nSite.Initialize
nSite.Put("Formulae", "")
nSite.Put("userid", uID)
nSite.Put("areaid", aID)
' build where clause
Dim wMap As Map
wMap.Initialize
wMap.Put("id", rID)
' update the site to the database, lets build the sql command from the map
Dim iQry As String = MSMySQL_UpdateHelper("Sites", nSite, wMap)
'execute the query and return a result
db.ExecuteASync(iQry, "updateSite")