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

Question

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

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.

Community · Accepted Answer · 2020-06-20 09:12:55Z

In brief, the solution could be :-

Do nothing (i.e. take advantage of SQLite's flexibility)
1. you could utilise CAST e.g. CAST(mycolumn AS TEXT) (as used below)
Create a new table to replace the old table.

Explanations.

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

one defined with ?? INTEGER PRIMARY KEY or ?? INTEGER PRIMARY KEY AUTOINCREMENT or ?? INTEGER ... PRIMARY KEY(??) (where ?? represents a valid column name)

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

Anjani Mittal · Accepted Answer · 2018-07-03 05:53:35Z

0

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);"

edited Jul 3, 2018 at 5:53

answered Jul 3, 2018 at 4:08

Anjani Mittal

5271 gold badge7 silver badges19 bronze badges

9 Comments

Sarthak Sharma Over a year ago

But you are not providing the column name at all.

Anjani Mittal Over a year ago

isnt "strColumnName" the column name?

Sarthak Sharma Over a year ago

Yes it is, but you are not providing the column name in your line of code.

Anjani Mittal Over a year ago

"id" is the column name here

Anjani Mittal Over a year ago

yes replace id with strcolumnname, i edited the above ans accordingly

|

Collectives™ on Stack Overflow

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

2 Answers 2

Explanations.

Comments

9 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Explanations.

Comments

9 Comments

Your Answer

Sign up or log in

Post as a guest

Related