I just implemented SQLite database in my project, the app worked perfectly in the old app but after I transfer database files to another app and try to run it, It gave me this error
android.database.sqlite.SQLiteException: no such table: countries (code 1 SQLITE_ERROR): , while compiling: SELECT * FROM countries
Now I tried to run the old app that was working fine, now that is not working too and giving the same error.
I searched a lot about this and tried to solve the errors in these ways but nothing works for me
- Clearing data, Reinstalling app...
- Changing version and name of the db...
- Calling onCreate in onOpen...
and some others like this, But no trick works
This is my entire database helper class (after modification)
class LocationDB(private val myContext: Context) {
private var dbPath: String? = null
private var db: SQLiteDatabase? = null
private var dbHelper: DatabaseHelper
inner class DatabaseHelper
(context: Context?) : SQLiteOpenHelper(context, DB_NAME, null, 5) {
override fun onCreate(db: SQLiteDatabase) {}
override fun onUpgrade(db: SQLiteDatabase, oldVersion: Int, newVersion: Int) {
if(newVersion>oldVersion)
copyDataBase()
}
override fun onOpen(db: SQLiteDatabase?) {
if (db != null) {
onCreate(db)
}
}
}
@Throws(IOException::class)
fun createDataBase() {
val dbExist = checkDataBase()
if (!dbExist) {
dbHelper.readableDatabase
try {
copyDataBase()
} catch (e: IOException) {
throw Error("Error copying database")
}
}
}
private fun checkDataBase(): Boolean {
var checkDB: SQLiteDatabase? = null
try {
val myPath = dbPath + DB_NAME
checkDB = SQLiteDatabase.openDatabase(
myPath, null,
SQLiteDatabase.OPEN_READONLY
)
} catch (e: SQLiteException) {
}
checkDB?.close()
return checkDB != null
}
@Throws(IOException::class)
private fun copyDataBase() {
val myInput = myContext.assets.open(DB_NAME)
val outFileName = dbPath + DB_NAME
val myOutput: OutputStream = FileOutputStream(outFileName)
val buffer = ByteArray(1024)
var length: Int
while (myInput.read(buffer).also { length = it } > 0) {
myOutput.write(buffer, 0, length)
}
myOutput.flush()
myOutput.close()
myInput.close()
}
@Throws(SQLException::class)
fun openDB(): LocationDB {
db = dbHelper.writableDatabase
return this
}
fun closeDB() {
dbHelper.close()
}
val countries: Cursor?
get() {
val query = "SELECT * FROM countries"
return db?.rawQuery(query, null)
}
fun getStateAsPerCountry(country_id: String): Cursor? {
val query = "SELECT * FROM states where country_id=$country_id"
return db?.rawQuery(query, null)
}
fun getCitiesAsPerState(state_id: String): Cursor? {
val query = "SELECT * FROM cities where state_id=$state_id"
return db?.rawQuery(query, null)
}
companion object {
const val DB_NAME = "Location.db"
}
init {
dbPath = myContext.filesDir.path + myContext.packageName + "/databases/"
dbHelper = DatabaseHelper(myContext)
}
}
Edited (10/06/2021)
I see Device File Explorer and I get that the path of the database is /data/data/com.my.package/databases/Location.db by default. And when I debug the app at the error breakpoint, I see that the path is different or we can say that the database helper is getting the database from different path /data/user/0/com.my.package/databases/Location.db which does not exist, So it's clear that without exact path the table will always be null or not created. But the question is why and how can we initialize the right path.
For that, I did this and get the exact path but it is still not working
val countries: Cursor?
get() {
db = SQLiteDatabase.openDatabase(dbPath, null, SQLiteDatabase.OPEN_READONLY)
val query = "SELECT * FROM countries"
return db?.rawQuery(query, null)
}
This is a picture of the database
