This DBHelper code is working in all version but not working android version 9
public class DBHelper extends SQLiteOpenHelper {
private static int db_version = 1;
private static String db_name = "quote_db";
private String db_path;
private SQLiteDatabase db;
private final Context con;
public DBHelper(Context con) {
super(con, db_name, null, db_version);
// TODO Auto-generated constructor stub
this.con = con;
db_path=con.getDatabasePath(db_name).getPath();
}
@Override
public void onCreate(SQLiteDatabase db) {
}
public void createDB() throws IOException {
this.getReadableDatabase();
copyDB();
Log.d("Database", "copy databse");
}
private boolean checkDB() {
SQLiteDatabase cDB = null;
try {
cDB = SQLiteDatabase.openDatabase(db_path+db_name, null,
SQLiteDatabase.OPEN_READWRITE);
} catch (SQLiteException e) {
}
if (cDB != null) {
cDB.close();
}
return cDB != null ? true : false;
}
private void copyDB() throws IOException {
InputStream inputFile = con.getAssets().open(db_name);
String outFileName = db_path + db_name;
OutputStream outFile = new FileOutputStream(outFileName);
byte[] buffer = new byte[1024];
int length;
while ((length = inputFile.read(buffer)) > 0) {
outFile.write(buffer, 0, length);
}
outFile.flush();
outFile.close();
inputFile.close();
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
// TODO Auto-generated method stub
db.execSQL("DROP TABLE IF EXISTS var_guj");
db.execSQL("DROP TABLE IF EXISTS var_eng");
onCreate(db);
Log.d("DB Upgrade", "Yes Upgrade");
}
//get category list from database
public Cursor get_categorydatabyid(String colum_name,int cateoryId) {
SQLiteDatabase db = this.getReadableDatabase();
Cursor cursor = null;
try {
cursor = db.rawQuery("SELECT id,date,month,"+colum_name+",day FROM quote where category_id="+cateoryId+" ORDER BY id",null);
if (cursor != null) {
cursor.moveToFirst();
db.close();
return cursor;
}
} catch (Exception e) {
db.close();
Log.d("Error-", ""+e);
Toast.makeText(con, "Compai-" + e, Toast.LENGTH_LONG).show();
}
cursor.close();
db.close();
return cursor;
}
public int getmaxid(int todate,int tomonth) {
int maxID = 0;
SQLiteDatabase db = this.getReadableDatabase();
Cursor cursor = null;
try {
cursor = db.rawQuery("SELECT id FROM quote WHERE date="+todate+" and month="+tomonth+"", null);
if (cursor != null) {
cursor.moveToFirst();
maxID = cursor.getInt(0);
db.close();
return maxID;
}
} catch (Exception e) {
db.close();
Log.d("Error-", ""+e);
Toast.makeText(con, "Compai-" + e, Toast.LENGTH_LONG).show();
}
cursor.close();
db.close();
return maxID;
}
}
The error is
Compai-android.database.sqlite.SQLite Exception:no such table : quote(code 1 SQLITE_ERROR):,while compiling:SELECT id FROM quote WHERE date=14 and month=10
This is caused because of the use of this.getReadableDatabase() before the copy.
This creates a database and since Android 9 the database is opened, by default, in WAL mode. This results in two files -wal and -shm (each preceded with the database file name) that are owned by the database which is overwritten, not by database that overwrites the former. This anomaly is detected and results in an empty database being returned and hence the table not found.
There are a few get-arounds but the suggested and most efficient way is to not open the database but to instead check if the file exists.
e.g. :-
private boolean checkDB() {
File cDB = new File(con.getDatabasePath(db_name).getPath());
if (cDB.exists()) return true;
if (!cDB.getParentFile().exists()) {
cDB.getParentFile().mkdirs();
}
return false;
}
Note this additionally gets around the issue why opening the database was likely introduced instead of the the file check; that is checking the file, without creating the parent directory (the databases folder) would subsequently fail when attempting the copy of the database file with an ENOENT cannot open file or directory exception.
databases are stored, unless otherwise specified, at data/data/the_package_name/databases/. However, when an App is installed only data/data/package_name/ exists, there is no databases folder/directory.
Opening the database as an SQLiteDatabase is quite expensive resource wise, a fair bit has to be done, the header checked, the schema generated and written to disk or read from disk into memory, the creation of the android_metadata table, the writing to the -wal and -shm files in addition to actually getting a File.
The only flaw as such with this method is that if the asset weren't a valid database in which case a Corrupt Database exception would occur. If this is an issue then you can easily check the first 16 bytes of the header as per Database File Format.
In addition to the above you should also remove/comment out this.getReadableDatabase(); in the createDB method. As this will also result in the same issue.