onCreate() method of SQLiteHelper is never called

Question

I'm creating a database in android using SQLite.

I wrote my code to create a table called products in onCreate() method.

Then when I call add() method to add some values into the table, I'm getting an error that there is no such table called products.

Here is my SQLiteHelper class :

public class MySQLiteHelper extends SQLiteOpenHelper {

    //variable declarations and some code
    ...

    @Override
    public void onCreate(SQLiteDatabase db) {

        // SQL statement to create a products table
        String CREATE_PRODUCTS_TABLE = "CREATE TABLE "+TABLE_NAME+ " ( " +
                "id INTEGER PRIMARY KEY AUTOINCREMENT, " +
                "url TEXT, " +
                "title TEXT, " +
                "price INTEGER );";

        // create products table
        db.execSQL(CREATE_PRODUCTS_TABLE);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        // Drop older table if existed
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME);

        // Create tables again
        onCreate(db);
    }


void add(String url, String title, String price) {


    this.url = url;
    this.title = title;
    this.price = price;

    // 1. get reference to writable DB
    SQLiteDatabase db = this.getWritableDatabase();

    // 2. create ContentValues to add key "column"/value
    ContentValues values = new ContentValues();
    values.put(KEY_URL, url);
    values.put(KEY_TITLE, title);
    values.put(KEY_PRICE, price);

    // 3. insert
    db.insert(TABLE_NAME, // table
            null, //nullColumnHack
            values); // key/value -> keys = column names/ values = column values

    // 4. close
    db.close();
}

I read from a similar question that onCreate() is not a constructor and it is called only when database doesn't exist, but database doesn't exists in my case too, so why is it not called?

I also learnt that we need to call getWritableDatabase() or getReadableDatabase()

But I'm not sure where to make such a call in my code.

I tried to put the 'table creating code' which is in onCreate() in my add() method. For first run, I didn't get anything, but from the next time, I keep getting an error that 'a table already exists'.

Now, how can I ensure that table is created properly only once, before I call my add() method to insert some values. Any help please?

EDIT :

My Logcat reads :

03-07 00:59:22.684    2107-2147/com.example.nikhil.amazon1 W/EGL_emulation﹕ eglSurfaceAttrib not implemented
03-07 00:59:22.684    2107-2147/com.example.nikhil.amazon1 W/OpenGLRenderer﹕ Failed to set EGL_SWAP_BEHAVIOR on surface 0xa5683060, error=EGL_SUCCESS
03-07 00:59:24.922    2107-2120/com.example.nikhil.amazon1 I/art﹕ Background sticky concurrent mark sweep GC freed 1464(89KB) AllocSpace objects, 7(2MB) LOS objects, 7% free, 9MB/9MB, paused 75.596ms total 203.616ms
03-07 00:59:25.338    2107-2413/com.example.nikhil.amazon1 E/SQLiteLog﹕ (1) no such table: products
03-07 00:59:25.339    2107-2413/com.example.nikhil.amazon1 E/SQLiteDatabase﹕ Error inserting price=   24,599.00 title=Google Nexus 5 D821 (16GB, Black) url=http://www.amazon.in/Google-Nexus-D821-16GB-Black/dp/B00GC1J55C/ref=sr_1_1?s=electronics&ie=UTF8&qid=1421161258&sr=1-1&keywords=Google
    android.database.sqlite.SQLiteException: no such table: products (code 1): , while compiling: INSERT INTO products(price,title,url) VALUES (?,?,?)
            at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method)
            at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:889)
            at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:500)
            at android.database.sqlite.SQLiteSession.prepare(SQLiteSession.java:588)
            at android.database.sqlite.SQLiteProgram.<init>(SQLiteProgram.java:58)
            at android.database.sqlite.SQLiteStatement.<init>(SQLiteStatement.java:31)
            at android.database.sqlite.SQLiteDatabase.insertWithOnConflict(SQLiteDatabase.java:1469)
            at android.database.sqlite.SQLiteDatabase.insert(SQLiteDatabase.java:1341)
            at com.example.nikhil.amazon1.MySQLiteHelper.add(MySQLiteHelper.java:86)
            at com.example.nikhil.amazon1.FirstParsing$1.run(FirstParsing.java:71)
            at java.lang.Thread.run(Thread.java:818)

My problem is exactly similar to : 1) Android SQLiteOpenHelper : onCreate() method is not called. Why?

2) SQLiteOpenHelper failing to call onCreate?

3) SQLiteOpenHelper "onCreate" is not called? (the DB does not exist)

But I'm finding it hard to understand it and apply it to my code.

Your code says `"CREATE TABLE TABLE_NAME ( "`. Shouldn't it say `"CREATE TABLE "+TABLE_NAME+" ( "`? — antonio, Mar 06 '15 at 18:55
Oh! Actually I have a variable called TABLE_NAME. I didn't put my whole code here, to avoid complexity. — Nikhil, Mar 06 '15 at 18:56
Yes, but your code is creating a table named "TABLE_NAME", not "products" — antonio, Mar 06 '15 at 18:57
I have` private static final String TABLE_NAME = "products";` in my code. — Nikhil, Mar 06 '15 at 18:58
I think I'm not explaining or I don't understand you, sorry... I mean that `"CREATE TABLE TABLE_NAME ( "` is creating a table named TABLE_NAME. Your code should be `"CREATE TABLE "+TABLE_NAME+" ( "`to use your `private static final String TABLE_NAME = "products";` — antonio, Mar 06 '15 at 19:01
Yep, thanks for pointing out. I changed it. But I still get the same error :) — Nikhil, Mar 06 '15 at 19:06
I assume that the database file still exists and it currently has the version code `1`, which means that your `onUpgrade` method will never get called, because the database is already the latest version. Delete the database file. You can do that by uninstalling your app on your device or clearing your app's storage. You could also increment the DB version code. However, your updated table creation code has a syntax error now. You need to add a space character to the end of the string `"CREATE TABLE"`, otherwise the resulting SQL code looks like: `CREATE TABLEproducts...`. — tiguchi, Mar 06 '15 at 19:16
I agree with @NobuGames. Go on your application settings in your emulator/phone and delete cache and all data. After run again your project. If you have changed your code and run it after, your DB version still remain 1 and it will not create again! — Giorgio Antonioli, Mar 06 '15 at 19:17
Thanks for your input. Tried it. But same result. In first case by the time I call add() method, a table is not created. I'll update my question with my logcat report. — Nikhil, Mar 06 '15 at 19:27
My problem is exactly similar to : http://stackoverflow.com/questions/6791852/android-sqliteopenhelper-oncreate-method-is-not-called-why and http://stackoverflow.com/questions/10147888/sqliteopenhelper-oncreate-is-not-called-the-db-does-not-exist But I couldn't apply the same to my application. Can any one help with that please. Thanks for your time. — Nikhil, Mar 06 '15 at 19:28

Coova · Accepted Answer · 2015-03-06T20:39:54.893

This may help with future development

MySQLiteHelper - Defines Database, tables etc.

public class MySQLiteHelper extends SQLiteOpenHelper {

    private static final String FILE_NAME = "application.db";
    private static final int DB_VERSION = 1;
    private final String TAG = MySQLiteHelper.class.getCanonicalName();
    private static SQLiteDatabase database = null;

    public MySQLiteHelper(Context context) {
        super(context, FILE_NAME, null, DB_VERSION);
        // TODO Auto-generated constructor stub
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        // TODO Auto-generated method stub
        //version 1
        database.execSQL(ProductsTbl.CREATE_SQL);
        //can create new tables if necessary, make sure to increase database version

    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        // TODO Auto-generated method stub
        Log.i(TAG, "Upgrading from version " + oldVersion + " to version "
                                + newVersion);
        for (int i = oldVersion + 1; i <= newVersion; i++) {
            Log.i(TAG, "version is becoming current " + i);
            switch (i) {
                case 2:
                    db.execSQL(ProductsTbl.CREATE_SQL);
                    break;
            //add more cases for each additional table added

            }
        }
    }

    public static class ProductsTbl {
        public static final String TABLE_NAME = "products";
        public static final String ID = "_id";
        public static final String URL = "url";
        public static final String TITLE = "title";
        public static final String PRICE = "price";
        public static final String[] TABLE_COLUMNS = { ID, URL, TITLE, PRICE };
        public static final String CREATE_SQL = "create table " + TABLE_NAME
                                                                                        + "(" + ID + " INTEGER PRIMARY KEY AUTOINCREMENT,"
                                                                                        + URL + " TEXT,"
                                                                                        + TITLE + " TEXT,"
                                                                                        + PRICE + " INTEGER);";
    }

}

MySQLiteDatasource - Defines access methods, Insert, Delete, etc.

public class MySQLiteDatasource {

    //Database fields
    private SQLiteDatabase database;
    private MySQLiteHelper dbHelper;

    public MySQLiteDatasource(Context context) {
        dbHelper = new MySQLiteHelper(context);
    }

    public void open() throws SQLException {
        database = dbHelper.getWritableDatabase();
    }

    public void close() {
        dbHelper.close();
    }

    public void insertProduct(String url, String title, String price) {
        ContentValues values = new ContentValues();
        values.put(MySQLiteHelper.ProductsTbl.URL, url);
        values.put(MySQLiteHelper.ProductsTbl.TITLE, title);
        values.put(MySQLiteHelper.ProductsTbl.PRICE, price);

        try {
            database.insertOrThrow(MySQLiteHelper.ProductsTbl.TABLE_NAME, null, values);
        } catch (SQLiteConstraintException e) {
            //System.out.println(e);
            Log.e("SQLite Database", "Unable to INSERT into Database, possible duplicate topic already exists.");
        }
    }

}

Here is how you could use the 2 classes inside an activity or something similar.

  public static void addProductToDB(String url, String title, String price) {
    MySQLiteDatasource datasource = new MySQLiteDatasource(this);
    datasource.open();
    datasource.insertProduct(url, title, price);
    datasource.close();
  }

You may also want to look into defining a Product object to hold data that is obtained from the database at a later point.

Did you try going into the application manager and force stop/clear data/clear cache and try re-opening the application? — Coova, Mar 06 '15 at 20:00
I updated my add() method in the question. I'm getting an error right at the insert statement that table called products doesn't exist. — Nikhil, Mar 06 '15 at 20:09
What I feel is that even though add() method is called, the code in onCreate() which creates the table is not called. — Nikhil, Mar 06 '15 at 20:09
I will take a look at it. I think you should restructure the way you are utilizing the database. I am going to post some of the updated code. — Coova, Mar 06 '15 at 20:12
Check the updated code out, and let me know if you have any other issues. — Coova, Mar 06 '15 at 20:36

onCreate() method of SQLiteHelper is never called

1 Answers1