Fails to create database in Android sqlite

Question

Running latest version of Android Studio. Project is built for API level 23 and up (Marshmallow) and using a Pixel XL emulator. I have granted permissions in Manifest and obtaining write access at runtime too (as required for API 23 and above)

Have a button hooked upto this

public void createDatabase(View view) {
    String[] permissions = {Manifest.permission.WRITE_EXTERNAL_STORAGE};
    requestPermissions(permissions, 1); 
}

I also have this in AndroidManifest.xml

<uses-permission android:name="android.permission.WRITE_EXTERNAL_STORAGE" />

This is my code for permission grant

@Override

public void onRequestPermissionsResult(int requestCode, String 
                                      permissions[], int[] grantResults) {
  switch (requestCode) {
    case 1:
        if(grantResults[0] == PackageManager.PERMISSION_GRANTED){
            DbUtil.CreateDb(getApplicationContext().getApplicationInfo().dataDir + "/databases/game360.db");

        }
        else{
            //Permission denied.
        }
        break;
  }
}

And this is DBUtil.CreateDb

public static void CreateDb(String dbPath){
    SQLiteDatabase db = SQLiteDatabase.openOrCreateDatabase(dbPath, null);
    db.execSQL("CREATE TABLE if not exists user (id INTEGER PRIMARY KEY AUTOINCREMENT, " +
            "username VARCHAR NOT NULL unique, firstName VARCHAR NOT NULL, lastName VARCHAR NOT NULL)");
    ContentValues values = new ContentValues();
    values.put("username", "jj678");
    values.put("firstName", "brad");
    values.put("lastName", "pitt");
    db.insert("user",null, values);
    db.close();
}

When I run app on emulator and click button, I get the permissions popup as expected. After I grant permission o, create Database gets called and it fails with below stack trace (error code 14). Any idea why?

02-23 17:44:39.347 19358-19358/com.jtech.game360 E/SQLiteLog: (14) cannot open file at line 31278 of [2ef4f3a5b1]
02-23 17:44:39.347 19358-19358/com.jtech.game360 E/SQLiteLog: (14) os_unix.c:31278: (2) open(/data/user/0/com.jtech.game360/databases/game360.db) - 
02-23 17:44:39.348 19358-19358/com.jtech.game360 E/SQLiteDatabase: Failed to open database '/data/user/0/com.jtech.game360/databases/game360.db'.
    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:207)
        at android.database.sqlite.SQLiteConnection.open(SQLiteConnection.java:191)
        at android.database.sqlite.SQLiteConnectionPool.openConnectionLocked(SQLiteConnectionPool.java:463)
        at android.database.sqlite.SQLiteConnectionPool.open(SQLiteConnectionPool.java:185)
        at android.database.sqlite.SQLiteConnectionPool.open(SQLiteConnectionPool.java:177)
        at android.database.sqlite.SQLiteDatabase.openInner(SQLiteDatabase.java:806)
        at android.database.sqlite.SQLiteDatabase.open(SQLiteDatabase.java:791)
        at android.database.sqlite.SQLiteDatabase.openDatabase(SQLiteDatabase.java:694)
        at android.database.sqlite.SQLiteDatabase.openOrCreateDatabase(SQLiteDatabase.java:709)
        at com.jtech.game360.DbUtil.CreateDb(DbUtil.java:13)
        at com.jtech.game360.Main2Activity.onRequestPermissionsResult(Main2Activity.java:52)
        at android.app.Activity.dispatchRequestPermissionsResult(Activity.java:6553)
        at android.app.Activity.dispatchActivityResult(Activity.java:6432)

Answer 1

As you're using the standard location for databases, there is no need for permissions as they already exist.

Additionally there is no need to hard code the full path but rather you can use the Context's getDatabasePath() which is the recommended way.

The issue you are encountering is that the dataabses folder doesn't exist and therefore the file can't be opened. So you need to check if the databases directory/folder exists and if not to create the folder.

Furthermore if the database exists, which can easily be checked as the File is obtained to check that to see if the databases folder exists, there is no need to try to check to see if the directory exists nor to openOrCreate the database, nor try to add the already existing row on subsequent runs.

So the CreateDB method could be more efficient if it were :-

// Only context required to be passed (assuming just the one database)
public static void CreateDB(Context context) {
    File f = new File(context.getDatabasePath(DBNAME).toString()); //<<<<<<<<<< uses the recommended GetDatabasePath method
    // If DB exists no need to waste time doing anything
    if (f.exists()) {
        return;
    }
    // Creates the databases directory (or others if directories change in the future)
    if (!f.getParentFile().exists()) {
        f.getParentFile().mkdirs();
    }
    SQLiteDatabase db = SQLiteDatabase.openOrCreateDatabase(f,null); //<<<<<<<<< uses the File to create the database
    //Same code as original from now
    db.execSQL("CREATE TABLE if not exists user (id INTEGER PRIMARY KEY AUTOINCREMENT, " +
            "username VARCHAR NOT NULL unique, firstName VARCHAR NOT NULL, lastName VARCHAR NOT NULL)");
    ContentValues values = new ContentValues();
    values.put("username", "jj678");
    values.put("firstName", "brad");
    values.put("lastName", "pitt");
    db.insert("user",null, values);
    db.close();
}

Working Example

As an example of utilising the above (tested in Lollipop (5.0) and Pie (10.1)) the following (in an activity) :-

@Override
protected void onCreate(Bundle savedInstanceState) {
    super.onCreate(savedInstanceState);
    setContentView(R.layout.activity_main);

    //DBUtil.CreateDb(getApplicationContext().getApplicationInfo().dataDir + "/databases/game360.db");
    DBUtil.CreateDB(this);
    SQLiteDatabase db = SQLiteDatabase.openDatabase(this.getDatabasePath(DBUtil.DBNAME).toString(),null,SQLiteDatabase.OPEN_READWRITE);
    Cursor csr = db.query("user",null,null,null,null,null,null);
    DatabaseUtils.dumpCursor(csr);
    csr.close();
    db.close();
}

Runs fine and results in the following output to the log, for the first and subsequent runs :-

02-24 17:58:07.175 9579-9579/? I/System.out: >>>>> Dumping cursor android.database.sqlite.SQLiteCursor@12c4306e
02-24 17:58:07.176 9579-9579/? I/System.out: 0 {
02-24 17:58:07.176 9579-9579/? I/System.out:    id=1
02-24 17:58:07.176 9579-9579/? I/System.out:    username=jj678
02-24 17:58:07.176 9579-9579/? I/System.out:    firstName=brad
02-24 17:58:07.176 9579-9579/? I/System.out:    lastName=pitt
02-24 17:58:07.176 9579-9579/? I/System.out: }
02-24 17:58:07.176 9579-9579/? I/System.out: <<<<<

Answer 2

Seems like you're missing databases folder. Try:

public static void CreateDb(String dbPath){
    File f = new File(dbPath);
    f.getParentFile().mkdirs();
    SQLiteDatabase db = SQLiteDatabase.openOrCreateDatabase(dbPath, null);
    // ...
}