SQLiteOpenHelper - creating database on SD card

Question

in my test android app I intend to create and access database file, which will be located on SD card. I am using main activity with help of a class, which extends SQLiteOpenHelper. I want to use it the same way as before, but I have to somehow change the database PATH. Do you know, how to achieve it?

thx

My current code of a class which extends SQLiteOpenHelper:

public class DatabaseDefinition extends SQLiteOpenHelper{
private static final String DATABASE_NAME="test.db";
private static final int DATABASE_VERSION=1;

public DatabaseDefinition(Context context) {
    super(context,DATABASE_NAME,null, DATABASE_VERSION);

}

@Override
public void onCreate(SQLiteDatabase db) {
    db.execSQL("CREATE TABLE "+TABLE_NAME+" ("+ _ID +" INTEGER PRIMARY KEY AUTOINCREMENT, "+ NAME+" TEXT NOT NULL, " +SURNAME+" TEXT NOT NULL, "+PHONE+" INT NOT NULL);");
}

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    db.execSQL("DROP TABLE IF EXISTS" + TABLE_NAME);
    onCreate(db);

}

And code of my main:

public class DatabaseExampleActivity extends Activity {
   private DatabaseDefinition database;
   private static String[] FROM={_ID, NAME, SURNAME,PHONE};
   private static String ORDER_BY=" DESC";


   @Override
   public void onCreate(Bundle savedInstanceState) {
       super.onCreate(savedInstanceState);
       setContentView(R.layout.main);
       database= new DatabaseDefinition(this); 
       try{
         String name = null;
         String surname=null;
         int phone=0;
         addEvent("John", "Black", 111012345);

    }finally{
        database.close();
    }
  }



}

Ghostwrite

duplicate of SQLiteOpenHelper problem with fully qualified DB path name – Shela

See my proposed solution in this link: github.com/sqlcipher/android-database-sqlcipher/issues/67 This way you get very easily an encrypted database in sd card which is a lot safer – Janeljanela

Answer 1

First you have to specify the path of the sdcard. You can do that by creating a string like this:

public static final String  DATABASE_FILE_PATH = "/sdcard";

But for you should call

Environment.getExternalStorageDirectory()   

to get the root path to the SD card and use that to create the database. After that you create the database as you want. Here is an example

public class DatabaseHelper
{ 
   private static final String TAG                  = "DatabaseHelper";

  public static final String  DATABASE_FILE_PATH      = Environment.getExternalStorageDirectory();
  public static final String  DATABASE_NAME      = "mydb"; 
  public static final String  TRACKS_TABLE        = "tracks";
  public static final String  TRACK_INFO_TABLE        = "track_info";

  private static final String TRACKS_TABLE_CREATE     = "create table "
           + TRACKS_TABLE
           + " (_id integer primary key autoincrement, title text not null, description text null, created_at date not null);";

  private static final String TRACK_INFO_TABLE_CREATE = "create table " 
           + TRACK_INFO_TABLE 
           + " (_id integer primary key autoincrement, track_id integer not null, latitude real not null, longitude real not null, altitude real not null, created_at date not null);";

private SQLiteDatabase      database;

public DatabaseHelper() 
{  
    try
    {
        database = SQLiteDatabase.openDatabase(DATABASE_FILE_PATH
            + File.separator + DATABASE_NAME, null,SQLiteDatabase.OPEN_READWRITE);
    }
    catch (SQLiteException ex)
    {
        Log.e(TAG, "error -- " + ex.getMessage(), ex);
        // error means tables does not exits
        createTables();
    }
    finally
    {
        DBUtil.safeCloseDataBase(database);
    }
}

private void createTables()
{
    database.execSQL(TRACKS_TABLE_CREATE);
    database.execSQL(TRACK_INFO_TABLE_CREATE);
}

public void close()
{
    DBUtil.safeCloseDataBase(database);
}

public SQLiteDatabase getReadableDatabase()
{
    database = SQLiteDatabase.openDatabase(DATABASE_FILE_PATH
            + File.separator + DATABASE_NAME, null,
            SQLiteDatabase.OPEN_READONLY);
    return database;
}

public SQLiteDatabase getWritableDatabase()
{
    database = SQLiteDatabase.openDatabase(DATABASE_FILE_PATH
            + File.separator + DATABASE_NAME, null,
            SQLiteDatabase.OPEN_READWRITE);
    return database;
}

And in the end you have to set permission in manifest like this: android.permission.WRITE_EXTERNAL_STORAGE

Good luck :) Arkde

Guan

Thanks for reply, but how to link it with my Main? – Ghostwrite

DatabaseHelper is a class and have a constructor that handle database opening and create operations. Create a new databasehelper object in your main activity and use it's metods. – Guan

I don't know, where you have copied it, but it doesn't solve it.I had the same code before and it wasn't working, thus I have asked for a solution with my own code. – Ghostwrite

The code is just an example of how you can do it. Have you set up write external storage permission in manifest? – Guan

Yep, everything like is stated here – Ghostwrite

Keep in mind, using getExternalStorageDirectory() actually reports incorrectly on most modern devices. HC and ICS for many devices will still report storage that's physically built into the device when you ask for that and will return a path of /sdcard/ even though the real sdcard is at /sdcard_ext/ or some such. It may be better to default to getExternalStorageDirectory() but allow the user to override it with a preference setting. – Pediment

Works perfectly for me. Used getExternalStoragePublicDirectory for Android version > 2.2 – Apograph

can you tell if someone wants upgrade database here ..how can he do according to your code. – Dulaney

-1 Do not hard code the path of the external storage, as it is device/version dependent. – Pesce

Answer 2

If some of you want to create sqlite database on sd card but you still want to keep using class which extends sqliteOpenHelper, you can visit this answer.

You just have to add another class, implement it on your class (which extends sqliteOpenHelper) in super(), then change the path on your new class.

Note: If you are using HC or ICS you need to describe the "external_sd" on your path. Because HC and ICS will still report storage that's physically built into the device

Sandal