Using SQLite Readable and Writable Database Together

Question

I am writing a code for Android which will require both Readable and Writable Database at the same time. And I am getting following error:

05-21 13:27:47.079: E/SQLiteDatabase(8326): close() was never explicitly called on database '/data/data/com.example.devicecontrolpanel/databases/AlarmSystem' 
05-21 13:27:47.079: E/SQLiteDatabase(8326): android.database.sqlite.DatabaseObjectNotClosedException: Application did not close the cursor or database object that was opened here
05-21 13:27:47.079: E/SQLiteDatabase(8326):     at android.database.sqlite.SQLiteDatabase.<init>(SQLiteDatabase.java:2052)
05-21 13:27:47.079: E/SQLiteDatabase(8326):     at android.database.sqlite.SQLiteDatabase.openDatabase(SQLiteDatabase.java:1087)
05-21 13:27:47.079: E/SQLiteDatabase(8326):     at android.database.sqlite.SQLiteDatabase.openDatabase(SQLiteDatabase.java:1050)
05-21 13:27:47.079: E/SQLiteDatabase(8326):     at android.database.sqlite.SQLiteDatabase.openOrCreateDatabase(SQLiteDatabase.java:1136)
05-21 13:27:47.079: E/SQLiteDatabase(8326):     at android.app.ContextImpl.openOrCreateDatabase(ContextImpl.java:1041)
05-21 13:27:47.079: E/SQLiteDatabase(8326):     at android.content.ContextWrapper.openOrCreateDatabase(ContextWrapper.java:221)
05-21 13:27:47.079: E/SQLiteDatabase(8326):     at android.database.sqlite.SQLiteOpenHelper.getWritableDatabase(SQLiteOpenHelper.java:165)
05-21 13:27:47.079: E/SQLiteDatabase(8326):     at com.example.devicecontrolpanel.DataBaseAdapter.addDeviceOnSearch(DataBaseAdapter.java:215)
05-21 13:27:47.079: E/SQLiteDatabase(8326):     at com.connection.DeviceInformation.<init>(DeviceInformation.java:39)
05-21 13:27:47.079: E/SQLiteDatabase(8326):     at com.example.devicecontrolpanel.TestActivity$DeviceSearcher$3.run(TestActivity.java:251)

The piece of code making error, as per what I am guessing is here:

    public int addDeviceOnSearch(DeviceInformation device) //Final and done
    {
        SQLiteDatabase dbRead = this.getReadableDatabase();

        String[] columDevice = {DEVICE_ID, DEVICE_NAME, DEVICE_IP, DEVICE_TYPE};
        String[] columPin = {PIN_ID, PIN_NO, PIN_NAME, PIN_CURRENT_STATUS};

        Cursor cursorDevice = dbRead.query(DEVICE_TABLE, columDevice, DEVICE_MAC+"=?", new String[] {device.getMAC()}, null, null, null);
        dbRead.close();
        if(cursorDevice==null)
        {
            SQLiteDatabase dbWrite = this.getWritableDatabase();
            ContentValues values = new ContentValues();
            //New Device
            values.put(DEVICE_IP, device.getIP());
            values.put(DEVICE_MAC, device.getMAC());
            values.put(DEVICE_NAME, device.getDeviceName());
            values.put(DEVICE_TYPE, device.getType());
            long devId = dbWrite.insert(DEVICE_TABLE, null, values);
            device.setId((int) devId);
            dbWrite.close();
        }
        else
        {

Error in this line Line No. 215

            SQLiteDatabase dbWrite = this.getWritableDatabase();

Error in this line Line No. 215

            ContentValues values = new ContentValues();
            //Already Exist
            device.setId(Integer.parseInt(cursorDevice.getString(0)));
            device.setDeviceName(cursorDevice.getString(1));

            values.put(DEVICE_IP, device.getIP());
            values.put(DEVICE_TYPE, device.getType());
            dbWrite.update(DEVICE_TABLE,values, DEVICE_ID+ " = ?",new String[] {String.valueOf(device.getId())});
            values  = new ContentValues();
            dbWrite.close();
            dbRead = this.getReadableDatabase();
            Cursor cursorPin = dbRead.query(PIN_TABLE, columPin, PIN_DEVICE_ID+"=?", new String[] {String.valueOf(device.getId())}, null, null, null);
            if(cursorPin==null)
            {
                device.setDevicePin(null);
            }
            else
            {
                cursorPin.moveToFirst();
                List<DevicePinDetail> devicePins = new ArrayList<DevicePinDetail>();
                do
                {
                    int pinId=Integer.parseInt(cursorPin.getString(0));
                    int pin_no=Integer.parseInt(cursorPin.getString(1));
                    String PinName =cursorPin.getString(2);
                    int pinStatus=Integer.parseInt(cursorPin.getString(3));
                    int PinDeviceId=device.getId();
                    devicePins.add(new DevicePinDetail(pinId, pin_no, PinName, PinDeviceId, pinStatus));
                }while(cursorPin.moveToNext());
                device.setDevicePin(devicePins);
            }   
        }
        dbRead.close();
        return device.getId();
    }

