Android SQLite leaked

Question

i having problem with sql handler

A SQLiteConnection object for database '/data/data/.../databases/queueManager' was leaked!  Please fix your application to end transactions in progress properly and to close the database when it is no longer needed.

taken from Androidhive tutorial and customized to my use

the table looks like

+ ----------------------------------------------------------- +
: DATABASE_ID : DATABASE_QID : DATABASE_QUEUE : DATABASE_DATE :
+ ----------------------------------------------------------- +

The code

   DBQueue searchDBqid(int id) {
       SQLiteDatabase db = this.getReadableDatabase();

        String selectQuery = "SELECT  * FROM " + TABLE_QUEUE + " WHERE " + DATABASE_QID + " = " + id; 

           Cursor cursornum = db.rawQuery(selectQuery, null);
           int dk = cursornum.getCount();
           cursornum.close();

           if (dk >0) {
               Cursor cursor = db.query(TABLE_QUEUE, new String[] { DATABASE_ID,
                       DATABASE_QID, DATABASE_QUEUE, DATABASE_DATE }, DATABASE_QID + "=?",
                       new String[] { String.valueOf(id) }, null, null, null, null);

               if (cursor != null) cursor.moveToFirst();

               DBQueue dbqueue = new DBQueue(Integer.parseInt(cursor.getString(0)),
                       cursor.getString(1), cursor.getString(2), cursor.getString(3));
               return dbqueue;
           }

       db.close();
       return null;
   }

   DBQueue getDBQueue(int id) {
       SQLiteDatabase db = this.getReadableDatabase();

       Cursor cursor = db.query(TABLE_QUEUE, new String[] { DATABASE_ID,
               DATABASE_QID, DATABASE_QUEUE }, DATABASE_ID + "=?",
               new String[] { String.valueOf(id) }, null, null, null, null);
       if (cursor != null)
           cursor.moveToFirst();

       DBQueue dbqueue = new DBQueue(Integer.parseInt(cursor.getString(0)),
               cursor.getString(1), cursor.getString(2), cursor.getString(3));
       return dbqueue;
   }


   public String getAllqid() {
       Time today = new Time(Time.getCurrentTimezone());
       today.setToNow();

       String selectQuery = "SELECT  * FROM " + TABLE_QUEUE + " WHERE " + DATABASE_DATE + " = '" + today.format("%d %m %Y") + "'";

       SQLiteDatabase db = this.getWritableDatabase();
       Cursor cursor = db.rawQuery(selectQuery, null);

       StringBuilder sb = new StringBuilder();       
       if (cursor.moveToFirst()) {
           do {
               if (sb.length() > 0) sb.append(',');
               sb.append(cursor.getString(1));
           } while (cursor.moveToNext());
       }

       String result = sb.toString();
       return result;
   }
   public void deleteDatedDBQueue() {
        Time today = new Time(Time.getCurrentTimezone());
        today.setToNow();
        String selectQuery = "SELECT  * FROM " + TABLE_QUEUE + " WHERE " + DATABASE_DATE + " != '" + today.format("%d %m %Y") + "'"; ;

        SQLiteDatabase db = this.getWritableDatabase();
        Cursor cursor = db.rawQuery(selectQuery, null);

        if (cursor.moveToFirst()) {
            do {
                db.delete(TABLE_QUEUE, DATABASE_ID + " = ?",
                       new String[] { String.valueOf(Integer.parseInt(cursor.getString(0))) });
            } while (cursor.moveToNext());
        }
        db.close();
    }
   public int getDBQueueCount() {
       String countQuery = "SELECT  * FROM " + TABLE_QUEUE;
       SQLiteDatabase db = this.getReadableDatabase();
       Cursor cursor = db.rawQuery(countQuery, null);
       cursor.close();

       return cursor.getCount();
   }
}

Can someone please tell me how to fix this leak ?

full code: http://ijailbreak.me/databasehandler.txt

score 51 · Accepted Answer · edited May 23 '17 at 11:54

51

Each Cursor should be closed when you're finished with it. The traditional way to do this is:

Cursor cursor = db.query(...);
try {
    // read data from the cursor in here
} finally {
    cursor.close();
}

But now, with try-with-resources, it can be much more concise:

try (Cursor cursor = db.query(...)) {
    // read data from the cursor in here
}

edited May 23 '17 at 11:54

Community

1
1

answered Oct 09 '12 at 14:05

Graham Borland

60,055
21
138
179

I also think its because the database is not closed in several of the methods which might mean when his open helper goes out of scope, it leaks, so in my answer I suggest to make the open helper a singleton to avoid open/close issues which will only get worse when he tries to use it across multiple threads :) – Ian Warwick Oct 09 '12 at 14:07
6

Yes, the database connection should always be a singleton. – Graham Borland Oct 09 '12 at 14:10
ok, i used this method every time i use Cursor. and added few db.close(); and i think it fix the problem. thanks ! – Kirma Oct 09 '12 at 16:26
You may wish to check if cursor is null before making use of it, e.g., `if (cursor != null) cursor.close();` – auspicious99 Jun 11 '15 at 02:15
2

