Intermittent SQLiteException: not an error at dbopen

Question

In my app I'm using DB in many situations, but there is one situation in which I'm getting an exception, not every time and could reproduce it (yet).

This is happening only on OS versions 2.3.7 and 2.1-update-1.

The code:

public void removeOldOccurrences() {
        SQLiteDatabase db = dbHelper.getWritableDatabase();
        Long oldTime = System.currentTimeMillis() - VALID_OCCURRENCE_TIME;
        String query = "";
        try {
            query = "DELETE FROM " + LOCATIONS_TABLE + " WHERE not ("
                    + REMEMBERED_FIELD + "=1) " + "and (" + LAST_FIELD + "<"
                    + oldTime + ");";
            db.execSQL(query);
        } catch (Exception e) {
            Log.e(TAG, query);
            e.printStackTrace();
        } finally {
            if (db != null) {
                db.close();
            }
        }
    }

The exception trace is:

android.database.sqlite.SQLiteException: not an error
at android.database.sqlite.SQLiteDatabase.dbopen(Native Method)
at android.database.sqlite.SQLiteDatabase.(SQLiteDatabase.java:1849)
at android.database.sqlite.SQLiteDatabase.openDatabase(SQLiteDatabase.java:820)
at android.database.sqlite.SQLiteDatabase.openOrCreateDatabase(SQLiteDatabase.java:854)
at android.database.sqlite.SQLiteDatabase.openOrCreateDatabase(SQLiteDatabase.java:847)
at android.app.ContextImpl.openOrCreateDatabase(ContextImpl.java:573)
at android.content.ContextWrapper.openOrCreateDatabase(ContextWrapper.java:203)
at android.database.sqlite.SQLiteOpenHelper.getWritableDatabase(SQLiteOpenHelper.java:118)

please help.

Solution 1

I have also received this same error when passing an empty string for query.

Solution 2

This error is coming from the statement getWritableDatabase where you are trying to create / open database.

From the code segment what you have given , i see that you are trying to open the database for each operation.

This is not the right way to do it. You have to open your DB only once before you close it . Normal practise is open it during your other initialisation and take care to close it before exit the app . Once open store the db context and use that in all other operations

for eg : you can have a database manegr class like this :

public DataBaseManager open() throws SQLException {
    try {
        mDbHelper = new DatabaseHelper(mCtx);
        mDb = mDbHelper.getWritableDatabase();

    } catch (Exception ex) {
        ex.printStackTrace();
        Utils.getHomeActivityinstance().finish();

    }
    return this;
}

now when delte need to be called, do not call open again

public boolean deleteRow(String tableName, long rowId) {

    return mDb.delete(tableName, ID + "=" + rowId, null) > 0;
}

Solution 3

Make sure your query doesn't contain trailing empty lines after semicolon ";" of your query.

I had the same issue executing queries inside a Sqlite Client, after poking around I found the problem is that I had two empty lines below the query.

In case anyone encountered the same issue, here are a few pass/fail cases inside Sqlite client (or loading a .sql file ).

Good:

-- test.sql
SELECT * FROM table;

Good:

-- test2.sql
-- notice the empty line below doesn't break the code

SELECT * FROM table;

Good:

-- test3.sql
-- notice the query string is not terminated by semicolon ";"
-- but it does contain trailing empty lines
SELECT * FROM table


-- this line is just a placeholder, in actual file this line should also be empty

Bad:

-- test3.sql
-- notice the query string is terminated with semicolon, 
-- and there are trailing empty lines after that
SELECT * FROM table;


-- this line is just a placeholder, in actual file this line should also be empty

Hope this helps.

Solution 4

It looks like your query is bad on the not.

Looking at the SQLite Docs, according to them there isn't a not in the way you are trying to use it (as a negation of your where).

Your choices for not appear to be:

NOT LIKE
NOT GLOB
NOT REGEXP
NOT MATCH
NOT BETWEEN
NOT IN
IS NOT

And most of those require an expression before them.

WHERE xxxx NOT MATCH yyyy

View more solutions

Comments

[Bush]

Thanks for answering, I understand that this is not the efficient way to use DB but I don't understand the reason that your solution will not get me with the same exception while executing getWritableDatabase(). As I mentioned, I saw this exception only on 2.3.7 and 2.1-update-1 and not every time.

[Bush]

Hey man thank for the answer. I don't think that this is true for 2 reasons: 1.The same query is succeeds everytime except those several cases (most of'em are on 2.3.7). 2.In the same document you linked written: "These are the unary prefix operators: - + ~ NOT". Beyond that, preetha said and was write when he said that I get the exception in the getWritableDatabase() method.

[png]

One possible issue could be "opening a db that is already open" . With my design this issue will not come

[Arkadiusz Cieśliński]

in my case I had only spaces, technically not empty, but the same result

[gotomanners]

Good catch, this helped me.