Android SQLite doesn't create tables using SQLiteOpenHelper

Question

I've posted this question to the android-developer mailing list without any success. So let's try here :)

What I'm trying to achieve here is very simple. Using a subclass of SQLiteOpenHelper I'm trying to create a fresh new DB if it doesn't exist so I'm relying on the onCreate()-method exposed by that class. Here's my code:

public class CurrencyStorageHelper extends SQLiteOpenHelper {

private static final String TAG = "CurrencyStorageHelper";
private static final int DATABASE_VERSION = 1;

public static final String DATABASE_NAME = "easycurrencyconverter.db";
public static final String COL_ID = "_id";

public static final String COL_CURRENCY_CODE = "currency_code";
public static final String COL_CURRENCY_VALUE = "currency_value";
public static final String COL_LAST_UPDATE = "last_update";
public static final String COL_LAST_DOWNLOADED = "last_downloaded";
public static final String COL_IS_TARGET_CURRENCY = "is_target_currency";
public static final String COL_CURRENCY_POSITION = "position";

public static final String TABLE_DEFAULT_CURRENCIES = "defaultcurrencies";
public static final String TABLE_CURRENCIES = "currencyhistory";
public static final String TABLE_CURRENCY_DEFINITION = "currencydefinition";

public static final String COL_DEFINITION_CODE = "code";
public static final String COL_DEFINITION_FULL_NAME = "full_name";




private static final String DATABASE_CREATE_1 = 
            "CREATE TABLE " + TABLE_CURRENCIES + " (" + COL_ID + " INTEGER PRIMARY KEY AUTOINCREMENT " +
                                   ", " + COL_CURRENCY_CODE + " TEXT NOT NULL" +
                                   ", " + COL_CURRENCY_VALUE + " REAL NOT NULL" +
                                   ", " + COL_LAST_UPDATE + " REAL NOT NULL" +
                                   ", " + COL_LAST_DOWNLOADED + " REAL NOT NULL); ";
private static final String DATABASE_CREATE_2 = 
            "CREATE TABLE " + TABLE_DEFAULT_CURRENCIES + " (" + COL_ID + " INTEGER PRIMARY KEY AUTOINCREMENT " +
                                   ", " + COL_CURRENCY_CODE + " TEXT NOT NULL" +
                                   ", " + COL_CURRENCY_VALUE + " REAL NOT NULL" +
                                   ", " + COL_CURRENCY_POSITION + " INTEGER NOT NULL" +
                                   ", " + COL_LAST_UPDATE + " REAL NOT NULL" +
                                   ", " + COL_LAST_DOWNLOADED + " REAL NOT NULL" + 
                                   ", " + COL_IS_TARGET_CURRENCY + " NUMERIC ); ";
private static final String DATABASE_CREATE_3 =
            "CREATE TABLE " + TABLE_CURRENCY_DEFINITION + " (" + COL_ID + " INTEGER PRIMARY KEY AUTOINCREMENT " +
                                   ", " + COL_DEFINITION_CODE + " TEXT NOT NULL" +
                                   ", " + COL_DEFINITION_FULL_NAME + " TEXT NOT NULL); ";


private static final String DATABASE_DROP = "DROP TABLE IF EXISTS " + TABLE_CURRENCIES + "; " +
                                            "DROP TABLE IF EXISTS " + TABLE_DEFAULT_CURRENCIES + ";" + 
                                            "DROP TABLE IF EXISTS " + TABLE_CURRENCY_DEFINITION + ";";

public CurrencyStorageHelper(Context context) {
    super(context, DATABASE_NAME, null, DATABASE_VERSION);

}

@Override
public void onOpen(SQLiteDatabase db) {
    super.onOpen(db);
    Log.d(TAG, "Opening the database... " + db.getPath() + " version " + db.getVersion());
    db.setLockingEnabled(true);
} 


@Override
public void onCreate(SQLiteDatabase database) {

    if (database.isDbLockedByCurrentThread()){
        Log.d(TAG, "Database locked by current thread...");
    }


    if (database.isDbLockedByOtherThreads()){
        Log.e(TAG, "Database locked by OTHER thread...");
    }

    if (database.isOpen()){
        Log.d(TAG, "OK.. Database open");
    }

    if (database.isReadOnly()){
        Log.e(TAG, "The database is read only");
    }

    if (database.inTransaction()){
        Log.e(TAG, "Why id the databse in transaction???");
    }

    Log.d(TAG, "Call to onCreate");

    Log.d(TAG, "Creating table..." + DATABASE_CREATE_1);

    database.beginTransaction();
    database.execSQL(DATABASE_CREATE_1);
    database.endTransaction();

    Log.d(TAG, "Creating table..." + DATABASE_CREATE_2);
    database.beginTransaction();
    database.execSQL(DATABASE_CREATE_2);
    database.endTransaction();

    Log.d(TAG, "Creating table..." + DATABASE_CREATE_3);        
    database.beginTransaction();
    database.execSQL(DATABASE_CREATE_3);
    database.endTransaction();
    insertInitialCurrencies(database);
}

/**
 * Create the default values to be shown to the user main activity when the application
 * is ran for the first time and no currencies have been downloaded yet.
 * @param database
 */
private void insertInitialCurrencies(SQLiteDatabase database){

    int i = 1;
    //TODO The fact that USD is the target currency is arbitrary. Maybe we should read user's
    //locale to get her currency
    insertDefaultCurrency(database, "USD", "U.S. Dollar", 1, i++, true, (new Date()).getTime(), (new Date()).getTime(), true, false);
    insertDefaultCurrency(database, "EUR", "Euro", 1, i++, false, (new Date()).getTime(), (new Date()).getTime(), false, false);
    insertDefaultCurrency(database, "GBP", "British Pound", 1, i++, false, (new Date()).getTime(), (new Date()).getTime(), false, false);
    insertDefaultCurrency(database, "JPY", "Japanese Yen", 1, i++, false, (new Date()).getTime(), (new Date()).getTime(), false, false);
    insertDefaultCurrency(database, "CNY", "Chinese Yuan", 1, i++, false, (new Date()).getTime(), (new Date()).getTime(), false, false);
    insertDefaultCurrency(database, "CHF", "Swiss Franc", 1, i++, false, (new Date()).getTime(), (new Date()).getTime(), false, true);


}


