Android Code Snippet MSMySQL_DeleteHelper

The purpose of this script is the generation of a Delete sql command for MySQL using the MSMySQL library. To delete, you pass a map containing the field, value pairs to delete from your table.

Public Sub MSMySQL_DeleteHelper(tblName As String, iMap As Map, bHasQuote As Boolean) As String
    ' pass a map with field names and field values to
    ' build a delete 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("DELETE FROM " & tblName & " WHERE ")
    For fCnt = 0 To fTot
        ' get field name
        colName = iMap.GetKeyAt(fCnt)
        colName = FixOperator(colName)
        ' get field value
        colValue = iMap.GetValueAt(fCnt)
        sb.append(colName)
        If bHasQuote = True Then sb.append("'")
        sb.append(colValue)
        If bHasQuote = True Then sb.append("'")
        If fCnt <> fTot Then sb.Append(" AND ")
    Next
    sb.Append(";")
    Return sb.tostring
End Sub

Usage...

ProgressDialogShow("Deleting reservior, please wait...")
            Dim dMap As Map
            dMap.Initialize
            dMap.Put("id = ", rID)
            Dim dQry As String = MSMySQL_DeleteHelper("Sites", dMap, False)
            hDB.db.ExecuteASync(dQry, "deleteSite")

 

[Mashiane]

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