Android Question Add columns to the sqlite table

SMOOTSARA

Active Member
Licensed User
Hello friends 🌹

I have a sqlite database "master.db" with a table "table1"

The columns of the table are: " ID, first_name, last_name"

I want to check if there is no "phone_number" column, add it and save a data in "phone_number" column . (with coding sqlite commands in the program)

1_How can I control the existence of this column?
2_How can I add this column to the table?

Thank you🙏
 

Pendrush

Well-Known Member
Licensed User
B4X:
Dim SQL1 As SQL
Dim Cursor1 As ResultSet

SQL1.Initialize(File.DirInternal, "master.db", False)
Cursor1 = SQL1.ExecQuery("SELECT * FROM table1 LIMIT 1")

Dim cols1 As Int = Cursor1.ColumnCount
Dim MyCol1(cols1) As String
Dim Flag1 As Boolean = False
For i = 0 To Cursor1.ColumnCount-1
    MyCol1(i)=Cursor1.GetColumnName(i)
     If MyCol1(i) = "phone_number" Then
          Flag1 = True
          Log("Col phone_number exist")   
     End If
Next
Cursor1.Close
If Flag1 = False Then
    SQL1.ExecNonQuery("ALTER TABLE table1 ADD COLUMN phone_number TEXT")
End If
 
Upvote 0

Alex_197

Active Member
Licensed User
B4X:
public Sub AddColumn(TableName As String    ,FieldName As String, Definition As String ,SQL1 As SQLCipher)
    
    Try
     
        
        Dim MySQL As String=""
        
        If IsFieldExists2(TableName,FieldName,SQL1)=True Then
            Return
        End If
                
        MySQL="ALTER TABLE " & TableName & " Add " & FieldName & " " & Definition & ";"
        
        SQL1.ExecNonQuery(MySQL)
        
    Catch
        Log("AddColumn " & LastException.Message)
        modFun.ShowError("modDB_AddColumn " & LastException.Message)
    End Try
    
End Sub



private Sub IsFieldExists2(TableName As String    ,FieldName As String,SQL1 As SQLCipher) As Boolean
    
    Try
        

        Dim MySQL As String=""
        Dim Cursor1 As Cursor
        Dim Res As String
                
        MySQL="SELECT sql FROM sqlite_master WHERE Type = 'table' And name='" & TableName & "'"
        
        Cursor1=SQL1.ExecQuery(MySQL)

        Cursor1.Position=0

        Res=Cursor1.GetString("sql")
        
        Cursor1.Close
        
        If Res.Contains(FieldName) Then
    
            Return True
    
        Else
            Return False
        End If
        
    Catch
        Log("IsFieldExists2 " & LastException.Message)
        modFun.ShowError("modDB_sFieldExists2 " & LastException.Message)
        Return False
    End Try
    
End Sub
 
Upvote 0
Top