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)

I think you're doing something weird in com.jtech.game360.DbUtil.CreateDb(), but it's a bit hard to know, since we can't see the code. Also I think you don't need permissions to write inside app's root directory (/data/user/0/com.jtech.game360) — Peter
– Peter, Commented Feb 23, 2019 at 23:23
@Peter I just realized that I left that out. Updated the post with it. Yeah the permissions thing I just did because I didn't want to leave 'any option untried' or have people suggest me to do it — Stubborn
– Stubborn, Commented Feb 23, 2019 at 23:59

Peter · Accepted Answer · 2019-02-24 02:28:35Z

1

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);
    // ...
}

edited Feb 24, 2019 at 2:28

answered Feb 24, 2019 at 1:04

Peter

6087 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

MikeT · Accepted Answer · 2019-02-24 07:14:39Z

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: <<<<<

Thank you. So I guess the createOrOpenDatabase call can be replaced with Create or Open calls based on the exists() result too
@Stubborn the SQLiteDatabase create method creates a memory backed database which would be deleted when it is closed, so that would probably not be wanted.

Collectives™ on Stack Overflow

Fails to create database in Android sqlite

2 Answers 2

Comments

Working Example

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Working Example

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related