Android Code Snippet How To Change a Column Type In a SQLite Table

Discussion in 'Code Snippets' started by Mahares, Dec 30, 2015.

  1. Mahares

    Mahares Well Known Member Licensed User

    SubName: Change SQLite column type

    Description:
    Most generally it is not necessary to change a column type, say from TEXT to INTEGER or vice-versa as SQLite structure allows you to store strings in numeric type column and numbers in text type column. There are situations where you want to do it for clarity and better sorting. For instance, if you have a field called CODE TEXT type, but it contains all numbers such as 3, 45, 287, when you sort the records by CODE, the records will show like this: 287, 3, 45. To get the correct sort, you need to cast CODE to INTEGER using the CAST function.

    The sequence is as follows:
    1. Create a 2nd table with the same structure as the 1st except that one of the columns is changed from INTEGER to TEXT.
    2. Insert all the records from the 1st table into the 2nd table.
    3. Delete the 1st table.
    4. Rename the 2nd table to the 1st table.

    Code:
    Dim Cursor1 As Cursor
    Dim DBTableName As String ="T1"
    Dim DBTableName2 As String ="T2"
    Dim txt As String
    Code:
    'DBTableName was created with this structure and has some records:
    'txt="CREATE TABLE IF NOT EXISTS " & DBTableName & "(col1 TEXT, col2 INTEGER )"
    Code:
    Sub CreateTable
      txt=
    "CREATE TABLE IF NOT EXISTS " & DBTableName2 & "(col1 TEXT, col2 TEXT )"
      SQL1.ExecNonQuery(txt)
    End Sub

    Sub ChangeColumnType
      txt=
    "SELECT * FROM " & DBTableName
      Cursor1=SQL1.ExecQuery(txt)
      SQL1.BeginTransaction
      
    For i=0 To Cursor1.RowCount -1
        Cursor1.Position=i
        txt=
    "INSERT INTO " & DBTableName2 & " VALUES (?,?)"
        SQL1.ExecNonQuery2(txt, 
    Array As String(Cursor1.GetString("col1"), Cursor1.GetString("col2") ))
      
    Next

      SQL1.ExecNonQuery(
    "DROP TABLE " & DBTableName)  'Delete the first table
      SQL1.ExecNonQuery("ALTER TABLE " & DBTableName2 & " RENAME TO " & DBTableName)  'rename the 2nd table to the 1st
      SQL1.TransactionSuccessful
      SQL1.EndTransaction
    End Sub
    Dependency: SQL library

    Tags: CREATE TABLE, INSERT, DROP TABLE, ALTER TABLE
     
    Toky Olivier, Devan, RauchG and 2 others like this.
Loading...
  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