Ormlite after updated table I can't use query for all method

Question

I'm developing an application which is using ormlite and sqlcipher. I need to rename column without user loosing all it's data. But after I update the column - I can't use ormlite's queryForAll method. I tried searching for the problem, but there was no luck at all. Thank you in advance.

Here is my update table method:

    List<DB_RecordItem> recordItems;
    List<JSONObject> objects = new ArrayList<>();
    try {
        // Add new column - file_id (instead of 'file')
        db.execSQL(String.format("ALTER TABLE %s ADD COLUMN %s INTEGER;", DB_RecordItem.DB_TABLE, DB_File.DB_FILE));

        // Get all items, which are going to be stored later.
        recordItems = Database.getRecordItems().queryForAll();

        Dao<DB_RecordItem, String> dao = DaoManager.createDao(connectionSource, DB_RecordItem.class);
        Log.i("DatabaseLog", "Before delete table: " + dao.queryRaw("PRAGMA table_info(db_recorditem)").getResults().toString());

        QueryBuilder<DB_RecordItem, String> qb = dao.queryBuilder();

        // Query for all items.
        for (DB_RecordItem item : recordItems) {
            JSONObject jsonObject = new JSONObject(item.toJson());

            Log.i("DatabaseLog", "Before: " + jsonObject.toString());

            qb.where().eq("id", item.getId());
            CloseableIterator<DB_RecordItem> iterator = dao.iterator(qb.prepare());
            // Get old file id, since model id is changed to "file_id"
            GenericRawResults<String[]> rawResults = dao.queryRaw("select file from db_recorditem where id = " + item.getId());

            try {
                int file_id = Integer.valueOf(rawResults.getResults().get(0)[0]);
                    if (file_id != -1) {
                        // If file_id it is not -1, then the recrod item had a file_id.
                        jsonObject.put(DB_File.DB_FILE, file_id);
                    }
            } finally {
                iterator.closeQuietly();
            }

            // Set all ids to 0, autoincrement will do its thing.
            jsonObject.remove(DB_Core.DB_ID);
            jsonObject.put(DB_Core.DB_ID, 0);
            // Store new data.
            objects.add(jsonObject);
        }

        // Drop old table, with column that we don't need any more.
        TableUtils.dropTable(connectionSource, DB_RecordItem.class, false);
        // Create new table with correct column naming.
        TableUtils.createTable(connectionSource, DB_RecordItem.class);

        Log.i("DatabaseLog", "New table: " + dao.queryRaw("PRAGMA table_info(db_recorditem)").getResults().toString());

        // Store all data.
        for (JSONObject object : objects) {
            Log.i("DatabaseLog", "After: " + object.toString());
            new DB_RecordItem(object).save();
        }

        Log.i("DatabaseLog", "Done!");
    } catch (Exception e) {
        Log.i("DatabaseLog", "Crash.");
        e.printStackTrace();
    }

Code above seems to work perfectly without any errors. However I'm getting error after I try to proceed with this line:

// getRecordItems() returns Dao<DB_RecordItem, Integer>
Database.getRecordItems().queryForAll()

It appears that the problem is with the new insrted field. If the fields don't have the value of 'file_id' everything works fine. But if it does - the error reproduces. Is there a chance, that I need to regenerate ormlite config_file ?

My error message is:

