Android Code Snippet MSMySQL_SelectHelper

Discussion in 'Code Snippets' started by Mashiane, Aug 14, 2015.

Similar threads

B4A Code Snippet MSMySQL_DeleteHelper
B4A Code Snippet MSMySQL_InsertHelper
B4A Code Snippet MSMySQL_UpdateHelper
B4A Question customlistview loads max items and loads more when scroll tot bottom
B4A Code Snippet [B4X] GUID
  1. Mashiane

    Mashiane Expert Licensed User

    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.

    Code:
    Public Sub MSMySQL_SelectHelper(tblName As String, iMap As MapAs 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..

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

    Code:
    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.
     
    rildomoraes and JakeBullet70 like this.
  2. Mashiane

    Mashiane Expert Licensed User

    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.

    Code:
    Public Sub MSMySQL_SelectHelper(tblName As String, wMap As Map, oList As ListAs 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...

    Code:
    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)
     
    DonManfred likes this.
  3. Mashiane

    Mashiane Expert Licensed User

    Code:
    Sub FixOperator(sValue As StringAs 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
     
    JakeBullet70 likes this.
Loading...
  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice