OnCreate for sqlite databse in android is not called automatically even though I am using writabledatabase

Question

I am new to SQLite and trying to create an SQLite database. Still, the problem is that even though I am using writabledatabase before inserting the data in the addData() method I still get the error "no such table". I don't know where the bug is.

This is the code for the database class. Of course, I am creating DBHelper in the MainActivity and I pass this as the context.

 class DBHelper(context: Context, factory: SQLiteDatabase.CursorFactory?) : SQLiteOpenHelper(context, DATABASE_NAME, factory, DATABASE_VERSION)
        {

            private  val SQL_CREATE_ENTRIES = "CREATE TABLE  $TABLE_NAME (" + "${BaseColumns._ID} INTEGER, " + "$COLUMN_NAME_CHAPTER TEXT," + "$COLUMN_NAME_VERSE TEXT, " + "$COLUMN_NAME_NUMBER INTEGER)"
            private  val SQL_DELETE_ENTRIES = "DROP TABLE IF EXISTS $TABLE_NAME"


            override fun onCreate(db: SQLiteDatabase) {
                Log.i("testDatabase", "create")
                db.execSQL(SQL_CREATE_ENTRIES)

            }

            override fun onUpgrade(db: SQLiteDatabase, oldVersion: Int, newVersion: Int) {
                db.execSQL(SQL_DELETE_ENTRIES)
                onCreate(db)

            }

            fun addData(name: String, content: String, number: Int) :Boolean
            {

                val values = ContentValues().apply {
                    put(COLUMN_NAME_CHAPTER, name)
                    put(COLUMN_NAME_VERSE, content)
                    put(COLUMN_NAME_NUMBER, number)
                }
                val db = writableDatabase

                val newId = db?.insert(TABLE_NAME, null, values)

                db.close()

                return newId?.toInt() != -1
            }

companion object {
                const val DATABASE_VERSION = 1
                const val DATABASE_NAME = "FeedReader.db"

                const val TABLE_NAME = "versesBookmarked"
                const val COLUMN_NAME_CHAPTER = "chapter"
                const val COLUMN_NAME_VERSE = "verse"
                const val COLUMN_NAME_NUMBER = "number"
            }
}

This is the error I am getting:

E/SQLiteLog: (1) no such table: versesBookmarked in "INSERT INTO versesBookmarked(number,verse,chapter) VALUES (?,?,?)" E/SQLiteDatabase: Error inserting number=0 verse=بِسْمِ اللَّهِ الرَّحْمَٰنِ الرَّحِيمِ chapter=الفاتحة android.database.sqlite.SQLiteException: no such table: versesBookmarked (code 1 SQLITE_ERROR): , while compiling: INSERT INTO versesBookmarked(number,verse,chapter) VALUES (?,?,?)

MikeT · Answer

There is nothing wrong with your code, although a few things that you might wish to consider.

You code as it is (with the exception of not closing the database) runs fine and the resultant database, using App Inspection shows:-

As such your issue is probably that you have run the code, and it failed when creating the table, and then you have corrected the code and then rerun.

The Very Likely Fix

If the above is true then the Fix is simple, uninstall the App and rerun.

Explanation

The reason is that even though the table creation may have failed, it would have done so in the onCreate method. This method is run after the database itself has been created (i.e. an empty database as far as app specific tables are concerned).

As the database exists albeit without your user defined tables, the onCreate method will never run again as the database exists (and hence why the table not found). So the only way to get the onCreate method to run automatically is to delete the database, the simplest way is to uninstall the App.

Additional

I would suggest the following changes before rerunning though

change that _id column from just INTEGER to INTEGER PRIMARY KEY, thus when inserting the _id column will be assigned a unique value that can be used to identify the row (otherwise null is of little use even though each value is considered unique it cannot be used to uniquely identify a row).
- so private val SQL_CREATE_ENTRIES = "CREATE TABLE $TABLE_NAME (" + "${BaseColumns._ID} INTEGER PRIMARY KEY, " + "$COLUMN_NAME_CHAPTER TEXT," + "$COLUMN_NAME_VERSE TEXT, " + "$COLUMN_NAME_NUMBER INTEGER)"
do not close the database, closing the database means that the next use has to open the database which is relatively heavy in resource usage. e.g. use:-
```
 val newId = db?.insert(TABLE_NAME, null, values)
 //db.close() /*<<<<<<<<<< COMMENTED OUT */
```
Have null as the default CursorFactory (you probably don't want a custom CursorFactory)
- so class DBHelper(context: Context, factory: SQLiteDatabase.CursorFactory?=null) : SQLiteOpenHelper(context, DATABASE_NAME, factory, DATABASE_VERSION)

Consider using a singleton approach by adding the following in the companion object:-

 @Volatile
 var instance: DBHelper?=null
 fun getInstance(context: Context): DBHelper {
     if (instance==null) {
         instance = DBHelper(context)
     }
     return instance as DBHelper
 }

so wherever you use dbHelper = Dbhelper.getInstance(this), you retrieve the same single instance of the database. Whilst using dbHelper = DBHelper(this) will create a new instance.

Using the above with the following in an Activity (when running after afresh install):-

class MainActivity : AppCompatActivity() {
    lateinit var dbHelper: DBHelper
    override fun onCreate(savedInstanceState: Bundle?) {
        super.onCreate(savedInstanceState)
        setContentView(R.layout.activity_main)
            //dbHelper = DBHelper(this) without the singleton
        dbHelper = DBHelper.getInstance(this)
        dbHelper.addData("بِسْمِ اللَّهِ الرَّحْمَٰنِ الرَّحِيمِ","الفاتحة ",0)
    }
}

Will:-

result in the log including D/HostConnection: createUnique: call
result in the database being:-

2.1 2.1 Notice how the _id has the value of 1 as opposed to null, and it will be the only row that has an _id of 1

If the App is run again (without uninstalling it) then :-

i.e. a second row has been added, and the _id of the second row is 2

OnCreate for sqlite databse in android is not called automatically even though I am using writabledatabase

1 Answers