onCreate()

@Override
public void onCreate(SQLiteDatabase db)
{
    String CREATE_ALARM_TABLE = "Create Table "+TABLE_NAME+"("
            +KEY_ALARM_ID+" integer primary key AUTOINCREMENT, "+KEY_DESC+" TEXT, "+KEY_REPEAT_DAY+ " TEXT,"
            +KEY_REPEAT_TYPE+" integer, "+KEY_CALENDAR+" TEXT, "+KEY_DEVICE_MAC+" TEXT,"+KEY_DEVICE_IP+" TEXT,"
            +KEY_DEVICE_TYPE+" integer, "+KEY_JSON+" TEXT,"+KEY_ACTIVE+" integer, "+KEY_DEVICE_NAME+" text);";

    String CREATE_DEVICE_TABLE = "Create Table "+ DEVICE_TABLE+"("
            +DEVICE_ID+" integer primary key AUTOINCREMENT, "+DEVICE_MAC+" TEXT, "+DEVICE_NAME+" text, "+DEVICE_IP+" TEXT,"+DEVICE_TYPE+" integer);";

    String CREATE_PIN_TABLE = "Create Table "+ PIN_TABLE + "("
            +PIN_ID+" integer primary key AUTOINCREMENT, "+PIN_NO+" integer, "+PIN_NAME+" text, "+PIN_CURRENT_STATUS+" integer, "
            +PIN_DEVICE_ID+" integer);";

    String CREATE_SETTING_TABLE = "Create Table "+SETTINGS_TABLE+" ("
            +SETTINGS_ID+" integer primary key AUTOINCREMENT, "
            +SETTINGS_COMPUTER+" integer, "+SETTINGS_RASPBERRY+ " integer, "+SETTINGS_FLYPORT+ " integer,"
            +COMPUTER_IP + " text,"+COMPUTER_PORT_SEND+" integer, "+RASPBERRY_IP+" text,"+RASPBERRY_PORT_SEND+" integer,"
            + " text,"+COMPUTER_PORT_RECV+" integer,"+RASPBERRY_PORT_RECV+" integer);";

    db.execSQL(CREATE_PIN_TABLE);
    db.execSQL(CREATE_ALARM_TABLE);
    db.execSQL(CREATE_DEVICE_TABLE);
    db.execSQL(CREATE_SETTING_TABLE);

    ContentValues values = new ContentValues();
    values.put(SETTINGS_COMPUTER, 1);
    values.put(SETTINGS_RASPBERRY, 1);
    values.put(SETTINGS_FLYPORT, 1);
    values.put(COMPUTER_IP, "225.4.5.6");
    values.put(RASPBERRY_IP, "225.4.5.6");
    values.put(COMPUTER_PORT_SEND, 5000);
    values.put(COMPUTER_PORT_RECV, 5003);
    values.put(RASPBERRY_PORT_SEND, 6000);
    values.put(RASPBERRY_PORT_RECV, 6003);
    db.insert(SETTINGS_TABLE, null, values);

    System.out.println("Values added");
}

Solution

If you need to read and write the same database, I suggest you open the database once in writable mode. Writable database can also be read, therefore you should have no problem doing everything you need. According to the getWritableDatabase documentation, this method is used to

Create and/or open a database that will be used for reading and writing.

Hence there's no need to open it twice in two separate modes. Open it once as writable, do everything you need to do, then close it.

Comments

[Sharda Singh]

Then what is the use of getReadableDatabase?

[Aleks G]

@ShardaSingh getReadableDatabase if faster. If you don't need to write anything, then readable database should be used. However it's much faster to open it once in writable mode than readable, then writable. Have a look at the documentation SQLiteOpenHelper documentation, there's a bit of info there.

[Mafro34]

they could have named the method better.

[user1530779]

Faster i don't think if you open SQLlite class you will see getReadable and getWritable both return the same instance so there is no difference reading itself is faster you can say