I know similar questions have been asked many a time before; but after scrubbing through posts for hours, I haven't been able to find a solution. My implementation of SQLite refuses to work correctly, and the database is not being created, even though I am calling getWritableDatabase. I have tried uninstalling and reinstalling the app to remove an empty db that might be there, but that didn't work. The error in logcat is as follows:
-25 09:19:48.924 6149-6149/com.nikhilp.thecashcontroller E/AndroidRuntime﹕ FATAL EXCEPTION: main
Process: com.nikhilp.thecashcontroller, PID: 6149
java.lang.RuntimeException: Unable to start activity ComponentInfo{com.nikhilp.thecashcontroller/com.nikhilp.thecashcontroller.activity.MainActivity}: android.database.sqlite.SQLiteException: no such table: expenses (code 1): , while compiling: SELECT * FROM expenses
at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2325)
at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:2390)
at android.app.ActivityThread.access$800(ActivityThread.java:151)
at android.app.ActivityThread$H.handleMessage(ActivityThread.java:1303)
at android.os.Handler.dispatchMessage(Handler.java:102)
at android.os.Looper.loop(Looper.java:135)
at android.app.ActivityThread.main(ActivityThread.java:5257)
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:903)
at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:698)
Caused by: android.database.sqlite.SQLiteException: no such table: expenses (code 1): , while compiling: SELECT * FROM expenses
at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method)
at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:889)
at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:500)
at android.database.sqlite.SQLiteSession.prepare(SQLiteSession.java:588)
at android.database.sqlite.SQLiteProgram.<init>(SQLiteProgram.java:58)
at android.database.sqlite.SQLiteQuery.<init>(SQLiteQuery.java:37)
at android.database.sqlite.SQLiteDirectCursorDriver.query(SQLiteDirectCursorDriver.java:44)
at android.database.sqlite.SQLiteDatabase.rawQueryWithFactory(SQLiteDatabase.java:1316)
at android.database.sqlite.SQLiteDatabase.rawQuery(SQLiteDatabase.java:1255)
at com.nikhilp.thecashcontroller.MyDBHandler.getExpenses(MyDBHandler.java:109)
at com.nikhilp.thecashcontroller.activity.MainActivity.onCreate(MainActivity.java:104)
at android.app.Activity.performCreate(Activity.java:5990)
at android.app.Instrumentation.callActivityOnCreate(Instrumentation.java:1106)
at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2278)...
MAIN ACTIVITY ONCREATE (CONCERNING DATABASE)
public MyDBHandler myDBHandler;
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
myDBHandler = new MyDBHandler(this);
ArrayList <Expense> temp = myDBHandler.getExpenses();
displayView(0);
}
DATABASE HANDLER
package com.nikhilp.thecashcontroller;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;
import android.widget.Toast;
import java.util.ArrayList;
public class MyDBHandler extends SQLiteOpenHelper{
private Context context;
private static final int DATABASE_VERSION = 1;
private static final String DATABASE_NAME = "cashdatabase.db";
public static final String TABLE_EXPENSES = "expenses";
public static final String COLUMN_ID = "_id";
public static final String COLUMN_AMOUNT = "_amount";
public static final String COLUMN_NOTE = "_note";
public static final String COLUMN_DATE = "_date";
public static final String COLUMN_CATEGORY = "_category";
public SQLiteDatabase db1;
private static final String CREATE_TABLE = "CREATE TABLE IF NOT EXISTS "+ TABLE_EXPENSES+"(" +
COLUMN_ID+ " INTEGER PRIMARY KEY AUTOINCREMENT" +
COLUMN_AMOUNT+ " TEXT " +
COLUMN_NOTE+ " TEXT " +
COLUMN_CATEGORY+ " TEXT "+
COLUMN_DATE+" TEXT" + ");";
private static final String DROP_TABLE = "DROP TABLE IF EXISTS "+TABLE_EXPENSES;
public MyDBHandler(Context context) {
super(context, DATABASE_NAME, null, 1);
this.context = context;
}
@Override
public void onOpen(SQLiteDatabase database) {
if(!database.isOpen()) {
SQLiteDatabase.openDatabase(database.getPath(), null, SQLiteDatabase.NO_LOCALIZED_COLLATORS |
SQLiteDatabase.CREATE_IF_NECESSARY);
}
}
@Override
public void onCreate(SQLiteDatabase db) {
try {
db.execSQL(CREATE_TABLE);
Message.message(context, "onCreate() called");
} catch (SQLException e) {
Message.message(context,e.toString());
}
}
//upgrade existing table
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
try {
db.execSQL(DROP_TABLE);
Message.message(context, "onUpgrade() called");
onCreate(db);
} catch (SQLException e) {
Message.message(context, e.toString());
}
}
//Add a new row to db
public void addExpense (Expense expense){
SQLiteDatabase db = this.getWritableDatabase();
ContentValues values = new ContentValues();
values.put(COLUMN_AMOUNT, expense.getAmount());
values.put(COLUMN_NOTE, expense.label);
values.put(COLUMN_CATEGORY, expense.category);
values.put(COLUMN_DATE, expense.sDate);
db.insert(TABLE_EXPENSES, null, values);
db.close();
}
//Delete Expense
public void deleteExpense (String label, double amount){
SQLiteDatabase db = getWritableDatabase();
db.execSQL("DELETE FROM " + TABLE_EXPENSES + " WHERE " + COLUMN_AMOUNT + "=" + amount + " AND " + COLUMN_NOTE + "=" + label);
}
public ArrayList<Expense> getExpenses(){
SQLiteDatabase db = this.getWritableDatabase();
ArrayList<Expense> expenses = new ArrayList<>();
// 1. build the query
String query = "SELECT * FROM " + TABLE_EXPENSES;
// 2. get reference to writable DB
onCreate(db);
Cursor cursor = db.rawQuery(query, null);
// 3. go over each row, build book and add it to list
Expense expense = null;
if (cursor.moveToFirst()) {
do {
expense = new Expense();
expense.setAmount(Double.parseDouble(cursor.getString(0)));
expense.label = (cursor.getString(1));
expense.category = Integer.parseInt(cursor.getString(2));
expense.sDate = cursor.getString(3);
// Add book to books
expenses.add(expense);
} while (cursor.moveToNext());
}
Log.d("getExpenses()", expenses.toString());
// return books
return expenses;
}
}
You're catching the exception in onCreate(SQLiteDatabase db) and silently letting it off. The crash should have been taken place in your onCreate() method itself as your query to create the table is wrong. You're missing commas between various columns in your create query.
private static final String CREATE_TABLE = "CREATE TABLE IF NOT EXISTS "+ TABLE_EXPENSES+"(" +
COLUMN_ID+ " INTEGER PRIMARY KEY AUTOINCREMENT," +
COLUMN_AMOUNT+ " TEXT, " +
COLUMN_NOTE+ " TEXT, " +
COLUMN_CATEGORY+ " TEXT, "+
COLUMN_DATE+" TEXT" + ")";