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

Declan · Aug 15, 2020

I am using the following code snippet courtesy of @Mashiane

B4X:

    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

B4X:

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 · Aug 15, 2020

Declan said:
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:

B4X:

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

B4X:

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 · Aug 15, 2020

Mahares said:

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

B4X:

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

B4X:

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:

B4X:

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 · Aug 16, 2020

Thanks Guys ...works a dream

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

Declan

Well-Known Member

Mahares

Expert

mc73

Well-Known Member

Declan

Well-Known Member

Similar Threads

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

Declan

Well-Known Member

Mahares

Expert

mc73

Well-Known Member

Declan

Well-Known Member

Similar Threads

Privacy & Transparency

Privacy & Transparency