How to modify type in a table of SQLite in Android, using onUpgrade method?

Question

Following this good example http://code.google.com/p/openintents/source/browse/trunk/notepad/NotePad/src/org/openintents/notepad/NotePadProvider.java?r=3878

I implement my sqlite database upgrade system. But in the link above the only example show how add new column in a table. The piece of code in the onUpgrade method is

 try {
      db.execSQL("ALTER TABLE " +   NOTES_TABLE_NAME + " ADD COLUMN "
                                                                    + Notes.TAGS + " TEXT;");
      db.execSQL("ALTER TABLE " + NOTES_TABLE_NAME + " ADD COLUMN "
                                                                    + Notes.ENCRYPTED + " INTEGER;");
      db.execSQL("ALTER TABLE " + NOTES_TABLE_NAME + " ADD COLUMN "
                                                                    + Notes.THEME + " TEXT;");
      } catch (SQLException e) {
      Log.e(TAG, "Error executing SQL: ", e);
      // If the error is "duplicate column name" then everything is fine,
      // as this happens after upgrading 2->3, then downgrading 3->2, 
      // and then upgrading again 2->3.
      }
      //  *** other upgrade from version x->y
      // ***

and in onCreate, something like this:

  public void onCreate(SQLiteDatabase db) {
      db.execSQL("CREATE TABLE " + NOTES_TABLE_NAME + " ("
           // Version 2:
           + Notes._ID + " INTEGER PRIMARY KEY,"
           + Notes.TITLE + " TEXT,"
           + Notes.NOTE + " TEXT,"
           + Notes.CREATED_DATE + " INTEGER,"
           + Notes.MODIFIED_DATE + " INTEGER,"
           // Version 3:
           + Notes.TAGS + " TEXT,"
           + Notes.ENCRYPTED + " INTEGER,"
           + Notes.THEME + " TEXT,"
           // Version 4:
           + Notes.SELECTION_START + " INTEGER,"
           + Notes.SELECTION_END + " INTEGER,"
           + Notes.SCROLL_POSITION + " REAL"
           + ");");
          }

Now I want a new database version where no column is added, but I want just change type of some field in a table. How can I do this?

Here http://sqlite.org/lang_altertable.html is said that is not possible to change a field type. How can I circumvent the problem?

I'm inserting data in database in this way:

   ContentValues v = new ContentValues();
   v.put("field1", myString); // fileld1 in db is defined as integer

   dbManager.updateTable("mytable", v, whereClause);

How can I make a CAST in this situation?

CL. · Accepted Answer · 2012-11-07 15:41:29Z

11

SQLite indeed does not allow changing existing table columns.

However, SQLite uses dynamic typing, so what column type you set in the table definition does not really matter (except for conversions due to type affinity).

In the general case, any changes to tables (other than new columns) require that you create a new table:

CREATE TABLE TempTable (...);
INSERT INTO TempTable SELECT col1, col2, ... FROM MyTable;
DROP TABLE MyTable;
ALTER TABLE TempTable RENAME TO MyTable;

If you want to change the type of values already stored in the table, you have to use CAST:

...
INSERT INTO TempTable SELECT CAST(col1 AS TEXT), ... From MyTable;
...

For new values, just put a value with the correct type into the ContentValues object.

edited Nov 7, 2012 at 15:41

answered Nov 7, 2012 at 13:23

CL.

182k18 gold badges241 silver badges282 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

GVillani82 Over a year ago

I'm using ContentValues for insert String data in Integer field of database. (I edited my answer). How can I make a CAST in this situation?

Collectives™ on Stack Overflow

How to modify type in a table of SQLite in Android, using onUpgrade method?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related