Error while recovering data from Sqlite

Question

I’m starting to work with database and I’m already in trouble. I ask for a hand!

Well, I have a database, apparently, there’s no mistake in adding data to it.

I have a Dbhelper class that inherits from SQLiteOpenHelper and I have another DBManager which I use to manage database operations

Error arises when I want to recover data from it.

The error that arises is as follows:

02-04 11:26:00.697: E/SQLiteLog(12125): (5) database is locked
02-04 11:26:00.707: E/SQLiteDatabase(12125): Failed to open database '/data/data/com.example.licencasteste/databases/licences_db'.
02-04 11:26:00.707: E/SQLiteDatabase(12125): android.database.sqlite.SQLiteDatabaseLockedException: database is locked (code 5): , while compiling: PRAGMA journal_mode
02-04 11:26:00.707: E/SQLiteDatabase(12125):    at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method)
02-04 11:26:00.707: E/SQLiteDatabase(12125):    at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:889)
02-04 11:26:00.707: E/SQLiteDatabase(12125):    at android.database.sqlite.SQLiteConnection.executeForString(SQLiteConnection.java:634)
02-04 11:26:00.707: E/SQLiteDatabase(12125):    at android.database.sqlite.SQLiteConnection.setJournalMode(SQLiteConnection.java:320)
02-04 11:26:00.707: E/SQLiteDatabase(12125):    at android.database.sqlite.SQLiteConnection.setWalModeFromConfiguration(SQLiteConnection.java:294)
02-04 11:26:00.707: E/SQLiteDatabase(12125):    at android.database.sqlite.SQLiteConnection.open(SQLiteConnection.java:215)
02-04 11:26:00.707: E/SQLiteDatabase(12125):    at android.database.sqlite.SQLiteConnection.open(SQLiteConnection.java:193)
02-04 11:26:00.707: E/SQLiteDatabase(12125):    at android.database.sqlite.SQLiteConnectionPool.openConnectionLocked(SQLiteConnectionPool.java:463)
02-04 11:26:00.707: E/SQLiteDatabase(12125):    at android.database.sqlite.SQLiteConnectionPool.open(SQLiteConnectionPool.java:185)
02-04 11:26:00.707: E/SQLiteDatabase(12125):    at android.database.sqlite.SQLiteConnectionPool.open(SQLiteConnectionPool.java:177)
02-04 11:26:00.707: E/SQLiteDatabase(12125):    at android.database.sqlite.SQLiteDatabase.openInner(SQLiteDatabase.java:806)
02-04 11:26:00.707: E/SQLiteDatabase(12125):    at android.database.sqlite.SQLiteDatabase.open(SQLiteDatabase.java:791)
02-04 11:26:00.707: E/SQLiteDatabase(12125):    at android.database.sqlite.SQLiteDatabase.openDatabase(SQLiteDatabase.java:694)
02-04 11:26:00.707: E/SQLiteDatabase(12125):    at android.app.ContextImpl.openOrCreateDatabase(ContextImpl.java:1179)
02-04 11:26:00.707: E/SQLiteDatabase(12125):    at android.content.ContextWrapper.openOrCreateDatabase(ContextWrapper.java:268)
02-04 11:26:00.707: E/SQLiteDatabase(12125):    at android.database.sqlite.SQLiteOpenHelper.getDatabaseLocked(SQLiteOpenHelper.java:223)
02-04 11:26:00.707: E/SQLiteDatabase(12125):    at android.database.sqlite.SQLiteOpenHelper.getWritableDatabase(SQLiteOpenHelper.java:163)
02-04 11:26:00.707: E/SQLiteDatabase(12125):    at com.example.licencasteste.DBManager.adicionarLicenca(DBManager.java:25)
02-04 11:26:00.707: E/SQLiteDatabase(12125):    at com.example.licencasteste.DBHelper.onCreate(DBHelper.java:30)
02-04 11:26:00.707: E/SQLiteDatabase(12125):    at android.database.sqlite.SQLiteOpenHelper.getDatabaseLocked(SQLiteOpenHelper.java:251)
02-04 11:26:00.707: E/SQLiteDatabase(12125):    at android.database.sqlite.SQLiteOpenHelper.getReadableDatabase(SQLiteOpenHelper.java:187)
02-04 11:26:00.707: E/SQLiteDatabase(12125):    at com.example.licencasteste.DBManager.verificarLicenca(DBManager.java:34)
02-04 11:26:00.707: E/SQLiteDatabase(12125):    at com.example.licencasteste.MainActivity$1.onClick(MainActivity.java:32)
02-04 11:26:00.707: E/SQLiteDatabase(12125):    at android.view.View.performClick(View.java:4788)
02-04 11:26:00.707: E/SQLiteDatabase(12125):    at android.view.View$PerformClick.run(View.java:19881)
02-04 11:26:00.707: E/SQLiteDatabase(12125):    at android.os.Handler.handleCallback(Handler.java:739)
02-04 11:26:00.707: E/SQLiteDatabase(12125):    at android.os.Handler.dispatchMessage(Handler.java:95)
02-04 11:26:00.707: E/SQLiteDatabase(12125):    at android.os.Looper.loop(Looper.java:135)
02-04 11:26:00.707: E/SQLiteDatabase(12125):    at android.app.ActivityThread.main(ActivityThread.java:5345)
02-04 11:26:00.707: E/SQLiteDatabase(12125):    at java.lang.reflect.Method.invoke(Native Method)
02-04 11:26:00.707: E/SQLiteDatabase(12125):    at java.lang.reflect.Method.invoke(Method.java:372)
02-04 11:26:00.707: E/SQLiteDatabase(12125):    at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:947)
02-04 11:26:00.707: E/SQLiteDatabase(12125):    at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:742)

