Android Question Check if a column exists in a SQLite table

[Espinosa4]

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

 

[Erel]

See this example: Android SQLite Viewer

It shows the available columns.

 

[Espinosa4]

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.

 

[Erel]

Download the code and see how it finds the available columns...

 

[Espinosa4]

Here more or less my idea

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

 

[Mahares]

You need something like this:

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

 

[Espinosa4]

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

 

[GuyBooth]

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

        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.

 

[Espinosa4]

Thank very very much GuyBooth! Now it seems complete!

Best regards

 

[luke2012]

Hi @GuyBoot and @Espinosa4.
The DBMS engine should check internally if the column exist, right ?

 

[Espinosa4]

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