Android Code Snippet Dbutils, Modified Create Table, to allow new columns

Hi all

I have seem some posts about adding new columns to an existing table, so, I have modified create table to allow this.

Hope this is useful to anyone who has wanted to do this.

Regards

John.

Sub CreateTable(SQL As SQL, TableName As String, FieldsAndTypes As Map, PrimaryKey As String)
   
   Dim pk As String, startMap As Int

   ' // create the table
   If Not(TableExist(SQL,TableName)) Then
     ' // start map at 1
     startMap = 1
     ' // set primary key
     If FieldsAndTypes.GetKeyAt(0) = PrimaryKey Then
       pk = " PRIMARY KEY"
     End If
     ' // create table and first col
     SQL.ExecNonQuery("CREATE TABLE [" & TableName & "] (" & _
           FieldsAndTypes.GetKeyAt(0) & " " & _
           FieldsAndTypes.GetValueAt(0)& pk & ")")
   End If
   
   ' // add rest of cols to table
   For i = startMap To FieldsAndTypes.Size - 1
     
     Dim field, ftype As String
     
     field = FieldsAndTypes.GetKeyAt(i)
     ftype = FieldsAndTypes.GetValueAt(i)
     pk = ""
     If field = PrimaryKey Then
       pk = " PRIMARY KEY"
     End If
     ' // add the colum     
     Try
       SQL.ExecNonQuery("ALTER TABLE [" & TableName & "] ADD COLUMN [" & field & "] " & ftype & pk)
     Catch
       Log("CreateTable() " & field & " already exists")
     End Try

   Next
   
End Sub

 

[asales]

   ' // create the table
   If Not(TableExist(SQL,TableName)) Then

I didn't found the code for "TableExist"...

 

[Jmu5667]

I didn't found the code for "TableExist"...

Here it is

Sub TableExist(pSQL As SQL, pName As String) As Boolean

   If pSQL.ExecQuerySingleResult("SELECT count(name) FROM sqlite_master WHERE type='table' AND name = '" & pName & "'") = 0 Then
     Return False
   Else
     Return True
   End If

End Sub