Android - Change a column type in SQLite database dynamically at runtime

I have an application, where I am detecting the type of a particular column at run-time, on page load. Please refer the below code:

public String fncCheckColumnType(String strColumnName){
db = this.getWritableDatabase();
String strColumnType = "";
Cursor typeCursor = db.rawQuery("SELECT typeof (" + strColumnName +") from tblUsers, null);
typeCursor.moveToFirst();
strColumnType = typeCursor.getString(0);
return strColumnType;
}


The above method simply detects the type of column with column Name 'strColumnName'. I am getting the type of column in this case.

Now, I want to change the column type to TEXT if I am receiving INTEGER as the column type. For this, I tried the below code:

public String fncChangeColumnType(String strColumnName){
db = this.getWritableDatabase();
String newType = "";
Cursor changeCursor = db.rawQuery("ALTER TABLE tblUsers MODIFY COLUMN " + strColumnName + " TEXT", null);
if (changeCursor != null && changeCursor.moveToFirst()){
newType = changeCursor.getString(0);
}

return newType;
}


But while executing the 'fncChangeColumnType' method, I am getting this error, android.database.sqlite.SQLiteException: near "MODIFY": syntax error (code 1): , while compiling: ALTER TABLE tblUsers MODIFY COLUMN UserID TEXT

android.database.sqlite.SQLiteException: near "MODIFY": syntax error (code 1): , while compiling: ALTER TABLE tblUsers MODIFY COLUMN UserID TEXT

NOTE: I also replaced 'MODIFY' with 'ALTER', but still getting the same error.

Please check if this is the right method to change the type dynamically.

Please respond back if someone has a solution to this.

Thanks in advance.

2 Answers
2

i think the sql query statement is wrong ,try

ALTER TABLE tblUsers MODIFY COLUMN id TYPE integer USING (id::integer);


instead of id use column name....

hope this helps....

EDIT:

"ALTER TABLE tblUsers MODIFY COLUMN "+strColumnName+" TYPE integer USING ("+strColumnName+"::integer);"


In brief, the solution could be :-

CAST(mycolumn AS TEXT)

With SQLite there are limitations on what can be altered. In short you cannot change a column. Alter only allows you to either rename a table or to add a column. As per :-

SQL As Understood By SQLite - ALTER TABLE

However, with the exception of a column that is an alias of the rowid column

?? INTEGER PRIMARY KEY

?? INTEGER PRIMARY KEY AUTOINCREMENT

?? INTEGER ... PRIMARY KEY(??)

you can store any type of value in any type of column. e.g. consider the following (which stores an INTEGER, a REAL, a TEXT, a date that ends up being TEXT and a BLOB) :-

CREATE TABLE IF NOT EXISTS example1_table (col1 BLOB);
INSERT INTO example1_table VALUES (1),(5.678),('fred'),(date('now')),(x'ffeeddccbbaa998877665544332211');
SELECT *, typeof(col1) FROM example1_table;


The result is :-

As such is there a need to change the column type at all?

If the above is insufficient then your only option is to create a new table with the new column definitions, populate it if required from the original table, and to then replace the original table with the new table ( a) drop original and b)rename new or a) rename original, b) rename new and c) drop original)

e.g. :-

DROP TABLE IF EXISTS original;
CREATE TABLE IF NOT EXISTS original (mycolumn INTEGER);
INSERT INTO original VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(0);
-- The original table now exists and is populated

CREATE TABLE IF NOT EXISTS newtable (mycolumn TEXT);
INSERT INTO newtable SELECT CAST(mycolumn AS TEXT) FROM original;
ALTER TABLE original RENAME TO old_original;
ALTER TABLE newtable RENAME TO original;
DROP TABLE IF EXISTS old_original;
SELECT *,typeof(mycolumn) FROM original;


The result being :-

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.