Android Question DBUtils - Check if table field(Col) exists, If Not add field to table

[Declan]

I am using the following code snippet courtesy of @Mashiane

    Dim strFld As String
    Dim fType As String
    Dim curFields As List
    Dim cur As ResultSet
    curFields.Initialize
    cur = DB.ExecQuery("PRAGMA table_info ('" & tblName & "')")
    Do While cur.NextRow
        strFld = cur.GetString("name")
        LogColor("Field Name: " & strFld, Colors.Red)
        fType = cur.GetString("type")
        If fType.ToLowerCase = "text" Then curFields.Add(strFld)
    Loop
    cur.close
    Return curFields

This works great and I get all the field names

Field Name: id
Field Name: lastname
Field Name: firstname
Field Name: address1
Field Name: address2
Field Name: suburb
Field Name: city
Field Name: occupation
Field Name: income
Field Name: cellno
Field Name: email
Field Name: product

In the above code I must look for a field "feedback" (which does not exist in the current list)
If it does not exist, I must append the new field "feedback" to the table.
How do I check the Field Name from the above Log?
How do I append / add the new field to the table?

I have looked at loads of examples, but for some reason, cannot get it to work.

 

[Mahares]

How do I check the Field Name from the above Log?
How do I append / add the new field to the table?

Here is my complete way of doing it. Code check if col exists. If not add it to table:

If TableColumnExists("tblVehicles", "location") =False Then
        Starter.SQL1.ExecNonQuery("ALTER TABLE tblVehicles ADD location TEXT")
        Log("new column added")
End If

Sub TableColumnExists(TableName As String, ColumnName As String) As Boolean
    Dim cur As ResultSet
    cur = Starter.SQL1.ExecQuery("PRAGMA table_info(" & TableName & ")")
    Do While cur.NextRow
        Dim MyColumn As String = cur.GetString("name")
        Log("col name " & cur.GetString("name"))
        If MyColumn.Trim.ToUpperCase = ColumnName.ToUpperCase Then Return True
    Loop
    Return False
End Sub

There are other ways too.

 

[mc73]

Here is my complete way of doing it. Code check if col exists. If not add it to table:

If TableColumnExists("tblVehicles", "location") =False Then
        Starter.SQL1.ExecNonQuery("ALTER TABLE tblVehicles ADD location TEXT")
        Log("new column added")
End If

Sub TableColumnExists(TableName As String, ColumnName As String) As Boolean
    Dim cur As ResultSet
    cur = Starter.SQL1.ExecQuery("PRAGMA table_info(" & TableName & ")")
    Do While cur.NextRow
        Dim MyColumn As String = cur.GetString("name")
        Log("col name " & cur.GetString("name"))
        If MyColumn.Trim.ToUpperCase = ColumnName.ToUpperCase Then Return True
    Loop
    Return False
End Sub

There are other ways too.

Just a slight modification, cause it's always good to close the cursors:

Sub TableColumnExists(TableName As String, ColumnName As String) As Boolean
   dim exists as boolean    
   Dim cur As ResultSet
    cur = Starter.SQL1.ExecQuery("PRAGMA table_info(" & TableName & ")")
    Do While cur.NextRow
        Dim MyColumn As String = cur.GetString("name")
        Log("col name " & cur.GetString("name"))
        If MyColumn.Trim.ToUpperCase = ColumnName.ToUpperCase Then 
          exists=true
          exit
    end if
    Loop
   cur.close
   return exists
End Sub

However, I remember in a similar case, I just used a try/catch block with altering. If field exists, an error is returned.

 

[Declan]

Thanks Guys ...works a dream