android - SQLite rawQuery causing problems

Question

posted below is the code that I'm having problems with. I have narrowed the issue down to the cursor = db.rawQuery() statement.

Why is the statement incorrect? The setup of the database seems correct, but I cannot seem to query it. I have tried both db.query() and db.rawQuery().

Because the app simply crashes when run on a physical device (the Motorola Atrix that I'm using for testing) is it possible that a few models don't ship with SQLite?

public class Database {
    private static final String ID_DATE = "id_date";
    private static final String ROUND = "round";
    private static final String CATEGORY = "category";
    private static final String VALUE = "value";
    private static final String ANSWER = "answer";
    private static final String QUESTION = "question";
    private static final String DB_NAME = "database.db";
    private static final String DB_TABLE = "clues";
    private static final String DB_PATH = "/data/data/es.foo.bar/databases/" + DB_NAME;
    private static final int DB_VERSION = 1;
    private static class DatabaseHelper extends SQLiteOpenHelper {
        private Context c = null;
        DatabaseHelper(Context context) {
            super(context, DB_NAME, null, DB_VERSION);
            this.c = context;
        }
        public void onCreate(SQLiteDatabase db) {
            try {
                InputStream is = c.getAssets().open(DB_NAME);
                OutputStream os = new FileOutputStream(DB_PATH);
                byte[] buffer = new byte[1024];
                while (is.read(buffer) > 0) {
                    os.write(buffer);
                }
                os.flush();
                os.close();
                is.close();
            } catch (java.io.IOException ex) {  }
        }
        public void onUpgrade(SQLiteDatabase db, int old_v, int new_v) {
        }
    }
    private final Context context;
    private DatabaseHelper helpme;
    private SQLiteDatabase db = null;
    public Database(Context c) {
        this.context = c;
    }
    public Database open() throws SQLException {
        helpme = new DatabaseHelper(context);
        db = helpme.getWritableDatabase();
        return this;
    }
    public void close() { helpme.close(); }
    public boolean askfordata() {
        Cursor cursor = null;
        cursor = db.rawQuery("select * from clues where question match \'foo\'", null);
        if (cursor != null) {
            return true;
        }
        return false;
    }
}

and the main (and only) activity for the app:

public class Main extends Activity {
    Database db = null;
    public void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.main);
        TextView hw = (TextView) findViewById(R.id.helloworld);
        db = new Database(this);
        db.open();
        if (db != null) {
            if (db.askfordata()) hw.setText("Worked.");
        }
    }
    public void onDestroy() {
        db.close();
        super.onDestroy();
    }
}

ADB logcat output:

01-20 23:35:27.183 22155 22155 E AndroidRuntime: FATAL EXCEPTION: main 01-20 23:35:27.183 22155 22155 E AndroidRuntime: java.lang.RuntimeException: Unable to start activity ComponentInfo{es.foo.bar/es.foo.bar.Main}: android.database.sqlite.SQLiteException: no such table: clues: , while compiling: select * from clues where question match 'foo'

Does that mean that my table was never created? I moved my table from my /assets/ folder to the proper location in the onCreate method. I can use sqlite3 to view the .db file on my computer, and list the tables there. That shows all my tables:

clues clues_content clues_segdir clues_segments

Note: I left out the imports to save space. Thanks in advance for any insights.

Answer 1

Have you checked the database in your assets folder to make sure it has been created correctly? You can look at it quickly with a tool like the Sqlite plugin for Firefox

Update

Modify this code:

InputStream is = c.getAssets().open(DB_NAME);
OutputStream os = new FileOutputStream(DB_PATH);

to be:

InputStream is = c.getAssets().open(DB_NAME);
this.getReadableDatabase();
OutputStream os = new FileOutputStream(DB_PATH);

I think your copy is failing, you just missed it in the logs. Uninstall your app before you try again.

Answer 2

see http://www.sqlite.org/lang_createtable.html

    Cursor cursor = null;
    cursor = db.rawQuery("CREATE TABLE IF NOT EXISTS clues (question TEXT, answer TEXT)", null);
    if (cursor != null) {
        return true;
    }
    return false;

is required to actually create the table