Databases
StackOverflow2013
Postsdbo
POError when querying a created dbView (not table)!
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POError when querying a created dbView (not table)!
    primarykey
    data
    text
    <p>For context see <a href="https://stackoverflow.com/questions/4258517/what-is-best-1-table-per-record-or-1-table-with-all-records-linked-with-foreign">post</a> </p> <p>Following the awsome sugestion provided by yock, I changed my database design to use foreign keys. You can check the overall database design in this <a href="https://stackoverflow.com/questions/4258517/what-is-best-1-table-per-record-or-1-table-with-all-records-linked-with-foreign">post</a>.</p> <p>So I have 2 tables.</p> <pre><code> TABLE(1) TABLE (2) +--------------------------------------+ +-----------+ | SURVEYS TABLE | | ICONS | +----+------+-------------+------------+ +----+------+ | ID | name | description | iconID | | ID | ICON | +----+------+-------------+------------+ +----+------+ | | | | FOREIGN KEY| +--------------------------------------+ </code></pre> <p>I constructed a VIEW using the two tables.</p> <pre><code>public static final String VIEW_SURVEYS = "surveysview"; public static final String VIEW_SURVEYS_CREATE = "CREATE VIEW " + VIEW_SURVEYS + " AS SELECT " + TABLE_SURVEYS + "." + KEY_ROWID + " AS _id, " + TABLE_SURVEYS + "." + KEY_NAME + ", " + TABLE_SURVEYS + "." + KEY_DESCRIPTION + ", " + TABLE_ICONS + "." + KEY_ICON + " " + "FROM " + TABLE_SURVEYS + " JOIN " + TABLE_ICONS + " ON " + TABLE_SURVEYS + "." + KEY_ICONID + " = " + TABLE_ICONS + "." + KEY_ROWID; </code></pre> <p>The tables and views are called in my Adapter constructor.</p> <pre><code>private static class DatabaseHelper extends SQLiteOpenHelper { DatabaseHelper(Context context) { super(context, DATABASE_NAME, null, DATABASE_VERSION); } @Override public void onCreate(SQLiteDatabase db) { // TABLES db.execSQL(TABLE_ICONS_CREATE); db.execSQL(TABLE_SURVEYS_CREATE); // VIEWS db.execSQL(VIEW_SURVEYS_CREATE); } </code></pre> <p>And this is the method used to query the view.</p> <pre><code> public Cursor getSurveys() { return db.query(VIEW_SURVEYS, new String[] { KEY_ROWID, KEY_NAME, KEY_DESCRIPTION, KEY_ICON}, null, null, null, null, null); } </code></pre> <p>Which is called in my activity through a cursor</p> <pre><code> DBAdapter db = new DBAdapter(this); db.open(); // Opens db session Cursor survey_cursor = db.getSurveys(); startManagingCursor(survey_cursor); </code></pre> <p>The problem is when I try run the above code, my program crashes. The line at it crashes is Cursor survey_cursor = db.getSurveys();</p> <p>And Catlog show</p> <pre><code>11-25 16:19:42.324: ERROR/AndroidRuntime(6146): Caused by: android.database.sqlite.SQLiteException: no such table: surveysview: , while compiling: SELECT _id, name, description, icon FROM surveysview </code></pre> <p>I reread the code countless times and can't find anything wrong. What am I missing?</p> <p>Here's the output for SELECT * FROM sqlite_master</p> <pre><code>table|android_metadata|android_metadata|3|CREATE TABLE android_metadata (locale TEXT) table|fieldtype|fieldtype|4|CREATE TABLE fieldtype (_id integer primary key autoincrement, type text not null) table|sqlite_sequence|sqlite_sequence|5|CREATE TABLE sqlite_sequence(name,seq) table|editicons|editicons|6|CREATE TABLE editicons (_id integer primary key autoincrement, icon text not null) table|surveys|surveys|7|CREATE TABLE surveys (_id integer primary key autoincrement, name text not null, description text, editableid integer not null, FOREIGN KEY(editableid) REFERENCES editicons(_id)) table|questions|questions|8|CREATE TABLE questions (_id integer primary key autoincrement, field text not null, typeid text not null, survey_id integer not null, FOREIGN KEY(typeid) REFERENCES fieldtype(_id)FOREIGN KEY(survey_id) REFERENCES surveys(_id)) table|choices|choices|9|CREATE TABLE choices (choice text, question_id integer not null, FOREIGN KEY(question_id) REFERENCES questions(_id)) table|answers|answers|10|CREATE TABLE answers (choice_id integer not null, FOREIGN KEY(choice_id) REFERENCES choices(_id)) view|surveysview|surveysview|0|CREATE VIEW surveysview AS SELECT surveys._id AS _id, surveys.name AS name, surveys.description AS description, editicons.icon AS icon FROM surveys JOIN editicons ON surveys.editableid = editicons._id </code></pre> <p>(this output reflects some changes I made to the code since I added " AS " after each column)</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USCommunity
    1. USTivie
    plurals
    1. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POError when querying a created dbView (not table)!
      1. USTivie
      1. VTFavorite
    2. VO
      singulars
      1. POError when querying a created dbView (not table)!
      1. USKaern Stone
      1. VTFavorite
    3. VO
      singulars
      1. POError when querying a created dbView (not table)!
      1. This table or related slice is empty.
      1. VTApproveEditSuggestion
    1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload