SQLite cannot open database file (code 14) with frequent SELECT query

Question

I have the following "Singleton" class to handle an SQLite connection and to provide 1 connection instance for the entire process / application:

public class DBController { private static DBController instance = new DBController(); private static DBHelper dbHelper; public static DBController getInstance() { return instance; } public SQLiteDatabase dbOpen(Context context) { if(dbHelper == null) dbHelper = new DBHelper(context); return dbHelper.getWritableDatabase(); } } 

And the DBHelper class itself:

 public class DBHelper extends SQLiteOpenHelper { public DBHelper(Context context) { super(context, "database.db", null, 1); } @Override public void onCreate(SQLiteDatabase db) { final String position = "CREATE TABLE test (" + "test TEXT NOT NULL);"; db.execSQL(position); } } 

When I often try to "SELECT" to get some information from the database, I get the following error:

 SQLiteLog: (14) cannot open file at line 31278 of [2ef4f3a5b1] SQLiteLog: (14) os_unix.c:31278: (24) open(/data/user/0/uz.mycompany.myapp/databases/database.db-journal) - SQLiteLog: (14) cannot open file at line 31278 of [2ef4f3a5b1] SQLiteLog: (14) os_unix.c:31278: (24) open(/data/user/0/uz.mycompany.myapp/databases/database.db-journal) - SQLiteLog: (14) statement aborts at 29: [SELECT * FROM test WHERE test='testdata1'] unable to open database file SQLiteQuery: exception: unable to open database file (code 14); query: SELECT * FROM test WHERE test='testdata1' android.database.sqlite.SQLiteCantOpenDatabaseException: unable to open database file (code 14) 

I execute the following code to execute the request:

 public String getData(Context context) { SQLiteDatabase _db = dbOpen(context); Cursor c = _db.rawQuery("SELECT * FROM test WHERE test='testdata1'", null); return getDataFromCursor(c).get(0); //gets data from cursor and returns first one } 

How can I manage / improve the database connection to overcome / prevent this problem?

Answer 1

before executing any request for this material (you must open your database). Close db after completing the task.

 private DBHelper dbHelper = new DBHelper(context); try { _db = dbHelper.getWritableDatabase(); } catch (SQLException s) { new Exception("Error with DB Open"); } 

// Then write your request now ... and then close db.

 _db.close(); 

You can check my git link1 git link2

Answer 2

In addition, I believe that too many cursors and, therefore, open's, can also lead to the same “incapable of opening a database file error”. (in the following code there are 507 lines in shoplistcursor , so there were more than 1,500 cursors used / reused in total)

I received the same message. In accordance with: -

 10-29 19:57:00.901 12845-12845/mjt.shopper E/SQLiteLog: (14) cannot open file at line 30046 of [9491ba7d73] 10-29 19:57:00.901 12845-12845/mjt.shopper E/SQLiteLog: (14) os_unix.c:30046: (24) open(/data/data/mjt.shopper/databases/Shopper-journal) - 10-29 19:57:00.901 12845-12845/mjt.shopper E/SQLiteLog: (14) cannot open file at line 30046 of [9491ba7d73] 10-29 19:57:00.901 12845-12845/mjt.shopper E/SQLiteLog: (14) os_unix.c:30046: (24) open(/data/data/mjt.shopper/databases/Shopper-journal) - 10-29 19:57:00.901 12845-12845/mjt.shopper E/SQLiteLog: (14) statement aborts at 24: [SELECT * FROM productusage WHERE productailseref = 60 AND productproductref = 75 ;] unable to open database file 10-29 19:57:00.902 12845-12845/mjt.shopper E/SQLiteQuery: exception: unable to open database file (code 14); query: SELECT * FROM productusage WHERE productailseref = 60 AND productproductref = 75 ; 10-29 19:57:00.902 12845-12845/mjt.shopper D/AndroidRuntime: Shutting down VM 10-29 19:57:00.903 12845-12845/mjt.shopper E/AndroidRuntime: FATAL EXCEPTION: main 

The code that was in the error was: -

  SQLiteDatabase db = getWritableDatabase(); Cursor shoplistcursor = getAllRowsFromTable(SHOPLIST_TABLE_NAME); Cursor productcsr; Cursor aislecsr; Cursor prdusecsr; while(shoplistcursor.moveToNext()) { productcsr = getProductFromProductId(shoplistcursor.getLong(shoplistcursor.getColumnIndex(SHOPLIST_COLUMN_PRODUCTREF))); aislecsr = getAisleFromAisleId(shoplistcursor.getLong(shoplistcursor.getColumnIndex(SHOPLIST_COLUMN_AISLEREF))); prdusecsr = getProductUsage(shoplistcursor.getLong(shoplistcursor.getColumnIndex(SHOPLIST_COLUMN_AISLEREF)), shoplistcursor.getLong(shoplistcursor.getColumnIndex(SHOPLIST_COLUMN_PRODUCTREF))); if (productcsr.getCount() < 1 | aislecsr.getCount() < 1 | prdusecsr.getCount() < 1) { deleteShopListEntry(shoplistcursor.getLong(shoplistcursor.getColumnIndex(SHOPLIST_COLUMN_ID))); } if(shoplistcursor.isLast()) { prdusecsr.close(); aislecsr.close(); productcsr.close(); } } shoplistcursor.close(); db.close(); } 

The treatment was to close the cursors at each iteration. In accordance with: -

  SQLiteDatabase db = getWritableDatabase(); Cursor shoplistcursor = getAllRowsFromTable(SHOPLIST_TABLE_NAME); Cursor productcsr; Cursor aislecsr; Cursor prdusecsr; while(shoplistcursor.moveToNext()) { productcsr = getProductFromProductId(shoplistcursor.getLong(shoplistcursor.getColumnIndex(SHOPLIST_COLUMN_PRODUCTREF))); aislecsr = getAisleFromAisleId(shoplistcursor.getLong(shoplistcursor.getColumnIndex(SHOPLIST_COLUMN_AISLEREF))); prdusecsr = getProductUsage(shoplistcursor.getLong(shoplistcursor.getColumnIndex(SHOPLIST_COLUMN_AISLEREF)), shoplistcursor.getLong(shoplistcursor.getColumnIndex(SHOPLIST_COLUMN_PRODUCTREF))); if (productcsr.getCount() < 1 | aislecsr.getCount() < 1 | prdusecsr.getCount() < 1) { productcsr.close(); aislecsr.close(); prdusecsr.close(); deleteShopListEntry(shoplistcursor.getLong(shoplistcursor.getColumnIndex(SHOPLIST_COLUMN_ID))); } else { productcsr.close(); aislecsr.close(); prdusecsr.close(); } } shoplistcursor.close(); db.close(); } 

Answer 3

Your code reopens the database every time dbOpen() called.

The SQLite database object is quite lightweight; it really doesn't make sense to keep closing and opening it again.

You already have your singleton; just save one SQLiteDatabase link.

Answer 4

I use the cursor for a large query where the cursor hits the database more than 1350 times with three separate "QUERY" within 800 milliseconds. So I got an error (android.database.sqlite.SQLiteCantOpenDatabaseException: could not open the database file (code 14)). I solved this by closing the cursor before starting a new query. This solves my problem. Hope this works out a more important request than mine.

 String data_query = "SELECT * FROM test WHERE id= " + c_id + " AND memberId = " + memberId; Cursor cu_cu = userHelper.getData(data_query); float cu_cu_count = cu_cu.getCount(); System.out.println("ALL ANSWER COUNT : " + cu_cu_count); cu_cu.close();