Android Code Snippet SQlite: Backup database and alter table

Discussion in 'Code Snippets' started by KMatle, Oct 30, 2017.

  1. KMatle

    KMatle Expert Licensed User

    Here's a little sub to backup and alter a table (assuming you have all the SQlite parts in the starter service).

    The sub checks if the column is already defined. Of course you can leave the loop at once with a return. For debug reasons I've used a Boolean here.

    Sub AddColumnToTable (TableName As String, CToAdd As String, CFormat As String)
    Private Row As Int
    Private Query As String
    '    Dim TableName As String = "items" 'tablename
    '    Dim CToAdd As String = "iid" 'column to add
    '    Dim CFormat As String = "INTEGER" 'format of the column
        Dim DBDir As String =Starter.SQLDataBasePath
    Dim DBName As String =Starter.SQLDateBaseName
        Query = 
    "SELECT * FROM " & TableName 'get all columns
        Cursor1 = Starter.SQL1.ExecQuery(Query)
    Dim Found As Boolean = False
    For l=0 To Cursor1.ColumnCount-1
    If Cursor1.GetColumnName(l) = CToAdd Then
    End If
    If Found=False Then 'backup before alter ;-)
            File.Copy(DBDir,DBName,DBDir,DBName & DateTime.Now & "_backup"'backup before altering table
            Query = "ALTER TABLE " & TableName & " ADD COLUMN " & CToAdd & " " & CFormat
    End If
        Query = 
    "PRAGMA table_info(" & TableName &")" 'Get table info -> shows column names and format
        Cursor1 = Starter.SQL1.ExecQuery(Query)
    For Row = 0 To Cursor1.RowCount - 1
            Cursor1.Position = Row
    Log(Cursor1.GetString2(1) & ":" & Cursor1.GetString2(2))

    End Sub
    mendiburen, jimmyF, Volga and 5 others like this.
  2. Star-Dust

    Star-Dust Expert Licensed User

    I also add a new table with its Fields :p

    Sub AddTable(Path as String, FileName as String, NameTable As String,Fields As StringAs Boolean
    ' Files. es
        Dim DB As SQL
    Dim S As String
    Dim B As Boolean = False
    "SELECT name FROM sqlite_master WHERE type='table' AND name='" & NameTable &"';")
    If S=Null Then
    "CREATE TABLE " & NameTable &" (" & Fields & ")" )
    End If
    Return b
    End Sub
  3. Johan Hormaza

    Johan Hormaza Active Member Licensed User

    Good job!!!
  4. ivan.tellez

    ivan.tellez Active Member Licensed User

    Do you really want to load ALL the table data to memory? What if has 2 million records?

    Yo should add an LIMIT 1 to the query
    mendiburen likes this.
  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice