Graham
Reputation: 153
SQLite DB not copying to internal
I have recently moved to Studio 3.3 on an app written some years ago but it seems that my code for copying a populated SQLite db from the Assets folder no longer works. Below is a simple test app.
The database name is SteelSectionProperties and it has 1 user table, SectionProps and the android_metadata table
The activity calls a typical helper but the SectionProps table is not found.
I am a beginner in Android. Am I missing something simple/obvious?
The debug output is; E/helper checkdb path: /data/data/com.silverfernsolutions.steelsections/databases/SteelSectionProperties
E/helper checkdb DB=: SQLiteDatabase: /data/data/com.silverfernsolutions.steelsections/databases/SteelSectionProperties
E/createDB.: dbExists=true
E/helper openDB path: /data/data/com.silverfernsolutions.steelsections/databases/SteelSectionProperties
E/helper openDataBase: after SQLiteDatabase.openDatabase
I/System.out: TABLE - android_metadata
I/System.out: COLUMN - locale
E/SQLiteLog: (1) no such table: SectionProps
public class CopyDbActivity extends AppCompatActivity {
Cursor c = null;
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_copy_db);
DataBaseHelperReign myDbHelper = new DataBaseHelperReign(this);
myDbHelper = new DataBaseHelperReign(this);
try {
myDbHelper.createDataBase();
}catch (IOException ioe) {
throw new Error("Unable to create database");
}
try {
myDbHelper.openDataBase();
}catch(SQLException sqle){
throw sqle;
}
//This does not find the table SectionProps???
//Only the android_metadata table
myDbHelper.getDatabaseStructure();
//This causes a crash
myDbHelper.getTablecontents("SectionProps");
}
The helper is
public class DataBaseHelperReign extends SQLiteOpenHelper {
//The Android's default system path of your application database.
//private static String DB_PATH = "/data/data/YOUR_PACKAGE/databases/";
//private static String DB_NAME = "myDBName";
private static String DB_PATH = "/data/data/com.silverfernsolutions.steelsections/databases/";
private static String DB_NAME = "SteelSectionProperties";
private SQLiteDatabase myDataBase;
private final Context myContext;
/**
* Constructor
* Takes and keeps a reference of the passed context in order to access to the application assets and resources.
*
* @param context
*/
public DataBaseHelperReign(Context context) {
super(context, DB_NAME, null, 1);
this.myContext = context;
}
/**
* Creates a empty database on the system and rewrites it with your own database.
*/
public void createDataBase() throws IOException {
boolean dbExist = checkDataBase();
if (dbExist) {
//do nothing - database already exist
Log.e("createDB.", " dbExists=true");
} else {
//By calling this method and empty database will be created into the default system path
//of your application so we are gonna be able to overwrite that database with our database.
this.getReadableDatabase();
try {
copyDataBase();
Log.e("copying db helper ", "db copied");
} catch (IOException e) {
Log.e("copying db helper ", "Error copying DB");
throw new Error("Error copying database");
}
}
}
/**
* Check if the database already exist to avoid re-copying the file each time you open the application.
*
* @return true if it exists, false if it doesn't
*/
/*Larrybud says:
June 17, 2011 at 2:20 am
Nice code, but a better way to get the full path of the file would be to do:
File fdb=getDatabasePath(DATABASE_NAME);
return fdb.getAbsolutePath();
*/
private boolean checkDataBase() {
SQLiteDatabase checkDB = null;
try {
String myPath = DB_PATH + DB_NAME;
Log.e("helper checkdb path", myPath);
checkDB = SQLiteDatabase.openDatabase(myPath, null, SQLiteDatabase.OPEN_READONLY);
Log.e("helper checkdb DB= ", checkDB.toString());
} catch (SQLiteException e) {
Log.e("helper checkdb", "Error " + e.toString());
}
if (checkDB != null) {
checkDB.close();
}
return checkDB != null ? true : false;
}
/**
* Copies your database from your local assets-folder to the just created empty database in the
* system folder, from where it can be accessed and handled.
* This is done by transfering bytestream.
*/
private void copyDataBase() throws IOException {
Log.e("helper copyDB ", " opening input stream");
//Open your local db as the input stream
InputStream myInput = myContext.getAssets().open(DB_NAME);
// Path to the just created empty db
String outFileName = DB_PATH + DB_NAME;
Log.e("helper outfileName", outFileName);
//Open the empty db as the output stream
OutputStream myOutput = new FileOutputStream(outFileName);
//transfer bytes from the inputfile to the outputfile
byte[] buffer = new byte[1024];
int length;
while ((length = myInput.read(buffer)) > 0) {
myOutput.write(buffer, 0, length);
}
//Close the streams
myOutput.flush();
myOutput.close();
myInput.close();
}
public void openDataBase() throws SQLException {
//Open the database
String myPath = DB_PATH + DB_NAME;
Log.e("helper openDB path ", myPath);
myDataBase = SQLiteDatabase.openDatabase(myPath, null, SQLiteDatabase.OPEN_READONLY);
Log.e("helper openDataBase ", "after SQLiteDatabase.openDatabase");
}
@Override
public synchronized void close() {
if (myDataBase != null)
myDataBase.close();
super.close();
}
@Override
public void onCreate(SQLiteDatabase db) {
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
}
public Cursor getTablecontents(String table){
String q = "SELECT * FROM " + table ;
Cursor mCursor = myDataBase.rawQuery(q, null);
return mCursor;
}
public ArrayList<String[]> getDatabaseStructure() {
Cursor c = myDataBase.rawQuery("SELECT name FROM sqlite_master WHERE type='table'", null);
ArrayList<String[]> result = new ArrayList<String[]>();
int i = 0;
result.add(c.getColumnNames());
for (c.moveToFirst(); !c.isAfterLast(); c.moveToNext()) {
String[] temp = new String[c.getColumnCount()];
for (i = 0; i < temp.length; i++) {
temp[i] = c.getString(i);
System.out.println("TABLE - " + temp[i]);
Cursor c1 = myDataBase.rawQuery(
"SELECT * FROM " + temp[i], null);
c1.moveToFirst();
String[] COLUMNS = c1.getColumnNames();
for (int j = 0; j < COLUMNS.length; j++) {
c1.move(j);
System.out.println(" COLUMN - " + COLUMNS[j]);
}
}
result.add(temp);
}
return result;
}
}
Upvotes: 0
Views: 285
Answers (1)
MikeT
Reputation: 56953
I believe that your issue may be due to Android Pie changing from a default of journal mode to write-ahead-logging. When the empty database is created (this is done to create the databases directory if it doesn't exist) prior to the copy the -shm and -wal files that are used by WAL are created. When the database is then opened it is seen as being invalid due to a mis-match between them and the copied database, so an empty database is then opened.
The fix is to not use :-
//By calling this method and empty database will be created into the default system path
//of your application so we are gonna be able to overwrite that database with our database.
this.getReadableDatabase();
but to instead get the parent of the database path as a File (i.e. the path to the databases folder) and to then do a mkdirs.
I would also suggest not using a hard coded path but to utilise the Context's getDatabasePath method.
Also I'd suggest not trying to open the database but rather to check to see if the database file exists.
The following is a method that checks the database file's existence and also creates the databases directory ready for the copy and thus opening the database before the copy is not required.
/**
* Check if the database already exists. NOTE will create the databases folder is it doesn't exist
* @return true if it exists, false if it doesn't
*/
public static boolean checkDataBase(Context context, String dbname) {
File db = new File(context.getDatabasePath(dbname).getPath()); //Get the file name of the database
Log.d("DBPATH","DB Path is " + db.getPath()); //TODO remove if publish App
if (db.exists()) return true; // If it exists then return doing nothing
// Get the parent (directory in which the database file would be)
File dbdir = db.getParentFile();
// If the directory does not exits then make the directory (and higher level directories)
if (!dbdir.exists()) {
db.getParentFile().mkdirs();
dbdir.mkdirs();
}
return false;
}
Tested Code
As per the comment
I removed the line; //this.getReadableDatabase(); and replaced the checkDataBase with your code.boolean dbExist = checkDataBase(myContext,DB_NAME);. It failed with debug D/DBPATH: DB Path is /data/user/0/com.silverfernsolutions.steelsections/databases. I cannot paste all as it exceeds the comment limit and I don't know how to get around that
The above was tested and worked (results below) using :-
DataBaseHelperReign.java
public class DataBaseHelperReign extends SQLiteOpenHelper {
//private static String DB_PATH = "/data/data/com.silverfernsolutions.steelsections/databases/"; //<<<<<<<<<< REMOVED
private static String DB_NAME = "SteelSectionProperties";
private SQLiteDatabase myDataBase;
private final Context myContext;
public DataBaseHelperReign(Context context) {
super(context, DB_NAME, null, 1);
this.myContext = context;
}
/**
* Creates a empty database on the system and rewrites it with your own database.
*/
public void createDataBase() throws IOException {
boolean dbExist = checkDataBase();
if (dbExist) {
//do nothing - database already exist
Log.e("createDB.", " dbExists=true");
} else {
//By calling this method and empty database will be created into the default system path
//of your application so we are gonna be able to overwrite that database with our database.
//this.getReadableDatabase();
try {
copyDataBase();
Log.e("copying db helper ", "db copied");
} catch (IOException e) {
Log.e("copying db helper ", "Error copying DB");
throw new Error("Error copying database");
}
}
}
/*****************************************************************************************************
* NEW
****************************************************************************************************/
public boolean checkDataBase() {
File db = new File(myContext.getDatabasePath(DB_NAME).getPath()); //Get the file name of the database
Log.d("DBPATH","DB Path is " + db.getPath()); //TODO remove if publish App
if (db.exists()) return true; // If it exists then return doing nothing
// Get the parent (directory in which the database file would be)
File dbdir = db.getParentFile();
// If the directory does not exits then make the directory (and higher level directories)
if (!dbdir.exists()) {
db.getParentFile().mkdirs();
dbdir.mkdirs();
}
return false;
}
private void copyDataBase() throws IOException {
Log.e("helper copyDB ", " opening input stream");
//Open your local db as the input stream
InputStream myInput = myContext.getAssets().open(DB_NAME);
// Path to the just created empty db
//String outFileName = DB_PATH + DB_NAME; //<<<<<<<<<< COMMENTED OUT BUT TESTED
String outFileName = myContext.getDatabasePath(DB_NAME).toString(); //<<<<<<<<<< PREFERRED
Log.e("helper outfileName", outFileName);
//Open the empty db as the output stream
OutputStream myOutput = new FileOutputStream(outFileName);
//transfer bytes from the inputfile to the outputfile
byte[] buffer = new byte[1024];
int length;
while ((length = myInput.read(buffer)) > 0) {
myOutput.write(buffer, 0, length);
}
//Close the streams
myOutput.flush();
myOutput.close();
myInput.close();
}
public void openDataBase() throws SQLException {
//Open the database
String myPath = myContext.getDatabasePath(DB_NAME).toString(); //<<<<<<<<<<
Log.e("helper openDB path ", myPath);
myDataBase = SQLiteDatabase.openDatabase(myPath, null, SQLiteDatabase.OPEN_READONLY);
Log.e("helper openDataBase ", "after SQLiteDatabase.openDatabase");
}
@Override
public synchronized void close() {
if (myDataBase != null)
myDataBase.close();
super.close();
}
@Override
public void onCreate(SQLiteDatabase db) {}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { }
public Cursor getTablecontents(String table) {
String q = "SELECT * FROM " + table;
Cursor mCursor = myDataBase.rawQuery(q, null);
return mCursor;
}
public ArrayList<String[]> getDatabaseStructure() {
Cursor c = myDataBase.rawQuery("SELECT name FROM sqlite_master WHERE type='table'", null);
ArrayList<String[]> result = new ArrayList<String[]>();
int i = 0;
result.add(c.getColumnNames());
for (c.moveToFirst(); !c.isAfterLast(); c.moveToNext()) {
String[] temp = new String[c.getColumnCount()];
for (i = 0; i < temp.length; i++) {
temp[i] = c.getString(i);
System.out.println("TABLE - " + temp[i]);
Cursor c1 = myDataBase.rawQuery(
"SELECT * FROM " + temp[i], null);
c1.moveToFirst();
String[] COLUMNS = c1.getColumnNames();
for (int j = 0; j < COLUMNS.length; j++) {
c1.move(j);
System.out.println(" COLUMN - " + COLUMNS[j]);
}
}
result.add(temp);
}
return result;
}
}
- Comments removed for brevity
MainActivity.java
public class MainActivity extends AppCompatActivity {
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
DataBaseHelperReign myDbHelper = new DataBaseHelperReign(this);
myDbHelper = new DataBaseHelperReign(this);
try {
myDbHelper.createDataBase();
}catch (IOException ioe) {
throw new Error("Unable to create database");
}
try {
myDbHelper.openDataBase();
}catch(SQLException sqle){
throw sqle;
}
//This does not find the table SectionProps???
//Only the android_metadata table
myDbHelper.getDatabaseStructure();
//<<<<<<<<<<< commented out as test db does not have a SectionProps table
//This causes a crash
//myDbHelper.getTablecontents("SectionProps");
}
}
- The above is as per your activity except for the attempt to get the table contents
Result
The above was run on two emulators with a test database renamed to SteelSectionProperties and placed into the assets folder. Obviously the underlying tables were different but as can be seen the database is not empty and thus has been copied from the assets folder. One of the emulators was Android Lollipop, the other Android Pie.
The log contains :-
2019-03-08 06:52:44.596 10351-10351/com.silverfernsolutions.steelsections D/DBPATH: DB Path is /data/user/0/com.silverfernsolutions.steelsections/databases/SteelSectionProperties
2019-03-08 06:52:44.596 10351-10351/com.silverfernsolutions.steelsections E/helper copyDB: opening input stream
2019-03-08 06:52:44.596 10351-10351/com.silverfernsolutions.steelsections E/helper outfileName: /data/user/0/com.silverfernsolutions.steelsections/databases/SteelSectionProperties
2019-03-08 06:52:44.597 10351-10351/com.silverfernsolutions.steelsections E/copying db helper: db copied
2019-03-08 06:52:44.598 10351-10351/com.silverfernsolutions.steelsections E/helper openDB path: /data/user/0/com.silverfernsolutions.steelsections/databases/SteelSectionProperties
2019-03-08 06:52:44.601 10351-10351/com.silverfernsolutions.steelsections E/helper openDataBase: after SQLiteDatabase.openDatabase
2019-03-08 06:52:44.602 10351-10351/com.silverfernsolutions.steelsections I/System.out: TABLE - android_metadata
2019-03-08 06:52:44.603 10351-10351/com.silverfernsolutions.steelsections I/System.out: COLUMN - locale
2019-03-08 06:52:44.603 10351-10351/com.silverfernsolutions.steelsections I/System.out: TABLE - retailer
2019-03-08 06:52:44.604 10351-10351/com.silverfernsolutions.steelsections I/System.out: COLUMN - _id
2019-03-08 06:52:44.604 10351-10351/com.silverfernsolutions.steelsections I/System.out: COLUMN - retailerName
2019-03-08 06:52:44.604 10351-10351/com.silverfernsolutions.steelsections I/System.out: TABLE - tariff
2019-03-08 06:52:44.605 10351-10351/com.silverfernsolutions.steelsections I/System.out: COLUMN - _id
2019-03-08 06:52:44.605 10351-10351/com.silverfernsolutions.steelsections I/System.out: COLUMN - planName
2019-03-08 06:52:44.605 10351-10351/com.silverfernsolutions.steelsections I/System.out: COLUMN - retailerReference
2019-03-08 06:52:44.605 10351-10351/com.silverfernsolutions.steelsections I/System.out: COLUMN - usage_rate_meter1
2019-03-08 06:52:44.605 10351-10351/com.silverfernsolutions.steelsections I/System.out: COLUMN - usage_rate_meter2
............
Note
The above, as per for your code, requires that the assets folder has a file named SteelSectionProperties (and is obviously a valid SQLite database). The file cannot be in a sub-directory (as per the code). If the file is not in the assets folder than you would get an error such as.
03-08 07:10:34.929 11420-11420/? D/DBPATH: DB Path is /data/data/com.silverfernsolutions.steelsections/databases/SteelSectionProperties
03-08 07:10:34.929 11420-11420/? E/helper copyDB: opening input stream
03-08 07:10:34.929 11420-11420/? E/copying db helper: Error copying DB
03-08 07:10:34.930 11420-11420/? D/AndroidRuntime: Shutting down VM
03-08 07:10:34.930 11420-11420/? E/AndroidRuntime: FATAL EXCEPTION: main
Process: com.silverfernsolutions.steelsections, PID: 11420
java.lang.Error: Error copying database
at com.silverfernsolutions.steelsections.DataBaseHelperReign.createDataBase(DataBaseHelperReign.java:49)
at com.silverfernsolutions.steelsections.MainActivity.onCreate(MainActivity.java:20)
at android.app.Activity.performCreate(Activity.java:5990)
at android.app.Instrumentation.callActivityOnCreate(Instrumentation.java:1106)
at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2278)
at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:2387)
at android.app.ActivityThread.access$800(ActivityThread.java:151)
at android.app.ActivityThread$H.handleMessage(ActivityThread.java:1303)
at android.os.Handler.dispatchMessage(Handler.java:102)
at android.os.Looper.loop(Looper.java:135)
at android.app.ActivityThread.main(ActivityThread.java:5254)
at java.lang.reflect.Method.invoke(Native Method)
at java.lang.reflect.Method.invoke(Method.java:372)
at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:903)
at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:698)
Upvotes: 1
Related Questions
- SQLite database not copied correctly in some Android devices
- Can't copy table data
- Database not copying from assets
- Android error copying database to devices
- SQLite copy database to Android "no such table"
- Android: SQLite DataBase not creating
- Android app sqlite database cannot dump in to android mobile
- copying own created sqlite database into android failing
- Copying database works on emulator but not on device
- Problem copying an SQLite database in Android 2.2