    /**
 * Insert a new currency value which will be shown on the user main activity.
 * 
 */
private void insertDefaultCurrency(SQLiteDatabase database, 
                            String currencyCode, 
                            String currencyFullName, 
                            double value, 
                            int position,
                            boolean isTargetCurrency,
                            long lastModifiedDate,
                            long lastDownloadedDate,
                            boolean startTransaction,
                            boolean endTransaction){
    ContentValues vals = new ContentValues();
    vals.put(COL_CURRENCY_CODE, currencyCode);
    vals.put(COL_CURRENCY_VALUE, value);
    vals.put(COL_LAST_DOWNLOADED, lastDownloadedDate);
    vals.put(COL_LAST_UPDATE, lastModifiedDate);
    vals.put(COL_IS_TARGET_CURRENCY, (isTargetCurrency? 1 : 0));
    vals.put(COL_CURRENCY_POSITION, position);

    if (startTransaction){
        database.beginTransaction();
    }
    Log.d(TAG, "Adding default currency..." + currencyCode);
    database.insertOrThrow(TABLE_DEFAULT_CURRENCIES, null, vals);

    if (endTransaction){
        database.endTransaction();
    }
}

What's happening here is that: The db file is actually created (sizing 3072 bytes) but no tables get created so when I lunch a method to query the DB I get the exception

android.database.sqlite.SQLiteException: no such table.

However I don't get any SQL exception so I have no idea why the tables don't get created.

Also, the onCreate()-method is executed every time I ran my application. Should it be performed only once when the DB doesn't exists? Why is it called all the time?

Debugging the application I've noticed that after opening the DB:

1. getVersion() returns 0
2. The isInTransaction() returns true
3. The SQLiteDatabase-instance has a private attribute mStackTrace set to DatabaseObjectNotClosedException, but I have no cursor opened yet.

All this happens with the emulator as well as my mobile phone (Samsung Galaxy S2) so there's something I'm missing here.

Thank you for any help/clue you might give to me!!

Answer 1

Apparently it's working now. I've put a "COMMIT;" after the last CREATE TABLE. I'm not sure why I need to force the commit but this eventually let my code working. However looking at the log I'm getting a non blocking exception:

08-23 15:45:17.714: ERROR/Database(18195): Failure 1 (cannot rollback - no transaction is active) on 0x1a3558 when executing 'ROLLBACK;'

but it's not preventing my code to be executed.. by now.

Thank you all!!

Nico

Answer 2

You are missing setTransactionSuccessful() call. Please follow the pattern:

   db.beginTransaction();
   try {
     ...
     db.setTransactionSuccessful();
   } finally {
     db.endTransaction();
   }

Answer 3

Take a look at openOrCreateDatabase method. This would probably make more sense to override, just hitch onto the portion of the code where it creates.

http://developer.android.com/reference/android/database/sqlite/SQLiteDatabase.html

Answer 4

Uninstall your application and install again.