Android SQLite Read Database from External Storage Folder
android, sqlite
Solution
Taken from @k3b answers:
You can use the SQLiteOpenHelper with a custom path if you provide a custom ContextClass and if you have write access to the target directory.
public class DatabaseHelper extends SQLiteOpenHelper {
private static final int DATABASE_VERSION = 3;
.....
DatabaseHelper(final Context context, String databaseName) {
super(new DatabaseContext(context), databaseName, null, DATABASE_VERSION);
}
}
And here is the custom DatabaseContext class that does all the magic:
class DatabaseContext extends ContextWrapper {
private static final String DEBUG_CONTEXT = "DatabaseContext";
public DatabaseContext(Context base) {
super(base);
}
@Override
public File getDatabasePath(String name) {
File sdcard = Environment.getExternalStorageDirectory();
String dbfile = sdcard.getAbsolutePath() + File.separator+ "databases" + File.separator + name;
if (!dbfile.endsWith(".db")) {
dbfile += ".db" ;
}
File result = new File(dbfile);
if (!result.getParentFile().exists()) {
result.getParentFile().mkdirs();
}
if (Log.isLoggable(DEBUG_CONTEXT, Log.WARN)) {
Log.w(DEBUG_CONTEXT, "getDatabasePath(" + name + ") = " + result.getAbsolutePath());
}
return result;
}
/* this version is called for android devices >= api-11. thank to @damccull for fixing this. */
@Override
public SQLiteDatabase openOrCreateDatabase(String name, int mode, SQLiteDatabase.CursorFactory factory, DatabaseErrorHandler errorHandler) {
return openOrCreateDatabase(name,mode, factory);
}
/* this version is called for android devices < api-11 */
@Override
public SQLiteDatabase openOrCreateDatabase(String name, int mode, SQLiteDatabase.CursorFactory factory) {
SQLiteDatabase result = SQLiteDatabase.openOrCreateDatabase(getDatabasePath(name), null);
// SQLiteDatabase result = super.openOrCreateDatabase(name, mode, factory);
if (Log.isLoggable(DEBUG_CONTEXT, Log.WARN)) {
Log.w(DEBUG_CONTEXT, "openOrCreateDatabase(" + name + ",,) = " + result.getPath());
}
return result;
}
}
How does this work:
Normal android apps have their local database files relative to the app folder. By using a customer context with overwritten `getDatabasePath()` the database is now relative to a different directory on the sd card.
Problem
Currently, I am developing an Android application where my requirement is to read SQLite db from a fixed location in External Location. But I am not unable to open the database. The code I am trying is below: DatabaseHelper: ``` import android.content.Context; import android.database.SQLException; import android.database.sqlite.SQLiteDatabase; import android.database.sqlite.SQLiteOpenHelper; import android.os.Environment; import android.util.Log; import java.io.File; import java.io.FileOutputStream; import java.io.IOException; import java.io.InputStream; import java.io.OutputStream; public class DataBaseHelper extends SQLiteOpenHelper { private static final int DB_VERSION = 1; //private static final String DB_NAME = "MedRec.db"; private static final String DB_NAME = "MedRec"; public DataBaseHelper(Context context) { //super(context,DB_NAME,null,DB_VERSION); super(context, Environment.getExternalStorageDirectory() + "/EMIS/MedRec", null, 1); //super(context, context.getExternalFilesDir(null).getAbsolutePath() + "/EMIS/" + DB_NAME, null, 1); Log.d("DB_PATH:", context.getExternalFilesDir(null).getAbsolutePath() + "/EMIS/" + DB_NAME); } @Override public void onCreate(SQLiteDatabase database) { Log.d("CREATING TABLE :", "SUCCESS"); } @Override public void onUpgrade(SQLiteDatabase database,int oldVersion,int newVersion) { Log.d("UPGRADING TABLE :", "SUCCESS"); onCreate(database); } } ``` PatientDataSource: ``` package com.medrecordapp.main.database; import android.content.ContentValues; import android.content.Context; import android.database.Cursor; import android.database.SQLException; import android.database.sqlite.SQLiteDatabase; import android.util.Log; import com.medrecordapp.main.model.Patient; import java.util.ArrayList; public class PatientDataSource { private SQLiteDatabase database; private DataBaseHelper dbHelper; public static final String TABLE_PATIENT = "Patient"; public final static String ID = "_id"; public final static String PATIENT_ID = "PatientID"; public final static String NHS_NUMBER = "NHSNumber"; public final static String FIRST_NAME = "FirstName"; public final static String LAST_NAME = "LastName"; public final static String CATEGORY = "category"; public final static String PHONE_1 = "phone1"; public final static String PHONE_2 = "phone2"; public final static String PHOTO_FILE = "photofile"; public final static String PROFESSION = "profession"; public final static String STREET = "Street"; public final static String VILLAGE = "Village"; public final static String TOWN = "Town"; public final static String COUNTRY = "County"; public final static String POST_CODE = "PostCode"; public final static String COMMENT = "comment"; public final static String E1 = "E1"; public final static String E2 = "E2"; public final static String E3 = "E3"; public final static String E4 = "E4"; public final static String E5 = "E5"; public final static String UPD_DATE = "upddate"; public PatientDataSource(Context context) { dbHelper = new DataBaseHelper(context); } public void open() throws SQLException { ///dbHelper.openDataBase(); //dbHelper.getReadableDatabase(); //database = dbHelper.getReadableDatabase(); database = dbHelper.getWritableDatabase(); } public void close() { dbHelper.close(); } public long addPatient(Patient patient) { open(); ContentValues patientValue = new ContentValues(); patientValue.put(PATIENT_ID, patient.getPatientId()); patientValue.put(NHS_NUMBER, patient.getNhsNumber()); patientValue.put(FIRST_NAME, patient.getFirstName()); patientValue.put(LAST_NAME, patient.getFirstName()); patientValue.put(CATEGORY, patient.getFirstName()); patientValue.put(PHONE_1, patient.getFirstName()); patientValue.put(PHONE_2, patient.getFirstName()); patientValue.put(PHOTO_FILE, patient.getFirstName()); patientValue.put(PROFESSION, patient.getFirstName()); patientValue.put(STREET, patient.getFirstName()); patientValue.put(VILLAGE, patient.getFirstName()); patientValue.put(TOWN, patient.getFirstName()); patientValue.put(COUNTRY, patient.getFirstName()); patientValue.put(POST_CODE, patient.getFirstName()); patientValue.put(COMMENT, patient.getFirstName()); patientValue.put(E1, patient.getFirstName()); patientValue.put(E2, patient.getFirstName()); patientValue.put(E3, patient.getFirstName()); patientValue.put(E4, patient.getFirstName()); patientValue.put(E5, patient.getFirstName()); patientValue.put(UPD_DATE, patient.getFirstName()); long insertId = database.insert(TABLE_PATIENT,null,patientValue); Log.d("RESULT :", insertId + ""); close(); return insertId; } public ArrayList<Patient> loadPatient() { open(); ArrayList<Patient> patientList = new ArrayList<Patient>(); Cursor cursor = database.rawQuery(" SELECT * FROM Patient " , null ); cursor.moveToFirst(); while (!cursor.isAfterLast()) { Patient patient = new Patient( cursor.getInt(cursor.getColumnIndex("_id")) , cursor.getString(cursor.getColumnIndex("PatientID")), cursor.getString(2), cursor.getString(3), cursor.getString(4), cursor.getString(5), cursor.getString(6), cursor.getString(6), cursor.getString(7), cursor.getString(8), cursor.getString(9), cursor.getString(10), cursor.getString(11), cursor.getString(12), cursor.getString(13), cursor.getString(14), cursor.getString(15), cursor.getString(16), cursor.getString(17), cursor.getString(18), cursor.getString(19), cursor.getString(20), cursor.getString(21) ); patientList.add(patient); cursor.moveToNext(); } cursor.close(); close(); return patientList; } public ArrayList<Patient> loadPatient(String patientId) { open(); ArrayList<Patient> patientList = new ArrayList<Patient>(); Cursor cursor = database.rawQuery(" SELECT * FROM Patient WHERE _id = ?" , new String[]{ patientId } ); cursor.moveToFirst(); while (!cursor.isAfterLast()) { Patient patient = new Patient( cursor.getInt(0) , cursor.getString(1), cursor.getString(2), cursor.getString(3), cursor.getString(4), cursor.getString(5), cursor.getString(6), cursor.getString(6), cursor.getString(7), cursor.getString(8), cursor.getString(9), cursor.getString(10), cursor.getString(11), cursor.getString(12), cursor.getString(13), cursor.getString(14), cursor.getString(15), cursor.getString(16), cursor.getString(17), cursor.getString(18), cursor.getString(19), cursor.getString(20), cursor.getString(21) ); patientList.add(patient); cursor.moveToNext(); } cursor.close(); close(); return patientList; } /*public void updateFavorites(Favorites favoritesInfo,String id) { open(); ContentValues favoritesInfoValues = new ContentValues(); String strFilter = "id = " + id; favoritesInfoValues.put(ID, favoritesInfo.getFavoritesId()); favoritesInfoValues.put(JOKES_ID, favoritesInfo.getJokesId()); //Update using the SQLite update function. database.update(TABLE_FAVORITES, favoritesInfoValues, strFilter, null); //Close the opened database connection close(); }*/ public boolean deletePatient(String id) { open(); String strFilter = "_id =" + id; boolean res = database.delete(TABLE_PATIENT, strFilter , null) > 0; close(); return res; } } ``` Manifest Permissions: ``` <uses-permission android:name="android.permission.WRITE_EXTERNAL_STORAGE" /> <uses-permission android:name="android.permission.READ_EXTERNAL_STORAGE" /> ``` But what I am getting is the following exceptions: ``` 10-25 12:49:25.838 30502-30502/com.medrecodapp.main.database E/SQLiteLog﹕ (14) cannot open file at line 31278 of [2ef4f3a5b1] 10-25 12:49:25.838 30502-30502/com.medrecodapp.main.database E/SQLiteLog﹕ (14) os_unix.c:31278: (2) open(/storage/emulated/0/EMIS/MedRec) - 10-25 12:49:25.838 30502-30502/com.medrecodapp.main.database E/SQLiteConnection﹕ DB info: sqlite3_open_v2, path: /storage/emulated/0/EMIS/MedRec, flag: 6, ret: 14 10-25 12:49:25.838 30502-30502/com.medrecodapp.main.database E/SQLiteConnection﹕ DB info: errno = 2, errno message = No such file or directory 10-25 12:49:25.839 30502-30502/com.medrecodapp.main.database E/SQLiteDatabase﹕ Failed to open database '/storage/emulated/0/EMIS/MedRec'. android.database.sqlite.SQLiteCantOpenDatabaseException: unknown error (code 14): Could not open database at android.database.sqlite.SQLiteConnection.nativeOpen(Native Method) at android.database.sqlite.SQLiteConnection.open(SQLiteConnection.java:237) at android.database.sqlite.SQLiteConnection.open(SQLiteConnection.java:221) at android.database.sqlite.SQLiteConnectionPool.openConnectionLocked(SQLiteConnectionPool.java:468) at android.database.sqlite.SQLiteConnectionPool.open(SQLiteConnectionPool.java:190) at android.database.sqlite.SQLiteConnectionPool.open(SQLiteConnectionPool.java:182) at android.database.sqlite.SQLiteDatabase.openInner(SQLiteDatabase.java:876) at android.database.sqlite.SQLiteDatabase.open(SQLiteDatabase.java:856) at android.database.sqlite.SQLiteDatabase.openDatabase(SQLiteDatabase.java:754) at android.app.ContextImpl.openOrCreateDatabase(ContextImpl.java:579) at android.content.ContextWrapper.openOrCreateDatabase(ContextWrapper.java:269) at android.database.sqlite.SQLiteOpenHelper.getDatabaseLocked(SQLiteOpenHelper.java:223) at android.database.sqlite.SQLiteOpenHelper.getWritableDatabase(SQLiteOpenHelper.java:163) at com.medrecordapp.main.database.PatientDataSource.open(PatientDataSource.java:53) at com.medrecordapp.main.database.PatientDataSource.loadPatient(PatientDataSource.java:100) at com.medrecordapp.main.activity.PatientListActivity.onCreate(PatientListActivity.java:90) at android.app.Activity.performCreate(Activity.java:6262) at android.app.Instrumentation.callActivityOnCreate(Instrumentation.java:1125) at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2462) at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:2569) at android.app.ActivityThread.access$900(ActivityThread.java:150) at android.app.ActivityThread$H.handleMessage(ActivityThread.java:1399) at android.os.Handler.dispatchMessage(Handler.java:102) at android.os.Looper.loop(Looper.java:168) at android.app.ActivityThread.main(ActivityThread.java:5885) at java.lang.reflect.Method.invoke(Native Method) at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:819) at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:709) 10-25 12:49:25.840 30502-30502/com.medrecodapp.main.database E/AndroidRuntime﹕ FATAL EXCEPTION: main Process: com.medrecodapp.main.database, PID: 30502 java.lang.RuntimeException: Unable to start activity ComponentInfo{com.medrecodapp.main.database/com.medrecordapp.main.activity.PatientListActivity}: android.database.sqlite.SQLiteCantOpenDatabaseException: unknown error (code 14): Could not open database at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2509) at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:2569) at android.app.ActivityThread.access$900(ActivityThread.java:150) at android.app.ActivityThread$H.handleMessage(ActivityThread.java:1399) at android.os.Handler.dispatchMessage(Handler.java:102) at android.os.Looper.loop(Looper.java:168) at android.app.ActivityThread.main(ActivityThread.java:5885) at java.lang.reflect.Method.invoke(Native Method) at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:819) at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:709) Caused by: android.database.sqlite.SQLiteCantOpenDatabaseException: unknown error (code 14): Could not open database at android.database.sqlite.SQLiteConnection.nativeOpen(Native Method) at android.database.sqlite.SQLiteConnection.open(SQLiteConnection.java:237) at android.database.sqlite.SQLiteConnection.open(SQLiteConnection.java:221) at android.database.sqlite.SQLiteConnectionPool.openConnectionLocked(SQLiteConnectionPool.java:468) at android.database.sqlite.SQLiteConnectionPool.open(SQLiteConnectionPool.java:190) at android.database.sqlite.SQLiteConnectionPool.open(SQLiteConnectionPool.java:182) at android.database.sqlite.SQLiteDatabase.openInner(SQLiteDatabase.java:876) at android.database.sqlite.SQLiteDatabase.open(SQLiteDatabase.java:856) at android.database.sqlite.SQLiteDatabase.openDatabase(SQLiteDatabase.java:754) at android.app.ContextImpl.openOrCreateDatabase(ContextImpl.java:579) at android.content.ContextWrapper.openOrCreateDatabase(ContextWrapper.java:269) at android.database.sqlite.SQLiteOpenHelper.getDatabaseLocked(SQLiteOpenHelper.java:223) at android.database.sqlite.SQLiteOpenHelper.getWritableDatabase(SQLiteOpenHelper.java:163) at com.medrecordapp.main.database.PatientDataSource.open(PatientDataSource.java:53) at com.medrecordapp.main.database.PatientDataSource.loadPatient(PatientDataSource.java:100) at com.medrecordapp.main.activity.PatientListActivity.onCreate(PatientListActivity.java:90) at android.app.Activity.performCreate(Activity.java:6262) at android.app.Instrumentation.callActivityOnCreate(Instrumentation.java:1125) at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2462) at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:2569) at android.app.ActivityThread.access$900(ActivityThread.java:150) at android.app.ActivityThread$H.handleMessage(ActivityThread.java:1399) at android.os.Handler.dispatchMessage(Handler.java:102) at android.os.Looper.loop(Looper.java:168) at android.app.ActivityThread.main(ActivityThread.java:5885) at java.lang.reflect.Method.invoke(Native Method) at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:819) at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:709) ``` The file has bee put inside the following folder : One last thing I would like to mention is that I am testing this on HTC 10 which does not have any External storage, but emulated storage. Any help would be highly appreciated. Thanks