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.
Dependency: SQL library
Tags: CREATE TABLE, INSERT, DROP TABLE, ALTER TABLE
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 SubDependency: SQL library
Tags: CREATE TABLE, INSERT, DROP TABLE, ALTER TABLE
