Java: line executes before previous line finishes running?

Question

First sorry for my bad English so I'll just ask my question.

I wrote my source

db.execSQL(String.format("DELETE FROM 'myTable' WHERE id=%d", i));
db.execSQL(String.format("UPDATE 'myTable' SET id=%d WHERE id=%d", i, i+1);
refreshList();

refreshList() gets myTable table's rows and set ListAdapter to a ListView with new ArrayList<String>.

but when it goes to refreshList function, it shows a previous database contents.

What seems to be the problem?

EDIT

This is the whole code what I written.

SQLiteDatabase db;

db = mHelper.getWritableDatabase();

db.execSQL(String.format("DELETE FROM '_CTGLIST' WHERE id=%d", i));
db.execSQL(String.format("UPDATE '_CTGLIST' SET id=%d WHERE id=%d", i, i+1));

db.close();

refreshList();

and

private void refreshList() {
    ArrayList<String> mCategoryList = new ArrayList<String>;
    SQLiteDatabase db;
    Cursor cursor;

    db = mHelper.getReadableDatabase();
    cuCategory = db.rawQuery("SELECT * FROM '_CTGLIST' ORDER BY id ASC;", null);
    while (cuCategory.moveToNext()) {
        mCategoryList.add(cuCategory.getString(0));
    }
    db.close();
    //and set list adapter here.
    //i won't write this part of code in here.
}

First, why are you rewriting row IDs? That's a serious code smell. Additionally, without at least the `refreshList()` method, and probably also the code for opening the database connection, it's impossible to tell what's happening. — chrylis -cautiouslyoptimistic-, May 31 '14 at 07:42
Additionally, *don't* build dynamic SQL like this. Use parameterized SQL, always. While there isn't a risk of SQL injection in this case, it's worth getting into good habits. — Jon Skeet, May 31 '14 at 07:56
Can you show us the complete code - how are you handling exceptions in this block? Also, what are you expecting to happen? If I=1234, the you delete this record and then try to set its id to 12345. This should update zero rows as row 1234 has been deleted. — kiwiron, May 31 '14 at 08:05
Ignore my previous comment re Also, what... I misread the question and I can't edit the comment. — kiwiron, May 31 '14 at 08:14
@chrylis I'm first on database related coding so I really don't know what the good code is. — Alfred Woo, May 31 '14 at 08:14
@JonSkeet It don't need to protect from SQL Injection because only me will use this app hehe ;) — Alfred Woo, May 31 '14 at 08:18
OK, i updated this post with the whole code so lease check it — Alfred Woo, May 31 '14 at 08:25
@AlfredWoo: As I say, it's a good habit to get into. Get into it *right away*, rather than excusing the bad code just because you're new to it. The sooner you get into good habits, the easier it'll be. — Jon Skeet, May 31 '14 at 08:29

score 1 · Answer 1 · answered May 31 '14 at 07:45

1

It looks like your query is executed first and not commited and then you are using a new connection in the refreshList method, so it will give you a result for the data that is commited and not the ones that are yet to be commited.

answered May 31 '14 at 07:45

ganaraj

420
3
13

This answer only makes sense refreshList uses a *different* connection (within the same connection/transaction, COMMIT means nothing) – user2864740 May 31 '14 at 08:01
Can you give more details on the refreshList() method as from the post it is not clear whether there is a different connection or it is using the same connection. Hence the specultion. – ganaraj May 31 '14 at 08:07
So there is a strong reason to think that the refreshList is using a new connection, which would be outside of your transaction. TO confirm, would it be possible to commit your transaction before calling refreshList – ganaraj May 31 '14 at 08:10
I updated my thread with the source codes so please check about it. – Alfred Woo May 31 '14 at 08:24
@Alfred Woo: Try begining and closing the transaction and marking the transction as successful before closing the connection. [Refer this link](http://stackoverflow.com/questions/3483123/data-not-inserting-in-sqlite-database-in-android) – ganaraj May 31 '14 at 09:02
@ganaraj what that means? could you give me some detail ways to do that? – Alfred Woo May 31 '14 at 11:12

Java: line executes before previous line finishes running?

1 Answers1