CODEWITHSUNDEEP
androidsqlite
turtleboy
turtleboy

Reputation: 7574

sqlite upgrading DB with extra field in table and keeping old data

I've and app that uses a sqlite DB. The db has transaction data stored in the Transactions table. I would like to add an extra field to the Transactions table whilst keeping the old data from the previous DB.

I've overidden the onUpgrade method in the SqliteOpenHelper class. I've tried to change the name of the Transactions table to OldTrasnsactions, then re-create the transactions table and copy the data from OldTransactions.

Android is saying that The Transactions table does not exist when compiling the Alter statement. I'm incrementing the version number correctly as the onUpgrade is executing.

Does anyone know what i'm doing wrong?

Below is the error and SqliteOpenHelper class.

NB the extra new field in Transactions is C_TRANSACTIONS_DRIVER

.

    10-28 14:33:12.550: E/AndroidRuntime(3171): FATAL EXCEPTION: IntentService[SendOutstandingTransactions]
    10-28 14:33:12.550: E/AndroidRuntime(3171): android.database.sqlite.SQLiteException: no such table: TABLETRANSACTIONS (code 1): , while compiling: ALTER TABLE TABLETRANSACTIONS RENAME TO OldTABLETRANSACTIONS
    10-28 14:33:12.550: E/AndroidRuntime(3171):     at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method)
    10-28 14:33:12.550: E/AndroidRuntime(3171):     at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:909)
    10-28 14:33:12.550: E/AndroidRuntime(3171):     at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:520)
    10-28 14:33:12.550: E/AndroidRuntime(3171):     at android.database.sqlite.SQLiteSession.prepare(SQLiteSession.java:588)
    10-28 14:33:12.550: E/AndroidRuntime(3171):     at android.database.sqlite.SQLiteProgram.<init>(SQLiteProgram.java:58)
    10-28 14:33:12.550: E/AndroidRuntime(3171):     at android.database.sqlite.SQLiteStatement.<init>(SQLiteStatement.java:31)
    10-28 14:33:12.550: E/AndroidRuntime(3171):     at android.database.sqlite.SQLiteDatabase.executeSql(SQLiteDatabase.java:1719)
    10-28 14:33:12.550: E/AndroidRuntime(3171):     at android.database.sqlite.SQLiteDatabase.execSQL(SQLiteDatabase.java:1650)
    10-28 14:33:12.550: E/AndroidRuntime(3171):     at com.carefreegroup.rr3.LoginValidate$DBHelper.onUpgrade(LoginValidate.java:643)
    10-28 14:33:12.550: E/AndroidRuntime(3171):     at android.database.sqlite.SQLiteOpenHelper.getDatabaseLocked(SQLiteOpenHelper.java:257)
    10-28 14:33:12.550: E/AndroidRuntime(3171):     at android.database.sqlite.SQLiteOpenHelper.getReadableDatabase(SQLiteOpenHelper.java:188)
    10-28 14:33:12.550: E/AndroidRuntime(3171):     at com.carefreegroup.rr3.LoginValidate.queryAllFromCarer(LoginValidate.java:275)
    10-28 14:33:12.550: E/AndroidRuntime(3171):     at com.carefreegroup.rr3.SendOutstandingTransactions.onHandleIntent(SendOutstandingTransactions.java:63)
    10-28 14:33:12.550: E/AndroidRuntime(3171):     at android.app.IntentService$ServiceHandler.handleMessage(IntentService.java:65)
    10-28 14:33:12.550: E/AndroidRuntime(3171):     at android.os.Handler.dispatchMessage(Handler.java:99)
    10-28 14:33:12.550: E/AndroidRuntime(3171):     at android.os.Looper.loop(Looper.java:158)
    10-28 14:33:12.550: E/AndroidRuntime(3171):     at android.os.HandlerThread.run(HandlerThread.java:60)
    10-28 14:33:12.560: E/EmbeddedLogger(508): App crashed! Process: com.carefreegroup.rr3
    10-28 14:33:12.565: E/EmbeddedLogger(508): App crashed! Package: com.carefreegroup.rr3 v13 (2.0.5e)
    10-28 14:33:12.570: E/EmbeddedLogger(508): Application Label: CareFree



private class DBHelper extends SQLiteOpenHelper {

        // database name and version number
        public static final String DB_NAME = "carefreemobiledb.db";
        public static final int DB_VERSION = 51;

