CODEWITHSUNDEEP
androidsqliteexceptiongetwritabledatabase
Bush
Bush

Reputation: 2533

Intermittent SQLiteException: not an error at dbopen

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.

Upvotes: 5

Views: 18610

Answers (5)

benjaminz
benjaminz

Reputation: 3228

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.

Upvotes: 2

Xeridea
Xeridea

Reputation: 1136

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

Upvotes: 19

Legoless
Legoless

Reputation: 11112

I spent two hours looking for solution to this problem, which was the reason I could not create table in SQLite database.

My error was that I had "is_selected" attribute and this caused NOT AN ERROR error. Renamed the attribute and it worked.

Upvotes: -1

png
png

Reputation: 4408

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;
}

Upvotes: 4

Barak
Barak

Reputation: 16393

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

Upvotes: 1

Related Questions