Class DBHelper

public class DBHelper extends SQLiteOpenHelper {
    private static final String DB_NAME = "licences_db";
    private static final int DB_VERSION = 1;
    private final String CRIAR_TABELA = "CREATE TABLE licences(codigo TEXT);";

    private Context context;

    public DBHelper(Context context) {          
        super(context, DB_NAME, null, DB_VERSION);
        this.context = context;         
    }       

    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL(CRIAR_TABELA);           
        DBManager dbm = new DBManager(context);         
        dbm.adicionarLicenca("2010");
    }       

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        // Apagar a base
        String apagarTabela = "DROP TABLE licences";

        //Criar nova base
        onCreate(db);
    }
}

Class DBManager:

public class DBManager {
    private DBHelper dbh = null;

    public DBManager(Context context) {

        if (dbh == null) {
            dbh = new DBHelper(context);    
        }           
    }       

    public void adicionarLicenca(String codigo) {           
        String sql = "INSERT INTO licences(codigo) VALUES('"+codigo+"')";
        SQLiteDatabase db = dbh.getWritableDatabase();
        db.execSQL(sql);            
    }

    public String verificarLicenca(String codigo) {
        String sql = "SELECT codigo FROM licencas WHERE codigo='"+codigo+"'";
        SQLiteDatabase db = dbh.getReadableDatabase();
        Cursor cr = db.rawQuery(sql, null);     
        cr.moveToFirst();

        String codigoValido = cr.getString(cr.getColumnIndex("codigo"));

        return codigoValido;
    }   
}

Answer 1

The database is locked(database is locked).

This can happen if two Dbhelper objects access the bank at the same time.

The best way to avoid this is for Dbhelper to be a Singleton, requiring there to be only one instance throughout the application.

I see no need to have the Dbmanger class, Dbhelper will be your db manager.

Pass all methods from Dbmanager to Dbhelper and "make it" Singleton:

public class DBHelper extends SQLiteOpenHelper {

    private static volatile DBHelper instance;

    private static final String DB_NAME = "licences_db";
    private static final int DB_VERSION = 1;
    private final String CRIAR_TABELA = "CREATE TABLE licences(codigo TEXT);";

    private Context context;

    private DBHelper(Context context) {

        super(context, DB_NAME, null, DB_VERSION);

        this.context = context;

    }

    public static DbHelper getInstance(Context context)
    {
        if(instance == null){
            synchronized(DBHelper.class){
                if (instance == null){
                    instance = new DbHelper(context);
                }
            }
        }
        return instance;
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL(CRIAR_TABELA);
        adicionarLicenca("2010");
    }


    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        // Apagar a base
        String apagarTabela = "DROP TABLE licences";

        //Criar nova base
        onCreate(db);
    }

    public void adicionarLicenca(String codigo) {

        String sql = "INSERT INTO licences(codigo) VALUES('"+codigo+"')";
        SQLiteDatabase db = getWritableDatabase();
        db.execSQL(sql);

    }


    public String verificarLicenca(String codigo) {

        String codigo;
        String sql = "SELECT codigo FROM licencas WHERE codigo='"+codigo+"'";
        SQLiteDatabase db = getReadableDatabase();
        Cursor cr = db.rawQuery(sql, null);

        if(cr.moveToFirst()){
             codigo = cr.getString(cr.getColumnIndex("codigo"));
        }
        else{
            codigo = "inválido";
        }
        cr.close()
        return codigo;
    }
}

When you want to use Dbhelper do so:

DBHelper dbHelper = DbHelper.getInstance(umContext);