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

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.

B4X:
Dim Cursor1 As Cursor
Dim DBTableName As String ="T1"
Dim DBTableName2 As String ="T2"
Dim txt As String

B4X:
'DBTableName was created with this structure and has some records:
'txt="CREATE TABLE IF NOT EXISTS " & DBTableName & "(col1 TEXT, col2 INTEGER )"

B4X:
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
 
Top