SQLite for Android custom table view (SQL VIEW, not Android view) discrepancy?

Question

I've created a custom view in an SQLite database for an Android application.
I'm using Sqliteman on Ubuntu to test my SQL statements before I put them in my app.
I'm trying to do a simple select statement on my view.
The select statement works fine in SQLiteman but when I put the same statement in my code it throws an error.

The statement:

select * from item_view where parent_item_id = 0;

The view (converted to Java as a String):

"create view if not exists item_view as select " +
    "item._id, item.status, item.name, item.position, " +
    "item.parent_item_id, item.note_id, item.other_id, " + 
    "note.contents, other.priority " +
"from item, note, other where item.note_id = note._id and item.other_id = other._id"

The error:

07-16 14:21:15.153: ERROR/AndroidRuntime(5054): Caused by: android.database.sqlite.SQLiteException: no such column: parent_item_id: , while compiling: SELECT * FROM item_view WHERE parent_item_id = 0

I first tried calling the field item.parent_item_id in my select statement, but that didn't work.
Then I pulled the db and opened it with Sqliteman.
The fields were listed as they were in the original tables (_id, status, name, etc.)
So I ran the SQL above in Sqliteman and was able to retrieve the appropriate data no problem but I can't get it to work in my app either way.
I also noticed that dropping the view as a DROP TABLE command worked in SQLiteman but not in my app.
I'm wondering if I'm maybe missing some other VIEW specific functionality.
I didn't see any in either the android documentation or any SQL documentation though.

Technically I could just do this with a more complex SQL call using the original tables, but all my tables follow certain guidelines so that I can dynamically generate SQL calls. I'd like to have the view tables work to keep my code uniform and always reusing the same calls to avoid maintenance and other buggy issues from duplicate code.

Answer 1

I can confirm a similar error: I have the same database running under MS SQL, MySQL, and SQLite3. [The application 'Web Bones' can be viewed at georgiansoftware.com user/password 'Guest'/'Guest2009'.] I am using SQLite Manager to view and administer the database (32 tables, 17 views). All my sql statements have been tested as error-free in SQLite Manager. Initially, I thought Android was having a problem 'starting my activity' in response to an entry in an AutoCompleteTextview. But, when I hard-coded the view query to happen immediately following an all-table query, the error was repeatable!

Did you ever find a solution to your problem other than to re-write the query to avoid the use of the SQLite view?

Answer 2

It turns out that you need to specifically name each field you're creating so that android can treat it like a regular table. The solution...

"create view if not exists item_view as select " +
    "item._id as _id, item.status as item_status, item.name as item_name, item.position as item_position, " +
    "item.parent_item_id as item_parent_item_id, item.note_id as item_note_id, item.other_id as item_other_id, " + 
    "note.contents as note_contents, other.priority as other_priority " +
"from item, note, other where item.note_id = note._id and item.other_id = other._id"