Android: SQLite Query Return 0 Rows Without Reload?

Question

My application has a SQLite database to contain a list of highways. It shows them all in a list. It first tries to query the database for the roads. If the query doesn't return any, it calls another method that downloads a list from a remote server, and populates the database. After that returns, it immediately queries the database again.

That's how it should work. How it actually works is, the first query always returns nothing. It goes straight to downloading a fresh list, it inserts the list into the database, and queries again. The second query always returns the correct result. The strange thing is, I can repeat the operation without even exiting the application. Using the adb shell, I can read the SQLite3 database on the emulator. The data shown in the database is exactly as expected. But the application is behaving as though the data isn't there? Is there some behaviour I'm not aware of? Here's the code.

RoadsDataSource.java

public class RoadsDataSource {

    private DataStorage data;

    private static Context context;

    public RoadsDataSource() {
        this.data = new DataStorage(RoadsDataSource.context);
    }

    private List<Road> getRoads(Integer state) {
        List<Road> roads = loadRoadsFromDb(state);
        if (roads.isEmpty()) {
            Request api = new Request(RoadsDataSource.context);
            Roads apiRoads = api.fetchRoads(state);
            this.data.storeRoads(apiRoads);
            roads = loadRoadsFromDb(state);
        }
        return roads;
    }

    private List<Road> loadRoadsFromDb(Integer state) {
        SQLiteQueryBuilder query = new SQLiteQueryBuilder();
        query.setTables(Queries.ROAD_STATE_MATCHES);
        Cursor results = query.query(
                this.data.getWritableDatabase(),
                new String[] {Tables.ROADS + "." + Tables.Roads.ID, Tables.ROADS + "." + Tables.Roads.TYPE, Tables.ROADS + "." + Tables.Roads.NUMBER}, 
                Queries.ROADS_BY_STATE,
                new String[] {state.toString()}, null, null, null 
        );

        List<Road> roads = new ArrayList<Road>();

        results.moveToFirst();
        while (!results.isAfterLast()) {
            roads.add(new Road(results.getInt(0), results.getString(1), results.getInt(2)));
            results.moveToNext();
        }
        results.close();

        System.out.println(roads.size());
        return roads;
    }
}

DataStorage.java

public class DataStorage extends SQLiteOpenHelper {
    public void storeRoads(Roads roads) {
        SQLiteDatabase db = this.getWritableDatabase();
        for (Road road : roads.getRoads()) {
            ContentValues roadRow = new ContentValues();
            roadRow.put(Tables.Roads.ID, road.getId());
            roadRow.put(Tables.Roads.TYPE, road.getType());
            roadRow.put(Tables.Roads.NUMBER, road.getNumber());
            try {
                db.insertOrThrow(Tables.ROADS, null, roadRow);
            } catch (SQLException e) {
            }
            ContentValues linkRow = new ContentValues();
            linkRow.put(Tables.StatesRoads.STATE_ID, roads.getState());
            linkRow.put(Tables.StatesRoads.ROAD_ID, road.getId());
            try {
                db.insertOrThrow(Tables.STATES_ROADS, null, linkRow);
            } catch (SQLException e) {
            }
        }
    }
}

I'm not familiar with android's wrappers. Insofar as sqlite in general, I would expect that behavior if a transaction was not completing. I would also not allow those empty catches to pass a code review. You're asking for weird behavior. :) — Tom Kerr
– Tom Kerr, Commented Feb 25, 2012 at 3:23
Concerning the empty catches, I'm in the early stages of development. At this point, I'm just trying to get the functionality in place, then I'll go back and deal with things like Exceptions. — Jonah
– Jonah, Commented Feb 25, 2012 at 5:04
Just a few questions - but in loadRoads, shouldn't you be using getReadableDatabase() and closing the handle opened by the getWriteableDatabase call? — Mo Kargas
– Mo Kargas, Commented Feb 25, 2012 at 5:48
I've had huge issues in the past with not closing the database handle - generally I do all db operations inside an SQLiteOpenHelper subclass, keeping a reference to the db, opening and closing it atomically — Mo Kargas
– Mo Kargas, Commented Feb 25, 2012 at 6:50

RasTheDestroyer · Accepted Answer · 2012-02-25 09:03:23Z

2

Mo Kargas is right. Your db helper should look more like this http://code.google.com/p/android-notes/source/browse/trunk/src/com/bitsetters/android/notes/DBHelper.java?r=10

This may fix your issue though Replace

SQLiteDatabase db = this.getWritableDatabase();

with this

SQLiteDatabase db;
try {
    db = dbHelper.getWritableDatabase();
} catch (SQLiteException e) {
   db = dbHelper.getReadableDatabase();
}

answered Feb 25, 2012 at 9:03

RasTheDestroyer

1,76116 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Mo Kargas · Accepted Answer · 2012-02-26 00:27:26Z

1

+200

I've had huge issues in the past with not closing the database handle - generally I do all db operations inside an SQLiteOpenHelper subclass, keeping a reference to the db, opening and closing it atomically.

answered Feb 26, 2012 at 0:27

Mo Kargas

9807 silver badges24 bronze badges

Collectives™ on Stack Overflow

Android: SQLite Query Return 0 Rows Without Reload?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related