Android Code Snippet MSMySQL_UpdateHelper

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...

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.

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...

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")