Android Question Check if a column exists in a SQLite table

Espinosa4

Active Member
Licensed User
Longtime User
Hello,

Can I check if a column exists in a SQLite table, please?
I am looking for information here but always the questions are around if the table exists or not.

Thanks in advance
Espinosa
 

Espinosa4

Active Member
Licensed User
Longtime User
Thanks Erel. Very useful app but with a simple code... can we know if a column exists? because isn't exists I want to add using ALTERNATE command.

Cheers.
 
Upvote 0

Espinosa4

Active Member
Licensed User
Longtime User
Here more or less my idea

B4X:
Sub Activity_Create(FirstTime As Boolean)
    'Do not forget to load the layout file created with the visual designer. For example:
    'Activity.LoadLayout("Layout1")
    If File.Exists(File.DirDefaultExternal,"l4pro.sql") = False Then
        File.Copy(File.DirAssets,"l4pro.sql",File.DirDefaultExternal,"l4pro.sql")
    End If
  
    If SQLVariables.IsInitialized = False Then
        SQLVariables.Initialize(File.DirDefaultExternal, "l4pro.sql", False)
    End If
    'AND HERE I'D LIKE TO CHECK IS THE COLUMN EXISTS.
    If the SQLVariables.column("Col10") Not exists Then "ALTER TABLE SQLVariables ADD COLUMN Col10 VARCHAR(100)"
End Sub

cheers
 
Upvote 0

Mahares

Expert
Licensed User
Longtime User
You need something like this:
B4X:
Dim SQLVariables As SQL   'in Process_Globals
Dim Cursor1 As Cursor 'in  Globals


If File.Exists(File.DirDefaultExternal,"l4pro.sql") = False Then
    File.Copy(File.DirAssets,"l4pro.sql",File.DirDefaultExternal,"l4pro.sql")
End If

If SQLVariables.IsInitialized = False Then
    SQLVariables.Initialize(File.DirDefaultExternal, "l4pro.sql", False)
End If

Dim MyTable As String = "names"    'Put your table name here
Dim txt As String
txt="SELECT * FROM " & MyTable & " LIMIT 1"
Cursor1=SQLVariables.ExecQuery(txt)
Cursor1.Position=0
Dim cols As Int = Cursor1.ColumnCount
Dim MyColName(cols) As String
Dim NewColumn As String = "Col10"
Dim Flag As Boolean =False

For i=0 To Cursor1.ColumnCount-1
    MyColName(i)=Cursor1.GetColumnName(i)
    If MyColName(i)= NewColumn Then
     Msgbox("Column already exists. ","")
     Flag=True
     Exit
    End If
Next

If Flag=False Then
    txt="ALTER TABLE " & MyTable & " ADD COLUMN " & NewColumn &   " VARCHAR(100)"
    SQLVariables.ExecNonQuery(txt)
    Msgbox(NewColumn & " was added to the table","")
End If
 
Upvote 0

Espinosa4

Active Member
Licensed User
Longtime User
Thank you very very much Mahares!
It's great!

I'll try to apply your code in my program.

Thanks to all for sharing your knowledges!
Cheers
Espinosa
 
Upvote 0

GuyBooth

Active Member
Licensed User
Longtime User
The approach I used was to run the Alter Table query without checking within a Try ... Catch loop, and intercept the error message.

B4X:
        Try
            ' Add new columns to the database if they don't already exist
            TMM.gsqlMB.ExecNonQuery("ALTER TABLE [PrimaryGroups] ADD [SortField] VarChar(50) default '';")
        Catch   
            If LastException.Message.IndexOf("duplicate column") = -1 Then
                ' Not a duplicate field problem
                sReturnMessage = "Problem creating Sort & Origin Fields: " & LastException.Message
            Else
                sReturnMessage = "FYI - Duplicate Column(s) found"
            End If
        End Try

Saves some code and lets SQLLite do the work for you.
Good Luck.
 
Upvote 0

Espinosa4

Active Member
Licensed User
Longtime User
Hi luke2012

I am a beginner programmer and I use the method that GuyBooth comments on his post.

The DBMS return an error when you try to create a new field in a table and it already exists. If the routine don't return an error you can create a new field because it doesn't exists.

Cheers
 
Upvote 0
Top