Can't find table in SQLite

Question

I'm trying to add an SQLite database to my android app. I made a separate class for the pin table (Pin.java) and a database handler in MySQLiteHelper.java

Now I'm trying to call the addPin method in one of my activities and it gives me an error: http://pastebin.com/8h4k3Quh

11-20 18:39:44.011: E/SQLiteLog(22644): (1) no such table: tblPins
11-20 18:39:44.021: E/SQLiteDatabase(22644): Error inserting pinMessage=testinh pinDuration=1 pinTitle=test
11-20 18:39:44.021: E/SQLiteDatabase(22644): android.database.sqlite.SQLiteException: no such table: tblPins (code 1): , while compiling: INSERT INTO tblPins(pinMessage,pinDuration,pinTitle) VALUES (?,?,?)
11-20 18:39:44.021: E/SQLiteDatabase(22644):    at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method)
11-20 18:39:44.021: E/SQLiteDatabase(22644):    at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:889)
11-20 18:39:44.021: E/SQLiteDatabase(22644):    at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:500)
11-20 18:39:44.021: E/SQLiteDatabase(22644):    at android.database.sqlite.SQLiteSession.prepare(SQLiteSession.java:588)
11-20 18:39:44.021: E/SQLiteDatabase(22644):    at android.database.sqlite.SQLiteProgram.<init>(SQLiteProgram.java:58)
11-20 18:39:44.021: E/SQLiteDatabase(22644):    at android.database.sqlite.SQLiteStatement.<init>(SQLiteStatement.java:31)
11-20 18:39:44.021: E/SQLiteDatabase(22644):    at android.database.sqlite.SQLiteDatabase.insertWithOnConflict(SQLiteDatabase.java:1467)
11-20 18:39:44.021: E/SQLiteDatabase(22644):    at android.database.sqlite.SQLiteDatabase.insert(SQLiteDatabase.java:1339)
11-20 18:39:44.021: E/SQLiteDatabase(22644):    at com.github.viperdream.MySQLiteHelper.addPin(MySQLiteHelper.java:54)
11-20 18:39:44.021: E/SQLiteDatabase(22644):    at com.github.viperdream.AddPinActivity.btnCreatePin(AddPinActivity.java:39)
11-20 18:39:44.021: E/SQLiteDatabase(22644):    at java.lang.reflect.Method.invokeNative(Native Method)
11-20 18:39:44.021: E/SQLiteDatabase(22644):    at java.lang.reflect.Method.invoke(Method.java:525)
11-20 18:39:44.021: E/SQLiteDatabase(22644):    at android.view.View$1.onClick(View.java:3628)
11-20 18:39:44.021: E/SQLiteDatabase(22644):    at android.view.View.performClick(View.java:4240)
11-20 18:39:44.021: E/SQLiteDatabase(22644):    at android.view.View$PerformClick.run(View.java:17721)
11-20 18:39:44.021: E/SQLiteDatabase(22644):    at android.os.Handler.handleCallback(Handler.java:730)
11-20 18:39:44.021: E/SQLiteDatabase(22644):    at android.os.Handler.dispatchMessage(Handler.java:92)
11-20 18:39:44.021: E/SQLiteDatabase(22644):    at android.os.Looper.loop(Looper.java:137)
11-20 18:39:44.021: E/SQLiteDatabase(22644):    at android.app.ActivityThread.main(ActivityThread.java:5103)
11-20 18:39:44.021: E/SQLiteDatabase(22644):    at java.lang.reflect.Method.invokeNative(Native Method)
11-20 18:39:44.021: E/SQLiteDatabase(22644):    at java.lang.reflect.Method.invoke(Method.java:525)
11-20 18:39:44.021: E/SQLiteDatabase(22644):    at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:737)
11-20 18:39:44.021: E/SQLiteDatabase(22644):    at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:553)
11-20 18:39:44.021: E/SQLiteDatabase(22644):    at dalvik.system.NativeStart.main(Native Method)
11-20 18:39:44.021: E/SQLiteLog(22644): (1) no such table: tblPins
11-20 18:39:44.021: D/AndroidRuntime(22644): Shutting down VM
11-20 18:39:44.021: W/dalvikvm(22644): threadid=1: thread exiting with uncaught exception (group=0x415df700)
11-20 18:39:44.021: E/AndroidRuntime(22644): FATAL EXCEPTION: main
11-20 18:39:44.021: E/AndroidRuntime(22644): java.lang.IllegalStateException: Could not execute method of the activity
11-20 18:39:44.021: E/AndroidRuntime(22644):    at android.view.View$1.onClick(View.java:3633)
11-20 18:39:44.021: E/AndroidRuntime(22644):    at android.view.View.performClick(View.java:4240)
11-20 18:39:44.021: E/AndroidRuntime(22644):    at android.view.View$PerformClick.run(View.java:17721)
11-20 18:39:44.021: E/AndroidRuntime(22644):    at android.os.Handler.handleCallback(Handler.java:730)
11-20 18:39:44.021: E/AndroidRuntime(22644):    at android.os.Handler.dispatchMessage(Handler.java:92)
11-20 18:39:44.021: E/AndroidRuntime(22644):    at android.os.Looper.loop(Looper.java:137)
11-20 18:39:44.021: E/AndroidRuntime(22644):    at android.app.ActivityThread.main(ActivityThread.java:5103)
11-20 18:39:44.021: E/AndroidRuntime(22644):    at java.lang.reflect.Method.invokeNative(Native Method)
11-20 18:39:44.021: E/AndroidRuntime(22644):    at java.lang.reflect.Method.invoke(Method.java:525)
11-20 18:39:44.021: E/AndroidRuntime(22644):    at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:737)
11-20 18:39:44.021: E/AndroidRuntime(22644):    at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:553)
11-20 18:39:44.021: E/AndroidRuntime(22644):    at dalvik.system.NativeStart.main(Native Method)
11-20 18:39:44.021: E/AndroidRuntime(22644): Caused by: java.lang.reflect.InvocationTargetException
11-20 18:39:44.021: E/AndroidRuntime(22644):    at java.lang.reflect.Method.invokeNative(Native Method)
11-20 18:39:44.021: E/AndroidRuntime(22644):    at java.lang.reflect.Method.invoke(Method.java:525)
11-20 18:39:44.021: E/AndroidRuntime(22644):    at android.view.View$1.onClick(View.java:3628)
11-20 18:39:44.021: E/AndroidRuntime(22644):    ... 11 more
11-20 18:39:44.021: E/AndroidRuntime(22644): Caused by: android.database.sqlite.SQLiteException: no such table: tblPins (code 1): , while compiling: Select * FROM tblPins WHERE pinTitle = "test"
11-20 18:39:44.021: E/AndroidRuntime(22644):    at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method)
11-20 18:39:44.021: E/AndroidRuntime(22644):    at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:889)
11-20 18:39:44.021: E/AndroidRuntime(22644):    at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:500)
11-20 18:39:44.021: E/AndroidRuntime(22644):    at android.database.sqlite.SQLiteSession.prepare(SQLiteSession.java:588)
11-20 18:39:44.021: E/AndroidRuntime(22644):    at android.database.sqlite.SQLiteProgram.<init>(SQLiteProgram.java:58)
11-20 18:39:44.021: E/AndroidRuntime(22644):    at android.database.sqlite.SQLiteQuery.<init>(SQLiteQuery.java:37)
11-20 18:39:44.021: E/AndroidRuntime(22644):    at android.database.sqlite.SQLiteDirectCursorDriver.query(SQLiteDirectCursorDriver.java:44)
11-20 18:39:44.021: E/AndroidRuntime(22644):    at android.database.sqlite.SQLiteDatabase.rawQueryWithFactory(SQLiteDatabase.java:1314)
11-20 18:39:44.021: E/AndroidRuntime(22644):    at android.database.sqlite.SQLiteDatabase.rawQuery(SQLiteDatabase.java:1253)
11-20 18:39:44.021: E/AndroidRuntime(22644):    at com.github.viperdream.MySQLiteHelper.findPin(MySQLiteHelper.java:61)
11-20 18:39:44.021: E/AndroidRuntime(22644):    at com.github.viperdream.AddPinActivity.btnCreatePin(AddPinActivity.java:41)

