Update issue

[Ryan Bis]

It seems the Update call to the db is not actually updating the db. As far as the app is concerned, the call worked.

In my situation, I make a call to the Db to get a String from the table. I do some processing, then Update the same table with a flag. This is run in a loop, so if that update ISN'T written to the db immediately, it will loop indefinitely.

I'm using a SqliteQueue to manage all jobs. This is the update call:

dbQueue.execute(new SqliteJob<Object>() {
                @Override
                protected Object job(SQLiteConnection db) throws Throwable {
                    long timeInSeconds = System.currentTimeMillis() / 1000;
                    SQLiteStatement st = db.prepare("UPDATE table SET datecol=? WHERE datacol=?;");
                    st.bind(1, timeInSeconds);
                    st.bind(2, wrapData(data));
                    st.step();
                    st.dispose();
                    return null;
                }
            }).complete();

The table I'm working in has about 4500 rows in it (though in the same db, there's a table with about 500K rows).

I've tried everything I could think of, I tried queue.flush() to wait for all jobs to finish, but that didn't help at all.

The strange thing is, if I exit the app, wait a bit, then start it again, it pulls the same String from before, but after the processing, it DOES update the db. But the next iteration, it's back to the same behavior.

[Igor Sereda]

I'd check three things:

• the queue is actually started
• the connection is in autocommit-mode inside the job - see SQLiteConnection.getAutoCommit(); if not you must commit a transaction at the end;
• you're actually updating the table - see SQLiteConnection.getChanges()

Hope this helps.

[Ryan Bis]

Thanks for the reply Igor.

1. The queue is most definitely started. I use one instance of the queue throughout the app. It has to read from the db before trying to do this update.

2. I added a check to make sure autocommit-mode is on, and it is. There is one method in my app which uses a transaction, and that properly commits at the end.

3. It appears that it's not actually updating the table. I added a log statement right after the step() method to display the rows modified, and sure enough, it comes up with 0. This is strange, because sometimes it will work. Usually have to close the app, then try again, and I can get one to work, then they fail after that.

I usually shut all logging off to quite the console, but I enabled it in this case, and I'm not seeing anything output after that update (in fact all I see are messages when the db is first instantiated, then nothing after that).

[Igor Sereda]

I believe the problem should be in the code, in the WHERE condition and the data you're comparing against. Make sure the data is there. Capture the parameters in the debugger and see if sqlite3 command-line tool will find anything for that value.

[Ryan Bis]

My bad, sorry for the bother. I was wrapping all the Strings with a single quote. Once I removed those, and stopped wrapping, things are running well again. Thank you for your time!