Populate a list from a database in Android

Question

I’m trying to query a single table database that I’ve created in my code. To the best of my knowledge the database is being created correctly. The query is supposed to be used to populate a ListView but when I try to use the resulting cursor from my query to create SimpleCursorAdapter, it crashes with: java.lang.IllegalArgumentException: column '_id' does not exist. I am assuming this can be traced to the cursor, and also the cursor seems to be empty.

The database is created in the following way within the onCreate() of my implementation of a SQLiteOpenHelper:

db.execSQL("CREATE TABLE " + TABLE_NAME + " (_id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, name TEXT NOT NULL, title TEXT NOT NULL, path TEXT NOT NULL);");

And then the actual query is set up and executed in my DataHelper Class which is used to interact with the database:

public Cursor selectEntryStartsWith(String partialName , String title)
{
    String where = "name LIKE '" + partialName + "%' AND title LIKE '" + title + "'";

    if (title== null || title.equals("")){
        where =  "name LIKE '" + partialName + "%'";
    }
    Cursor cur = mDatabase.query(TABLE_NAME, new String[] {"_id", "name", "title"}, where, null, null, null, "name");

    return cur;
}

The code that uses the cursor is as follows:

Cursor cursor = mDataHelper.selectEntryStartsWith("ex", null); //get all entries that start with "ex"
String [] from = new String [] { "name", "title" };
int [] to = new int [] { R.id.name, R.id.title };
SimpleCursorAdapter adapter = new SimpleCursorAdapter(mContext, R.layout.listview_entry, cursor, from, to);

songList.setAdapter(adapter);

I'm using tabs to this last piece of code is from within the onActivityCreated() of a Fragment; I not that it might be better to extend a ListFragment, but I don't think this is the problem here in particularity.

Sorry in advance if I have missed an information that you may require, I've been banging my head on this problem for some time now.

Kevin Galligan · Accepted Answer · 2011-11-08 03:09:07Z

1

Do you know the data is actually in the database? Run 'adb shell', cd to your data directory '/data/data/[app package name]/databases'. Then run sqlite3 [db file name]. Run some direct sql queries and make sure data exists.

If there is data there, rather than going right to the SimpleCursorAdapter, run some text queries in code, and see if you can access the results.

Once all of that works out, add the ListView stuff as a last step.

Some things to mention. If the user is typing in query values, you need to escape those statement values. Either use selectionArgs in the query statement:

http://developer.android.com/reference/android/database/sqlite/SQLiteDatabase.html#query(java.lang.String, java.lang.String[], java.lang.String, java.lang.String[], java.lang.String, java.lang.String, java.lang.String)

or use my stripped-down apache commons-lang, and the StringEscapeUtils class.

http://www.touchlab.co/blog/android-mini-commons/

Another thing to consider, although if you're not having trouble, its probably not an issue. 'name' and 'title' might be tricky keywords in sql statements.

answered Nov 8, 2011 at 3:09

Kevin Galligan

17.5k6 gold badges46 silver badges69 bronze badges

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

3 Comments

cpretty Over a year ago

I'm not sure that I understand how to determine if there is actually data in my database. My tablet is not rooted, and I don't have permission to access the /data folder through the file explorer in eclipse, am I missing something or is there another way? I assume there is data there but like you said I should try and make sure first. Thanks for your help

Kevin Galligan Over a year ago

Try it in an emulator and you'll be able to access that folder. You can also skip part 1 and query your db in code and just try to loop through results in code rather than putting it into an Adapter, just to make sure you have data. Being able to look at the db is better, though. Another way to get the db is to copy the file in your java code to the sdcard. You should be able to access that.

cpretty Over a year ago

So for some reason I still can't get access to the files through the emulator. As for querying the database in my code, that's what I've been trying to do but the cursor that is returned doesn't seem to be valid. Looking at the cursor during debug, the mCount is -1 after the query to the database, what does this mean about the returned cursor?

cpretty · Accepted Answer · 2011-11-09 15:27:35Z

1

The problem was indeed the database was not set up properly, I tried another method of inserting the data i.e. using ContentValues and inserting directly into the database, as opposed to using the precompiled insert statement I was using before.

The insert method now looks like this:

public long insert(String name, String title) 
{
    ContentValues cv = new ContentValues();
    cv.put("name", name);
    cv.put("title", title);
    return mDatabase.insert(TABLE_NAME, null, cv);

    /* The old code was using this precompiled statement
    mInsertStatement.bindString(1, name);
    mInsertStatement.bindString(2, title);
    return mInsertStatement.executeInsert();
    */
}

answered Nov 9, 2011 at 15:27

cpretty

963 bronze badges

Collectives™ on Stack Overflow

Populate a list from a database in Android

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related