I am a novice to Android programming and am creating an barter-system app.
I've used SQLite as the Database and have created a DatabaseHelper class.
The app starts with a Login page and has a link to the Register user page. It is when inserting values into the users database that I keep getting the SQLite Database is locked code 5 exception. I've searched everywhere for an answer and have tried using transaction controls and, I think I've closed the database whenever required.
I am truly lost and have no idea how to proceed with my app. I've added my log and DatabaseHandler class. Please do look through and help me debug this program.
04-15 14:35:39.046 2631-2631/? E/SQLiteLog: (5) database is locked
04-15 14:35:39.046 2631-2631/? E/SQLiteDatabase: Failed to open database '/data/data/com.example.asas.handmedown/databases/userDB.db'.
android.database.sqlite.SQLiteDatabaseLockedException: database is locked (code 5): , while compiling: PRAGMA journal_mode
at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method)
at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:889)
at android.database.sqlite.SQLiteConnection.executeForString(SQLiteConnection.java:634)
at android.database.sqlite.SQLiteConnection.setJournalMode(SQLiteConnection.java:320)
at android.database.sqlite.SQLiteConnection.setWalModeFromConfiguration(SQLiteConnection.java:294)
at android.database.sqlite.SQLiteConnection.open(SQLiteConnection.java:215)
at android.database.sqlite.SQLiteConnection.open(SQLiteConnection.java:193)
at android.database.sqlite.SQLiteConnectionPool.openConnectionLocked(SQLiteConnectionPool.java:463)
at android.database.sqlite.SQLiteConnectionPool.open(SQLiteConnectionPool.java:185)
at android.database.sqlite.SQLiteConnectionPool.open(SQLiteConnectionPool.java:177)
at android.database.sqlite.SQLiteDatabase.openInner(SQLiteDatabase.java:806)
at android.database.sqlite.SQLiteDatabase.open(SQLiteDatabase.java:791)
at android.database.sqlite.SQLiteDatabase.openDatabase(SQLiteDatabase.java:694)
at android.app.ContextImpl.openOrCreateDatabase(ContextImpl.java:1142)
at android.content.ContextWrapper.openOrCreateDatabase(ContextWrapper.java:267)
at android.database.sqlite.SQLiteOpenHelper.getDatabaseLocked(SQLiteOpenHelper.java:223)
at android.database.sqlite.SQLiteOpenHelper.getWritableDatabase(SQLiteOpenHelper.java:163)
at com.example.asas.handmedown.MyDBHandler.addUser(MyDBHandler.java:96)
at com.example.asas.handmedown.SignupActivity.addButtonClicked(SignupActivity.java:117)
at com.example.asas.handmedown.SignupActivity$1.onClick(SignupActivity.java:44)
at android.view.View.performClick(View.java:4756)
at android.view.View$PerformClick.run(View.java:19749)
at android.os.Handler.handleCallback(Handler.java:739)
at android.os.Handler.dispatchMessage(Handler.java:95)
at android.os.Looper.loop(Looper.java:135)
at android.app.ActivityThread.main(ActivityThread.java:5221)
at java.lang.reflect.Method.invoke(Native Method)
at java.lang.reflect.Method.invoke(Method.java:372)
at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:899)
at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:694)
Here is my DatabaseHandler class.
public class MyDBHandler extends SQLiteOpenHelper {
private static final int DATABASE_VERSION = 1;
private static final String DATABASE_NAME = "userDB.db";
public static final String TABLE_USERS = "users";
public static final String COLUMN_USERID = "_userid";
public static final String COLUMN_USERNAME = "username";
public static final String COLUMN_USERPWD = "userpwd";
public static final String COLUMN_USERBATCH = "userbatch";
public static final String COLUMN_USERMOB = "usermob";
public static final String TABLE_ITEMS = "items";
public static final String COLUMN_ITEMID = "_itemid";
public static final String COLUMN_ITEMUID = "_userid";
public static final String COLUMN_ITEMNAME = "itemname";
public static final String COLUMN_ITEMDESC = "itemdesc";
public static final String COLUMN_ITEMPRICE = "itemprice";
public static final String COLUMN_ITEMIMG = "itemimg";
private static MyDBHandler sInstance;
/*public static synchronized MyDBHandler getInstance(Context context){
// Use the application context, which will ensure that you don't accidentally leak an Activity's context.
if (sInstance == null) {
sInstance = new MyDBHandler(context.getApplicationContext(), DATABASE_NAME, null, DATABASE_VERSION);
}
return sInstance;
}*/
public MyDBHandler(Context context, String name, SQLiteDatabase.CursorFactory factory, int version) {
super(context, DATABASE_NAME, factory, DATABASE_VERSION);
}
@Override
public void onCreate(SQLiteDatabase db) {
String query_users = "CREATE TABLE " + TABLE_USERS + "(" +
COLUMN_USERID + " INTEGER PRIMARY KEY, " +
COLUMN_USERNAME + " TEXT," + COLUMN_USERPWD + " TEXT," + COLUMN_USERBATCH + " TEXT,"+
COLUMN_USERMOB + " NUMBER" +
");";
db.execSQL(query_users);
String query_items = "CREATE TABLE " + TABLE_ITEMS + "(" +
COLUMN_ITEMID + " INTEGER PRIMARY KEY, " + COLUMN_ITEMUID + " NUMBER, " +
COLUMN_ITEMNAME + " TEXT," + COLUMN_ITEMDESC + " TEXT," + COLUMN_ITEMPRICE+ " NUMBER,"+
COLUMN_ITEMIMG + "BLOB" +
");";
db.execSQL(query_items);
db.close();
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
db.execSQL("DROP TABLE IF EXISTS " + TABLE_USERS);
db.execSQL("DROP TABLE IF EXISTS " + TABLE_ITEMS);
onCreate(db);
db.close();
}
//Add row to the database
public void addUser(User user) {
ContentValues values = new ContentValues();
values.put(COLUMN_USERID, user.get_id());
values.put(COLUMN_USERNAME, user.get_username());
values.put(COLUMN_USERPWD, user.get_userpwd());
values.put(COLUMN_USERBATCH, user.get_userbatch());
values.put(COLUMN_USERMOB, user.get_usermob());
SQLiteDatabase db = getWritableDatabase();
db.beginTransaction();
db.insert(TABLE_USERS, null, values);
db.endTransaction();
db.close();
}
public void addItem(Item item) {
try {
FileInputStream fis=new FileInputStream(item.get_itemimg());
byte[] image = new byte[fis.available()];
fis.read(image);
ContentValues values = new ContentValues();
values.put(COLUMN_ITEMID, item.get_id());
values.put(COLUMN_ITEMUID, item.get_userid());
values.put(COLUMN_ITEMNAME, item.get_itemname());
values.put(COLUMN_ITEMDESC, item.get_itemdesc());
values.put(COLUMN_ITEMPRICE, item.get_itemprice());
values.put(COLUMN_ITEMIMG, image);
SQLiteDatabase db = getWritableDatabase();
db.insert(TABLE_ITEMS, null, values);
db.close();
fis.close();
}
catch (Exception e){
//Error
}
}
//delete product from database
public void deleteUser(int userId){
SQLiteDatabase db = getWritableDatabase();
db.execSQL("DELETE FROM " + TABLE_USERS + " WHERE " + COLUMN_USERID + " =\"" + userId + "\";");
db.close();
}
//delete product from database
public void deleteItem(int itemId){
SQLiteDatabase db = getWritableDatabase();
db.execSQL("DELETE FROM " + TABLE_ITEMS + " WHERE " + COLUMN_ITEMID + " =\"" + itemId + "\";");
db.close();
}
//update user details
public void updateUser(User user){
SQLiteDatabase db = getWritableDatabase();
db.beginTransaction();
ContentValues values = new ContentValues();
values.put(COLUMN_USERID, user.get_id());
values.put(COLUMN_USERNAME, user.get_username());
values.put(COLUMN_USERPWD, user.get_userpwd());
values.put(COLUMN_USERBATCH, user.get_userbatch());
values.put(COLUMN_USERMOB, user.get_usermob());
db.update(TABLE_USERS, values, COLUMN_USERID + " = ?", new String[]{String.valueOf(user.get_id())});
db.close();
db.endTransaction();
}
public void updateItem(Item item){
try {
FileInputStream fis = new FileInputStream(item.get_itemimg());
byte[] image = new byte[fis.available()];
fis.read(image);
SQLiteDatabase db = getWritableDatabase();
ContentValues values = new ContentValues();
values.put(COLUMN_ITEMID, item.get_id());
values.put(COLUMN_ITEMUID, item.get_userid());
values.put(COLUMN_ITEMNAME, item.get_itemname());
values.put(COLUMN_ITEMDESC, item.get_itemdesc());
values.put(COLUMN_ITEMPRICE, item.get_itemprice());
values.put(COLUMN_ITEMIMG,image);
db.update(TABLE_ITEMS, values, COLUMN_ITEMID + " = ?", new String[]{String.valueOf(item.get_id())});
db.close();
fis.close();
}
catch (Exception e){
//Error
}
}
//print out the users table as a string
public User userDatabaseToString(int userId){
String dbString = "";
User user = new User();
SQLiteDatabase db = getWritableDatabase();
db.beginTransaction();
String query = "SELECT * FROM " + TABLE_USERS + " WHERE " + COLUMN_USERID + " = " + userId;
//Cursor points to a location in your results
Cursor c = db.rawQuery(query,null);
//Move to the 1st row in ur results
c.moveToFirst();
while (!c.isAfterLast()){
if (c.getString(c.getColumnIndex(COLUMN_USERID))!=null){
user.set_id(c.getInt(c.getColumnIndex(COLUMN_USERID)));
user.set_username(c.getString(c.getColumnIndex(COLUMN_USERNAME)));
user.set_userpwd(c.getString(c.getColumnIndex(COLUMN_USERPWD)));
user.set_userbatch(c.getString(c.getColumnIndex(COLUMN_USERBATCH)));
user.set_usermob(c.getString(c.getColumnIndex(COLUMN_USERMOB)));
//dbString += "\n";
}
c.moveToNext();
}
c.close();
db.close();
db.endTransaction();
return user;
}
//print out the items table as a string
public Item itemDatabaseToString(int itemId){
String dbString = "";
Item item = new Item();
SQLiteDatabase db = getWritableDatabase();
String query = "SELECT * FROM " + TABLE_ITEMS + " WHERE " + COLUMN_ITEMID + " = " + itemId;
//Cursor points to a location in your results
Cursor c = db.rawQuery(query,null);
//Move to the 1st row in ur results
c.moveToFirst();
while (!c.isAfterLast()){
if (c.getString(c.getColumnIndex(COLUMN_ITEMID))!=null){
item.set_id(c.getInt(c.getColumnIndex(COLUMN_ITEMID)));
item.set_userid(c.getInt(c.getColumnIndex(COLUMN_ITEMUID)));
item.set_itemname(c.getString(c.getColumnIndex(COLUMN_ITEMNAME)));
item.set_itemdesc(c.getString(c.getColumnIndex(COLUMN_ITEMDESC)));
item.set_itemprice(c.getFloat(c.getColumnIndex(COLUMN_ITEMPRICE)));
item.set_itemimg("ImagePath");
//dbString += "\n";
}
c.moveToNext();
}
c.close();
db.close();
return item;
}
//to get Item Image
public Bitmap getItemImage(int itemId){
String dbString = "";
SQLiteDatabase db = getWritableDatabase();
String query = "SELECT * FROM " + TABLE_ITEMS + " WHERE " + COLUMN_ITEMID + " = " + itemId;
Bitmap bmp=null;
Cursor c = db.rawQuery(query,null);
c.moveToFirst();
while (!c.isAfterLast()){
if (c.getString(c.getColumnIndex(COLUMN_ITEMID))!=null){
byte[] image=c.getBlob(c.getColumnIndex(COLUMN_ITEMIMG));
bmp = BitmapFactory.decodeByteArray(image, 0, image.length);
}
c.moveToNext();
}
c.close();
db.close();
return bmp;
}
//search for occurance in user database
public boolean searchUserOccurance(int userId){
SQLiteDatabase db = getWritableDatabase();
db.beginTransaction();
String query = "SELECT * FROM " + TABLE_USERS + " WHERE " + COLUMN_USERID + " = " + userId;
Log.d("search",query);
Cursor c = db.rawQuery(query, null);
c.moveToFirst();
if(c.getCount() <= 0){
c.close();
return false;
}
while(!c.isAfterLast()){
if (c.getInt(c.getColumnIndex(COLUMN_USERID)) == userId){
return true;
}
c.moveToNext();
}
c.close();
db.close();
db.endTransaction();
return false;
}
//search for occurance in items database
public boolean searchItemOccurance(int itemId){
SQLiteDatabase db = getWritableDatabase();
String query = "SELECT * FROM " + TABLE_ITEMS + " WHERE " + COLUMN_ITEMID + " = " + itemId;
Log.d("search",query);
Cursor c = db.rawQuery(query, null);
c.moveToFirst();
if(c.getCount() <= 0){
c.close();
return false;
}
while(!c.isAfterLast()){
if (c.getInt(c.getColumnIndex(COLUMN_ITEMID)) == itemId){
return true;
}
c.moveToNext();
}
c.close();
db.close();
return false;
}
}
I've not used any explicit threads in my program code, since because I am not well versed in thread programming.
