SQLite problem - works perfect in the 1.6 emulator - won't work on the phone (2.2) or 2.0+ emulator

Question

I created a sqlite database to store playlists for a media player I am developing because of extended feature (rather than using the Content Provider). It works perfectly on the 1.6 emulator but FCs on anything higher than 2.0... what has changed that I need to know about as far as opening databases in SDK 2.0+? Here is the logcat.

08-06 19:05:46.365: INFO/SQLiteDatabaseAdapter(4692): DB (playlists.db) copied! 08-06 19:05:46.373: INFO/SQLiteDatabaseAdapter(4692): Try to create instance of database (playlists.db) 08-06 19:05:46.373: INFO/SQLiteDatabaseAdapter(4692): Create or Open database : playlists.db 08-06 19:05:46.389: INFO/SQLiteDatabaseAdapter(4692): instance of database (playlists.db) created ! 08-06 19:05:46.389: INFO/Database(4692): sqlite returned: error code = 1, msg = no such table: playlist 08-06 19:05:46.397: DEBUG/AndroidRuntime(4692): Shutting down VM 08-06 19:05:46.397: WARN/dalvikvm(4692): threadid=1: thread exiting with uncaught exception (group=0x4001d7e0) 08-06 19:05:46.514: ERROR/AndroidRuntime(4692): FATAL EXCEPTION: main 08-06 19:05:46.514: ERROR/AndroidRuntime(4692): java.lang.RuntimeException: Unable to start activity ComponentInfo{com.androidworkz.andamp/com.androidworkz.andamp.andAMP}: android.database.sqlite.SQLiteException: no such table: playlist: , while compiling: SELECT playlist.* FROM playlist ORDER BY id ASC; 08-06 19:05:46.514: ERROR/AndroidRuntime(4692): at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2663) 08-06 19:05:46.514: ERROR/AndroidRuntime(4692): at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:2679) 08-06 19:05:46.514: ERROR/AndroidRuntime(4692): at android.app.ActivityThread.access$2300(ActivityThread.java:125) 08-06 19:05:46.514: ERROR/AndroidRuntime(4692): at android.app.ActivityThread$H.handleMessage(ActivityThread.java:2033) 08-06 19:05:46.514: ERROR/AndroidRuntime(4692): at android.os.Handler.dispatchMessage(Handler.java:99) 08-06 19:05:46.514: ERROR/AndroidRuntime(4692): at android.os.Looper.loop(Looper.java:123) 08-06 19:05:46.514: ERROR/AndroidRuntime(4692): at android.app.ActivityThread.main(ActivityThread.java:4627) 08-06 19:05:46.514: ERROR/AndroidRuntime(4692): at java.lang.reflect.Method.invokeNative(Native Method) 08-06 19:05:46.514: ERROR/AndroidRuntime(4692): at java.lang.reflect.Method.invoke(Method.java:521) 08-06 19:05:46.514: ERROR/AndroidRuntime(4692): at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:868) 08-06 19:05:46.514: ERROR/AndroidRuntime(4692): at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:626) 08-06 19:05:46.514: ERROR/AndroidRuntime(4692): at dalvik.system.NativeStart.main(Native Method) 08-06 19:05:46.514: ERROR/AndroidRuntime(4692): Caused by: android.database.sqlite.SQLiteException: no such table: playlist: , while compiling: SELECT playlist.* FROM playlist ORDER BY id ASC; 08-06 19:05:46.514: ERROR/AndroidRuntime(4692): at android.database.sqlite.SQLiteCompiledSql.native_compile(Native Method) 08-06 19:05:46.514: ERROR/AndroidRuntime(4692): at android.database.sqlite.SQLiteCompiledSql.compile(SQLiteCompiledSql.java:91) 08-06 19:05:46.514: ERROR/AndroidRuntime(4692): at android.database.sqlite.SQLiteCompiledSql.(SQLiteCompiledSql.java:64) 08-06 19:05:46.514: ERROR/AndroidRuntime(4692): at android.database.sqlite.SQLiteProgram.(SQLiteProgram.java:80) 08-06 19:05:46.514: ERROR/AndroidRuntime(4692): at android.database.sqlite.SQLiteQuery.(SQLiteQuery.java:46) 08-06 19:05:46.514: ERROR/AndroidRuntime(4692): at android.database.sqlite.SQLiteDirectCursorDriver.query(SQLiteDirectCursorDriver.java:42) 08-06 19:05:46.514: ERROR/AndroidRuntime(4692): at android.database.sqlite.SQLiteDatabase.rawQueryWithFactory(SQLiteDatabase.java:1345) 08-06 19:05:46.514: ERROR/AndroidRuntime(4692): at android.database.sqlite.SQLiteDatabase.rawQuery(SQLiteDatabase.java:1315) 08-06 19:05:46.514: ERROR/AndroidRuntime(4692): at com.androidworkz.andamp.objects.Playlist.getPlaylists(Playlist.java:75) 08-06 19:05:46.514: ERROR/AndroidRuntime(4692): at com.androidworkz.andamp.andAMP.onCreate(andAMP.java:353) 08-06 19:05:46.514: ERROR/AndroidRuntime(4692): at android.app.Instrumentation.callActivityOnCreate(Instrumentation.java:1047) 08-06 19:05:46.514: ERROR/AndroidRuntime(4692): at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2627) 08-06 19:05:46.514: ERROR/AndroidRuntime(4692): ... 11 more