It says it can't find the table tblPins, but I thought it'd create automatically after calling the function.

I can't seem to figure this one out, here's my code:

Databasehandler, MySQLiteHelper.java:

public class MySQLiteHelper extends SQLiteOpenHelper{

    private static final int DATABASE_VERSION = 1;

    private static final String DATABASE_NAME = "PinDB";

    private static final String TABLE_PINS = "tblPins";

    private static final String KEY_PINID = "pin_id";
    private static final String KEY_PINTITLE = "pinTitle";
    private static final String KEY_PINMESSAGE = "pinMessage";
    private static final String KEY_PINDURATION = "pinDuration";

    private static final String[] COLUMNS = {KEY_PINID, KEY_PINTITLE, KEY_PINMESSAGE, KEY_PINDURATION};

    public MySQLiteHelper(Context context, String name, CursorFactory factory, int version){
        super(context, DATABASE_NAME, factory, DATABASE_VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase db){
        db.execSQL("CREATE TABLE "
                + TABLE_PINS + " ("
                + KEY_PINID +" INTEGER PRIMARY KEY," + KEY_PINTITLE + 
                " TEXT,"+
                KEY_PINMESSAGE+" TEXT,"
                + KEY_PINDURATION +" INTEGER "+ ")");
    }
    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersio, int newVersion){
        db.execSQL("DROP TABLE IF EXISTS tblPins");
        this.onCreate(db);
    }

