Error retrieving SQLite data

Navigation
4

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

Well, I have a database, apparently there is no error adding data to it.

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

The error occurs when I want to retrieve data from it.

The error that appears 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;
    }   
}
    
asked by anonymous 04.02.2017 / 10:30

1 answer

4

The database is locked ( database is locked ).

This can happen if two DBHelper objects access the database at the same time.

The best way to avoid this situation is for DBHelper to be a Singleton, requiring only one instance of the entire application.

I do not see need to have DBManger class, DBHelper will be your db manager .

Pass all DBManager methods 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);
    
04.02.2017 / 11:28
How to create 1-N relationship in class with more than one property and even subtype? Prevention Session Hijacking