@auspicious99 that is not necessary for a database query. It will never return a null cursor. (ContentProviders are different, though.) – Graham Borland Jun 11 '15 at 08:04
@GrahamBorland Thanks.. on further investigation, it appears you are right, and I had been misled by some sample code that people posted that unnecessarily checked for null cursors for a database query. – auspicious99 Jun 11 '15 at 08:15
try-with-resources requires api level 19+ – Vlad Feb 29 '16 at 12:45
So, when using try construction, the close statement is no longer required? – zygimantus Aug 15 '17 at 16:52
Could you please help to solve this [issue](https://stackoverflow.com/questions/62646206/unable-to-prevent-sqliteconnection-object-leakage) – MrinmoyMk Jun 29 '20 at 22:26

score 8 · Answer 2 · edited Oct 09 '12 at 14:06

You forget to close your cursors on several occasions, make sure you always close the cursor when your done.

For instance, the second query does not close the cursor, I have TODO'd it for clarity

Also you do not close theSQLiteDatabase once your done in getDBQueue, getAllqid and getDBQueueCount, if you change your design to make your SQLiteOpenHelper a singleton then you won't need to close the SQLiteDatabase and avoid the leak

   DBQueue searchDBqid(int id) {
       SQLiteDatabase db = this.getReadableDatabase();

        String selectQuery = "SELECT  * FROM " + TABLE_QUEUE + " WHERE " + DATABASE_QID + " = " + id; 

           Cursor cursornum = db.rawQuery(selectQuery, null);
           int dk = cursornum.getCount();
           cursornum.close();

           if (dk >0) {

               // TODO: Close this cursor!
               Cursor cursor = db.query(TABLE_QUEUE, new String[] { DATABASE_ID,
                       DATABASE_QID, DATABASE_QUEUE, DATABASE_DATE }, DATABASE_QID + "=?",
                       new String[] { String.valueOf(id) }, null, null, null, null);

               if (cursor != null) cursor.moveToFirst();

               DBQueue dbqueue = new DBQueue(Integer.parseInt(cursor.getString(0)),
                       cursor.getString(1), cursor.getString(2), cursor.getString(3));
               return dbqueue;
           }

       db.close();
       return null;
   }

Could you please help to solve this [issue](https://stackoverflow.com/questions/62646206/unable-to-prevent-sqliteconnection-object-leakage) — MrinmoyMk, Jun 29 '20 at 22:26

score 1 · Answer 3 · answered Jul 26 '16 at 04:03

Each time you open a database(readable or writable) and cursor which uses memory resources has to be deallocated by using ".close();" after its usage ends in each database function eg:

 if (cursor != null) cursor.moveToFirst();

               DBQueue dbqueue = new DBQueue(Integer.parseInt(cursor.getString(0)),
                       cursor.getString(1), cursor.getString(2), cursor.getString(3));
               return dbqueue;
           }
cursor.close();

       db.close();
       return null;
   }

DBQueue getDBQueue(int id) {
       SQLiteDatabase db = this.getReadableDatabase();

       Cursor cursor = db.query(TABLE_QUEUE, new String[] { DATABASE_ID,
               DATABASE_QID, DATABASE_QUEUE }, DATABASE_ID + "=?",
               new String[] { String.valueOf(id) }, null, null, null, null);
       if (cursor != null)
           cursor.moveToFirst();

       DBQueue dbqueue = new DBQueue(Integer.parseInt(cursor.getString(0)),
               cursor.getString(1), cursor.getString(2), cursor.getString(3));
cursor.close();
db.close();
       return dbqueue;
   }

and so on....!!

score 0 · Answer 4 · answered Mar 29 '13 at 09:07

0

first time open database and last put this code.

 @Override
        protected void onDestroy() {
            // TODO Auto-generated method stub
         mdb.close();   
         super.onDestroy();
        }

answered Mar 29 '13 at 09:07

AnilPatel

2,356
1
24
40

Could you please help to solve this [issue](https://stackoverflow.com/questions/62646206/unable-to-prevent-sqliteconnection-object-leakage) – MrinmoyMk Jun 29 '20 at 22:27

score 0 · Answer 5 · answered Mar 19 '22 at 23:27

DBQueue dbQueue = new DBQueue();

call your (Database) DBHelper class using dbQueue.getData();

 Cursor cursor = dbQueue.getData(); 
 try{
    while(cursor.moveToNext){
        int id = cursor.getInt(0);
    }
 }finally{
    dbQueue.close();
 }

This is your DBHelper Class

    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL("CREATE TABLE " (....);
    }
 
 public Cursor getSharedData() {
    SQLiteDatabase db = this.getWritableDatabase();
    Cursor cursor;
    cursor = db.rawQuery("Select * from " + "YOUR_DATABASE_TABLE_NAME", null);
    return cursor;
}

Android SQLite leaked

5 Answers5

Linked