    public void addPin(Pin pin){
        Log.d("addPin", pin.toString());
        SQLiteDatabase db = this.getWritableDatabase();
        ContentValues values = new ContentValues();

        values.put(KEY_PINTITLE, pin.getPinTitle());
        values.put(KEY_PINMESSAGE, pin.getPinMessage());
        values.put(KEY_PINDURATION, pin.getPinDuration());

        db.insert(TABLE_PINS, null, values);
        db.close();
    }

    public Pin findPin(String pinTitle){
        String query = "Select * FROM " + TABLE_PINS + " WHERE " + KEY_PINTITLE + " = \"" + pinTitle +"\"";
        SQLiteDatabase db = this.getWritableDatabase();
        Cursor cursor = db.rawQuery(query, null);
        Pin pin = new Pin();

        if (cursor.moveToFirst()){
            cursor.moveToFirst();
            pin.setPinID(Integer.parseInt(cursor.getString(0)));
            pin.setPinTitle(cursor.getString(1));
            pin.setPinMessage(cursor.getString(2));
            pin.setPinDuration(Integer.parseInt(cursor.getString(3)));
            cursor.close();
        }else{
            pin = null;
        }
        db.close();
        return pin;
    }

    public boolean deletePin(String pinTitle){
        boolean result = false;
        String query = "Select * FROM " + TABLE_PINS + " WHERE " + KEY_PINTITLE + " = \"" + pinTitle + "\"";
        SQLiteDatabase db = this.getWritableDatabase();
        Cursor cursor = db.rawQuery(query,  null);
        Pin pin = new Pin();

        if(cursor.moveToFirst()){
            pin.setPinID(Integer.parseInt(cursor.getString(0)));
            db.delete(TABLE_PINS, KEY_PINID + " = ?",
                    new String[] { String.valueOf(pin.getID()) });
            cursor.close();
            result = true;
        }
        db.close();
        return result;
    }

}

If you need any more information, please do not hesitate to ask!

Thanks in advance

Update: I've managed to get the table in, I've put onCreate() in the addPin method, but is there any reason why this method doesn't get called when I don't put it in addPin?

most likely you added the create query after you first used the version 1. try to uninstall your application. — njzk2, Nov 20 '13 at 17:50
@njzk2 or just update DATABASE_VERSION to 2 and update the app. — ddmps, Nov 20 '13 at 17:53
Can you see in `Database` that table has been created or not? If its present then in what `schema`. May be you have to specifically say the shema name. — Smit, Nov 20 '13 at 17:56
I tried giving the database a higher version, but I'm still getting the same error. @Smit how exactly would I check for that? Do I use adb for it? I'm using a real device to debug by the way. — Viperdream, Nov 20 '13 at 18:32
follow this video and write oncreate like this: http://thenewboston.org/watch.php?cat=6&number=114 — Ranjit, Nov 20 '13 at 18:38
@Viperdream You can use `ADB` see [Command Line Shell For SQLite](http://www.sqlite.org/sqlite.html) **OR** you can do it using java see [How do I check in SQLite whether a table exists?](http://stackoverflow.com/questions/1601151/how-do-i-check-in-sqlite-whether-a-table-exists). — Smit, Nov 20 '13 at 18:55
@Smit well I managed getting my table up, by placing the onCreate in the addPin method, but is there another way to call it? — Viperdream, Nov 20 '13 at 19:09
@Viperdream `addPin` is definitely not a good place to call that method. When you call addPin next time it could throw you exception that table already exist. Better you debug your code and see why this is happening. Make sure while creating `connection` you have given correct `DataBase` name — Smit, Nov 20 '13 at 19:29

Can't find table in SQLite

0 Answers0