E/CursorWindow﹕ Bad request for field slot 0,17. numRows = 1, numColumns = 17
W/System.err﹕ java.lang.IllegalStateException: get field slot from row 0 col 17 failed
W/System.err﹕ at net.sqlcipher.CursorWindow.getLong_native(Native Method) 
W/System.err﹕ at net.sqlcipher.CursorWindow.getInt(CursorWindow.java:522)
W/System.err﹕ at net.sqlcipher.AbstractWindowedCursor.getInt(AbstractWindowedCursor.java:95)
W/System.err﹕ at android.database.CursorWrapper.getInt(CursorWrapper.java:102)
W/System.err﹕ at com.j256.ormlite.sqlcipher.android.AndroidDatabaseResults.getInt(AndroidDatabaseResults.java:168)
W/System.err﹕ at com.j256.ormlite.field.types.IntegerObjectType.resultToSqlArg(IntegerObjectType.java:37)
W/System.err﹕ at com.j256.ormlite.field.BaseFieldConverter.resultToJava(BaseFieldConverter.java:24)
W/System.err﹕ at com.j256.ormlite.field.FieldType.resultToJava(FieldType.java:819)
W/System.err﹕ at com.j256.ormlite.stmt.mapped.BaseMappedQuery.mapRow(BaseMappedQuery.java:60)
W/System.err﹕ at com.j256.ormlite.stmt.SelectIterator.getCurrent(SelectIterator.java:270)
W/System.err﹕ at com.j256.ormlite.stmt.SelectIterator.nextThrow(SelectIterator.java:161)
W/System.err﹕ at com.j256.ormlite.stmt.StatementExecutor.query(StatementExecutor.java:202)
W/System.err﹕ at com.j256.ormlite.stmt.StatementExecutor.queryForAll(StatementExecutor.java:118)
W/System.err﹕ at com.j256.ormlite.dao.BaseDaoImpl.queryForAll(BaseDaoImpl.java:241)
W/System.err﹕ at database.Database$ObjectsIterator.start(Database.java:221)
W/System.err﹕ at fragments.F_BackupLocal$LocalBackup.doInBackground(F_BackupLocal.java:429)
W/System.err﹕ at fragments.F_BackupLocal$LocalBackup.doInBackground(F_BackupLocal.java:368)
W/System.err﹕ at android.os.AsyncTask$2.call(AsyncTask.java:288)
W/System.err﹕ at java.util.concurrent.FutureTask.run(FutureTask.java:237)
W/System.err﹕ at android.os.AsyncTask$SerialExecutor$1.run(AsyncTask.java:231)

I'll update this post as long as I will find out where the problem lies.

Gediminas Bublys · Answer 1 · 2015-09-13T15:14:59.487

I finally fixed the problem.

According to this documentation - In SQLite, all you can do is rename a table name and add a new column. You can’t rename or remove a column or change the constraints.

My final solution was to append the table with new field, and leave the old one as dead.

private void renameDB_Record_Item_Columns(ConnectionSource connectionSource) {
    List<DB_RecordItem> recordItems;
    List<JSONObject> objects = new ArrayList<>();
    try {
        Dao<DB_RecordItem, String> dao = DaoManager.createDao(connectionSource, DB_RecordItem.class);
        dao.executeRaw(String.format("ALTER TABLE %s ADD COLUMN %s INTEGER;", "db_recorditem", "file_id"));

        recordItems = Database.getRecordItems().queryForAll();
        QueryBuilder<DB_RecordItem, String> qb = dao.queryBuilder();
        // Query for all items.
        for (DB_RecordItem item : recordItems) {
            qb.where().eq("id", item.getId());
            CloseableIterator<DB_RecordItem> iterator = dao.iterator(qb.prepare());

            GenericRawResults<String[]> rawResults = dao.queryRaw("select file from db_recorditem where id = " + item.getId() + ";");

            try {
                int file_id = Integer.valueOf(rawResults.getResults().get(0)[0]);
                    if (file_id != -1) {
                        // If file_id it is not -1, then the recrod item had a file_id.
                        item.assignFile(new DB_File().setId(file_id));
                        item.update();
                    }
            } finally {
                iterator.closeQuietly();
            }
        }
    } catch (Exception e) {
        Log.i("DatabaseLog", "Crash.");
        e.printStackTrace();
    }
}

Anyway if someone would find a proper way how to drop and recreate table, please share it. :)

I have same problem and if you want to keep old data for first time app crashes. — Mahdi, Nov 06 '18 at 12:05

Ormlite after updated table I can't use query for all method

1 Answers1