Android Code Snippet MSMySQL_SelectHelper

The purpose of this function is to return the SELECT command by using a map field, operator, value pairs for use with the MSMySQL library. As you will note, the values passed to the map are assumed to be strings. When defining the map, you include the operator to choose the values with.

Public Sub MSMySQL_SelectHelper(tblName As String, iMap As Map) As String
    ' pass a map with field names and field values to
    ' build a select 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("SELECT * FROM " & tblName & " WHERE ")
    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(" AND ")
    Next
    sb.Append(";")
    Return sb.tostring
End Sub

Usage..

Dim wSite As Map
    wSite.Initialize
    wSite.Put("SiteName = ", txtSiteName.Text)
    Dim iQry As String = modMashiane.MSMySQL_SelectHelper("Sites", wSite)
    hDB.db.QueryASync(iQry, "checkSite")

Then, you check the record counted and then execute another...

Sub MySQL_QueryResult(data As List, meta As Map)
    ' get records returned
    Dim rTot As Int
    rTot = meta.Get("RecordCount")
    Select Case rTot
    Case 1
        ' tell user that the site exists
        modMashiane.Alert(txtSiteName.Text & " Error", "This site has already been added to the server db!")
        txtSiteName.RequestFocus
    Case Else
        ' everything is spruce, insert record to database...

See MSMySQL_InsertHelper for more details.

 

[Mashiane]

You can also update the sql command to include a sort order for the records you want to read. This code snippet below includes a sort order for the fields.

Public Sub MSMySQL_SelectHelper(tblName As String, wMap As Map, oList As List) As String
    ' pass a map with field names and field values to
    ' build a select 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 = wMap.size - 1
    ' start building the sql query
    sb.initialize
    sb.Append("SELECT * FROM ")
    sb.Append(tblName)
    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
    fTot = oList.Size - 1
    If fTot >= 0 Then
        sb.Append(" ORDER BY ")
        For fCnt = 0 To fTot
            ' get field name
            colName = oList.Get(fCnt)
            sb.append(colName)
            If fCnt <> fTot Then sb.Append(",")
        Next
    End If
    sb.Append(";")
    Return sb.tostring
End Sub

Usage...

Dim wSite As Map
        wSite.Initialize
        wSite.Put("id = ", rID)
        Dim sList As List
        sList.Initialize
        sList.Add("SiteName")
        Dim iQry As String = modMashiane.MSMySQL_SelectHelper("Sites", wSite, sList)

 

[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