Update the ID of an Item in the SQLite database

Question

I still can not solve the issue of update in database.

My application has two lists defined in two activities. List A has positions and List B items. The data in both lists are saved to and uploaded from the database. As it could be seen from the code, I don't have any problems with deleting.

When I delete a position in List A, the set of items in List B that belong to this position are deleted as well.

There is a problem with update: the set of items after the deleted ones should be autoincremented but it only works for the first line which comes after the deleted item, others stays unchanged.

Here is my code:

public void deleteItemsFromProduct(long nomer) {

    cursor = DataSource.queryAllinItems();

    if (cursor != null) {

        String [] pat = new String[] { String.valueOf(nomer) }; 
        db.delete(DBHelper.TABLE_ITEMS, DBHelper.COLUMN_NOMER + "=?", pat);


    for (int i=0; i<cursor.getCount(); i++){            
    ContentValues cv = new ContentValues();
    cv.put(DBHelper.COLUMN_NOMER, nomer);

    pat = new String[] { String.valueOf(nomer+1) };

    int updCount = db.update(DBHelper.TABLE_ITEMS, cv, DBHelper.COLUMN_NOMER + "=?", pat);

    Log.d(LOG_TAG, "updated rows count = " + updCount); 
    }           


    if (cursor.moveToFirst()) {

        Log.d(LOG_TAG, "--- Left in the table for items and prices: ---");

        idColIndex = cursor.getColumnIndex(DBHelper.COLUMN_ITEM_ID);
        nameColIndex = cursor.getColumnIndex(DBHelper.COLUMN_ITEM);
        priceColIndex = cursor.getColumnIndex(DBHelper.COLUMN_PRICE);
        nomerColIndex = cursor.getColumnIndex(DBHelper.COLUMN_NOMER);

        do {
            Log.d(LOG_TAG, "item id = " 
            + cursor.getInt(idColIndex)
            + ", item name = "
            + cursor.getString(nameColIndex)
            + ", item price = "
            + cursor.getInt(priceColIndex)
            + ", product nomer = "
            + cursor.getInt(nomerColIndex));

        } while (cursor.moveToNext());
    } else {
        Log.d(LOG_TAG, "Cursor is null");

        cursor.close();

        }
    }

and here Log

11-07 14:37:49.862: D/(1813): item id = 89, item name = б, item price = 0, product nomer = 0
11-07 14:37:49.862: D/(1813): item id = 90, item name = т, item price = 1, product nomer = 1
11-07 14:37:49.922: D/(1813): item id = 91, item name = м, item price = 2, product nomer = 2
11-07 14:37:58.442: D/(1813): MainActivity: onResume()
11-07 14:38:05.382: D/(1813): --- Delete from table for products: ---
11-07 14:38:05.465: D/(1813): deleted row id = 72, name = х, image = /mnt/sdcard/download/sliced-bread-icon.png
11-07 14:38:05.712: D/(1813): updated rows count = 1
11-07 14:38:05.752: D/(1813): updated rows count = 0
11-07 14:38:05.752: D/(1813): --- Left in the table for items and prices: ---
11-07 14:38:05.804: D/(1813): item id = 90, item name = т, item price = 1, product nomer = 1
11-07 14:38:05.822: D/(1813): item id = 91, item name = м, item price = 2, product nomer = 2
11-07 14:38:10.416: D/(1813): MainActivity: onPause()
11-07 14:38:10.622: D/(1813): ChildActivity: onCreate()
11-07 14:38:10.723: D/(1813): item id = 90, item name = т, item price = 1, product nomer = 0

When I delete the product with the id 72, it stays first in the list A (nomer 0) item in the list B with id 89 is also deleted. Item with id=90 gets nomer=0 since its parent takes first position in the list A. But item with id=91 stays nomer=2, but it should be nomer=1. So, autoincrement works only for the next item after deleted, all others stay unchanged.

actually it is not about updating id. I have chained two lists with variable nomer. AdapterView.AdapterContextMenuInfo acmi = (AdapterView.AdapterContextMenuInfo) item.getMenuInfo(); nomer is acmi.id — user1706819, Nov 07 '12 at 15:47
when you click on the first position of the list A acmi.id = 0 becomes nomer=0 for all items that belong to this position in the list B. So, when I delete position in the list A, all items in B under 0 are also deleted. But the problem is in update. I can update the next line but others stay same. — user1706819, Nov 07 '12 at 15:51

score 0 · Answer 1 · answered Nov 07 '12 at 16:06

In this code:

db.update(..., DBHelper.COLUMN_NOMER + "=?", pat);

the parameter is a string, but the values in the column are numbers, so they will never compare as equal.
(Forcing all parameters to be strings is a design bug of the Android SQLite API.)

To ensure that parameters have the correct type, use something like this:

db.update(..., DBHelper.COLUMN_NOMER + "= CAST(? AS INTEGER)", pat);

score 0 · Answer 2 · answered Nov 08 '12 at 09:31

As I thought solution lies between the good knoweledge of sqlite lingvo and programming logic.Here it is:

`           for (int i = 0; i <= cursor.getCount(); i++) {

                ContentValues cv = new ContentValues();

                cv.put(DBHelper.COLUMN_NOMER, nomer + i);

                int updCount = db.update(DBHelper.TABLE_ITEMS, cv,
                        DBHelper.COLUMN_NOMER + "=?",
                        new String[] { String.valueOf((nomer + i) + 1) });

                Log.d(LOG_TAG, "updated rows count = " + updCount);
            }
`

So, we change the value of all items with ((nomer + i) + 1) to (nomer + i).

Update the ID of an Item in the SQLite database

2 Answers2