Here is the dbhelper class

package com.androidworkz.andamp;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteException;
import android.database.sqlite.SQLiteOpenHelper;

public class DBHelper extends SQLiteOpenHelper {

// Android's default system path for your application's database.
private static String DB_PATH = "/data/data/com.androidworkz.andamp/databases/";

private static String DB_NAME = "playlists.db";

public DBHelper(Context context) {

    super(context, DB_NAME, null, 1);
    if (!checkDataBaseExistence()) {
        createDatabase();
    }
}

public void onCreate(SQLiteDatabase db) {
    // Leave this method empty
}

public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    // fill in your code here
}

public void createDatabase() {

    SQLiteDatabase db = null;
    String dbPath = DB_PATH + DB_NAME;
    db = SQLiteDatabase.openDatabase(dbPath, null, SQLiteDatabase.CREATE_IF_NECESSARY);
    db.close();

    db = SQLiteDatabase.openDatabase(dbPath, null, SQLiteDatabase.OPEN_READWRITE);

    db.execSQL("CREATE TABLE [playlist] ("+
            "[id] INTEGER  PRIMARY KEY AUTOINCREMENT NOT NULL,"+
            "[name] VARCHAR(255)  NULL);)");

    db.execSQL("CREATE TABLE [songs] ("+
            "[id] INTEGER  PRIMARY KEY AUTOINCREMENT NOT NULL,"+
            "[playlistId] INTEGER  NULL,"+
            "[songId] INTEGER  NULL,"+
            "[name] VARCHAR(255)  NULL,"+
            "[key] VARCHAR(255)  NULL,"+
            "[duration] INTEGER  NULL,"+
            "[path] VARCHAR(255)  NULL,"+
            "[artistName] VARCHAR(255)  NULL,"+
            "[artistKey] VARCHAR(255)  NULL,"+
            "[albumId] INTEGER  NULL,"+
            "[albumName] VARCHAR(255)  NULL,"+
            "[albumKey] VARCHAR(255)  NULL);");
    db.close();
}

private boolean checkDataBaseExistence() {

    // database to be verified
    SQLiteDatabase dbToBeVerified = null;

    try {
        // get database path
        String dbPath = DB_PATH + DB_NAME;
        // try to open the database
        dbToBeVerified = SQLiteDatabase.openDatabase(dbPath, null,
                SQLiteDatabase.OPEN_READONLY);

    } catch (SQLiteException e) {
        // do nothing since the database does not exist
    }

    // in case it exists, close it
    if (dbToBeVerified != null) {
        // close DB
        dbToBeVerified.close();

    }

    // in case there is a DB entity, the DB exists
    return dbToBeVerified != null ? true : false;
}
}

god I hate this stupid "enter code here"... why can't stackoverflow just use tags like a normal syntax highlighter.

Answer 1

08-06 19:05:46.389: INFO/Database(4692): sqlite returned: error code = 1, msg = no such table: playlist

Are you sure the database is copying correctly, it appears as though the table doesn't exist?

Have you tried copying the data off the phone after execution via DDMS and exploring it to see if the schema is correct?

Answer 2

I have noticed that it can appear that you are missing tables if you try to access the table too soon after startup. There is a definite latency that you need to account for before you attempt to access the table. It drove me crazy.