sqlite queries

by tmoriarty » Tue, 01 Jul 2008 13:33:49 GMT


Sponsored Links
 Hi,
I recently ran into a situation querying a database table that I found
odd.  My table had the following fields. (_id (PRIMARY KEY), path,
title, artist, album, genre).  I was writting a querry that would
reurn all of the distict aritists, albums, or genres to an activity
for display to a ListView.  I was using the query method which returns
a Cursor.  I found that I had to include _id in the Cursor or it would
error out once the Cursor was used in a CursorAdapter.  This forced me
to write a slightly more copmplex querry using a GROUP BY and
uitilizing the MAX() function to return an _id that I didn't really
want.  I believe I understand the reason why primary key must be
present, so you can uniqyuly identify a row in the Cursor to allow you
to make changes to the undelying database through the Cursor.
However, why force a hard coded _id.  This seems to be enforcing a
naming question on the primary key. This doesn't seem like good
practice.  Can anyone shed some light onto why this is designed this
way, and what approach people usually take when they want data in a
Cursor for  ListView display and are unconcerned with the primary key
or changing the database in anyway through the Cursor.  Thanks.
Tom
--~--~---------~--~----~------------~-------~--~----~



sqlite queries

by Mark Murphy » Tue, 01 Jul 2008 13:49:39 GMT


 


Could you provide us with the text of the error message? That might help 
us shed some light on the situation.

-- 
Mark Murphy (a Commons Guy)
 http://commonsware.com 
Warescription: All titles, revisions, & ebook formats, just $35/year

--~--~---------~--~----~------------~-------~--~----~


Sponsored Links


sqlite queries

by tmoriarty » Wed, 02 Jul 2008 12:14:34 GMT


 The error message reads as follows:
An error has occurred in com.android.mp3player.
Unable to start activity
ComponentInfo{com.android.mp3player/
com.android.mp3player.BrowseCategory}:
java.lang.IlleaglStateException: Unable to get field slot.

This error only occurs when I return A cursor from the querry without
_id in it.  The documentation for CursorAdapter says it needs _id to
work properly.  If I understand this correctly it probably needs the
primary key so it can commit changes to the Cursor back to the
database.  However, I don't understand why they force the name to be
_id.  I guess that is the root of my question.  If I am not
understanding this correctly I would appreciate any insight.  Thanks.






--~--~---------~--~----~------------~-------~--~----~



sqlite queries

by Mark Murphy » Wed, 02 Jul 2008 12:34:00 GMT


 


Your analysis is probably correct. I was thinking I didn't use _id in my 
TourIt sample project, but it turns out I did. And, if you want a full 
read-write Cursor, you may not have a choice.

But, if you are merely looking to display the results of a query, I can 
think of two workarounds.

You could try renaming your existing primary key to _id in the results 
via appropriate SQL and rawQuery():

"SELECT MyKey AS _id, Other, Columns, Here FROM MyTable WHERE ..."

I haven't tried that, but I don't know how Android would know the 
difference between a "real" _id column and a renamed one.

Or, you could pour the results of your query into a List and use 
ArrayAdapter rather than SimpleCursorAdapter. This wouldn't be great for 
a large result set, of course.

-- 
Mark Murphy (a Commons Guy)
 http://commonsware.com 
Warescription: All titles, revisions, & ebook formats, just $35/year

--~--~---------~--~----~------------~-------~--~----~