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:

getVersion() returns 0
The isInTransaction() returns true
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!!

...but no tables get created. Are you drawing this conclusion from the error message that is returned when you try to insert, or have you confirmed it for yourself? E.g. have you tried to issue a select against the (empty) table? — Tim
– Tim, Commented Aug 23, 2011 at 13:37
Is it possible that code execution is falling through to the insert statement before the database has actually completed the work of creating the table? — Tim
– Tim, Commented Aug 23, 2011 at 13:43
Hi Tim.. I've pulled the db file out of the /data folder and opened with a SQLite managment software and the db is indeed empty, which confirm why the "select" I'm performing afterward is failing. — Nico Balestra
– Nico Balestra, Commented Aug 23, 2011 at 14:04
About your second point.. I'm putting the "inserts" after the CREATE TABLE statement after executing the DDL statements. How can I be sure they are executed after the DDL have completed? Maybe should I put a "commit" at the end of the last DDL? — Nico Balestra
– Nico Balestra, Commented Aug 23, 2011 at 14:06

Vitaliy Polchuk · Accepted Answer · 2011-10-21 10:14:53Z

2

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

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

answered Oct 21, 2011 at 10:14

Vitaliy Polchuk

1,89819 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

M Granja Over a year ago

Exactly. As the documentation says:(developer.android.com/reference/android/database/sqlite/…)

Andro Selva · Accepted Answer · 2012-04-05 14:05:58Z

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

edited Apr 5, 2012 at 14:05

Andro Selva

54.4k53 gold badges195 silver badges244 bronze badges

answered Aug 24, 2011 at 8:19

Nico Balestra

2663 silver badges13 bronze badges

Comments

Rasel · Accepted Answer · 2011-08-23 11:36:00Z

0

Uninstall your application and install again.

answered Aug 23, 2011 at 11:36

Rasel

15.5k6 gold badges42 silver badges50 bronze badges

1 Comment

Nico Balestra Over a year ago

I've did it many many times.. also checked that the db gets actually deleted once the app has been uninstalled. No success :(

yep · Accepted Answer · 2011-08-23 13:27:05Z

0

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

answered Aug 23, 2011 at 13:27

yep

1,2731 gold badge9 silver badges13 bronze badges

1 Comment

Nico Balestra Over a year ago

Hi.. thanks for your comment. I will try to digg in the "low level" API but this would make the use of SQLiteOpenHelper subclass unuseful.

Collectives™ on Stack Overflow

Android SQLite doesn't create tables using SQLiteOpenHelper

4 Answers 4

1 Comment

Comments

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related