        // table names
        public static final String TABLETRANSACTIONS = "transactions";



        public DBHelper() {
            super(context, DB_NAME, null, DB_VERSION);
        }

        @Override
        public void onCreate(SQLiteDatabase db) {
            String sqlToCreateTransactionsTable = String
                    .format("create table %s ( %s INTEGER primary key, %s TEXT, %s TEXT, %s TEXT, %s TEXT, %s TEXT,"
                            + " %s TEXT, %s TEXT, %s INT, %s TEXT, %s TEXT )",
                            TABLETRANSACTIONS, C_ID, C_TYPE, C_COMPANY_ID,
                            C_PERSON_ID, C_NAME, C_TAG_ID, C_STATUS,
                            C_TAG_SCAN_TIME, C_TAG_SENTSERVER_TIME,
                            C_TRANSACTIONS_LATITUDE, C_TRANSACTIONS_LONGITUDE);

            db.execSQL(sqlToCreateTransactionsTable);
            Log.e(TAG, "oncreate " + sqlToCreateTransactionsTable);



        }

        @Override
        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

//          db.execSQL("drop table if exists " + TABLETRANSACTIONS);
//          db.execSQL("drop table if exists " + TABLECARER);
//          db.execSQL("drop table if exists " + TABLETRANSACTIONSMAP);
//          db.execSQL("drop table if exists " + TABLEPHONE);
//          db.execSQL("drop table if exists " + TABLECOMPANYID);
//          db.execSQL("drop table if exists " + TABLEBACKGROUNDSERVICES);
//          db.execSQL("drop table if exists " + TABLEMESSAGE);
//          db.execSQL("drop table if exists " + TABLEDUPLICATETRANSACTIONS);
//          db.execSQL("drop table if exists " + TABLECACHEDROTA);
//          Log.e(TAG, "onUpgrade dropped all tables apart from message and transactions");

            db.execSQL("ALTER TABLE TABLETRANSACTIONS RENAME TO OldTABLETRANSACTIONS");

            db.execSQL("CREATE TABLE TABLETRANSACTIONS(C_ID INTEGER PRIMARY KEY, C_TYPE TEXT," +
                    " C_COMPANY_ID TEXT,C_PERSON_ID TEXT, C_NAME TEXT, C_TAG_ID TEXT, C_STATUS TEXT,C_TAG_SCAN_TIME TEXT, " +
                    "C_TAG_SENTSERVER_TIME INT,C_TRANSACTIONS_LATITUDE TEXT, C_TRANSACTIONS_LONGITUDE TEXT, C_TRANSACTIONS_DRIVER TEXT)");

            db.execSQL("INSERT INTO TABLETRANSACTIONS (C_ID, C_TYPE, C_COMPANY_ID, C_PERSON_ID,  C_NAME,  C_TAG_ID, C_STATUS, C_TAG_SCAN_TIME, " +
                "C_TAG_SENTSERVER_TIME,  C_TRANSACTIONS_LATITUDE, C_TRANSACTIONS_LONGITUDE, C_TRANSACTIONS_DRIVER ) SELECT C_ID, C_TYPE, C_COMPANY_ID, " +
                "C_PERSON_ID,  C_NAME,  C_TAG_ID, C_STATUS, C_TAG_SCAN_TIME, C_TAG_SENTSERVER_TIME,  C_TRANSACTIONS_LATITUDE, C_TRANSACTIONS_LONGITUDE FROM OldTABLETRANSACTIONS");

            db.execSQL("DROP TABLE OldTABLETRANSACTIONS");


            Log.e(TAG, " SQLiteOpenHelper onUpgrade: finished copying old data to new transaction table");

            Toast.makeText(nfcscannerapplication, "SQLiteOpenHelper onUpgrade: finished copying old data to new transaction table", Toast.LENGTH_LONG);
            //this.onCreate(db);

        }

    }

Upvotes: 1

Views: 1267

Answers (1)

Rajesh
Rajesh

Reputation: 15774

The name of your transactions table seem to be "transactions", but you are looking for "TABLETRANSACTIONS", possibly because of converting between Java statements and SQL. Same is the case with other constants like C_ID.

Either change all SQL statements to refer to transactions or use the constant value as follows:

db.execSQL("ALTER TABLE "+TABLETRANSACTIONS+" RENAME TO Old"+TABLETRANSACTIONS);

Upvotes: 1

Related Questions