problems in creating multiple tables in sqlite

Question

I'm using the following code for creating multiple tables in a database. But, I don't understand why this problem happens.

private static final String TABLE_SMSFilter = "SMSFilter";


public void onCreate(SQLiteDatabase db)
    {
        Log.d("Test", "Control is in Oncreate()");
        String CREATE_SMSSSCHEDULE_TABLE = "CREATE TABLE " + TABLE_SMSSchedule
                + "(" + KEY_ID + " INTEGER PRIMARY KEY autoincrement,"
                + KEY_NUMBER + " TEXT)";

        String CREATE_PROFILE_SCHEDULE_TABLE = "CREATE TABLE "
                + TABLE_ProfileSchedule + "(" + ProfileSchedule_ID
                + " INTEGER PRIMARY KEY autoincrement,"
                + ProfileSchedule_NUMBER + " TEXT, "
                + ProfileSchedule_ProfileMode + " TEXT,"
                + ProfileSchedule_CalendarID + "INTEGER)";

        String CREATE_SMS_FILTER_TABLE = "CREATE TABLE " + TABLE_SMSFilter
                + "(" + SMSFilter_ID + " INTEGER PRIMARY KEY autoincrement,"
                + SMSFilter_NUMBER + " TEXT)";

        db.execSQL(CREATE_SMSSSCHEDULE_TABLE);
        db.execSQL(CREATE_PROFILE_SCHEDULE_TABLE);
        db.execSQL(CREATE_SMS_FILTER_TABLE);
    }

    // Upgrading database
    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion)
    {
        // Drop older table if existed
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_SMSSchedule);
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_ProfileSchedule);

        db.execSQL("DROP TABLE IF EXISTS " + TABLE_SMSFilter);

        // Create tables again
        onCreate(db);
    }

here is the insert function

public void addSMSFilter(SMSFilter filterVariable)
    {

        SQLiteDatabase db = this.getWritableDatabase();

        ContentValues values = new ContentValues();
        values.put(SMSFilter_NUMBER, filterVariable.getPhoneNumber()); // Contact
                                                                        // Name
        // values.put(KEY_PH_NO, contact.getPhoneNumber()); // Contact Phone

        Log.d("test", "inserting" + filterVariable.getPhoneNumber());
        // Inserting Row
        db.insert(TABLE_SMSFilter, null, values);

        db.close(); // Closing database connection
    }

and now in my main activity, I used this code to insert into this table

DatabaseHandler db = new DatabaseHandler(this);

        SMSFilter sms = new SMSFilter("1234556");
        db.addSMSFilter(sms);

but it gives me the error that no table "SMSFILTER" found.

Log result is here

11-25 22:52:22.643: I/Database(12209): sqlite returned: error code = 1, msg = no such table: SMSFilter
11-25 22:52:22.643: E/Database(12209): Error inserting PhoneNo=1234556
11-25 22:52:22.643: E/Database(12209): android.database.sqlite.SQLiteException: no such table: SMSFilter: , while compiling: INSERT INTO SMSFilter(PhoneNo) VALUES(?);
11-25 22:52:22.643: E/Database(12209):  at android.database.sqlite.SQLiteCompiledSql.native_compile(Native Method)
11-25 22:52:22.643: E/Database(12209):  at android.database.sqlite.SQLiteCompiledSql.compile(SQLiteCompiledSql.java:91)
11-25 22:52:22.643: E/Database(12209):  at android.database.sqlite.SQLiteCompiledSql.<init>(SQLiteCompiledSql.java:64)
11-25 22:52:22.643: E/Database(12209):  at android.database.sqlite.SQLiteProgram.<init>(SQLiteProgram.java:80)
11-25 22:52:22.643: E/Database(12209):  at android.database.sqlite.SQLiteStatement.<init>(SQLiteStatement.java:36)
11-25 22:52:22.643: E/Database(12209):  at android.database.sqlite.SQLiteDatabase.compileStatement(SQLiteDatabase.java:1212)
11-25 22:52:22.643: E/Database(12209):  at android.database.sqlite.SQLiteDatabase.insertWithOnConflict(SQLiteDatabase.java:1610)
11-25 22:52:22.643: E/Database(12209):  at android.database.sqlite.SQLiteDatabase.insert(SQLiteDatabase.java:1484)
11-25 22:52:22.643: E/Database(12209):  at com.scheduler.database.DatabaseHandler.addSMSFilter(DatabaseHandler.java:327)
11-25 22:52:22.643: E/Database(12209):  at com.schedule.test.TestActivity.onCreate(TestActivity.java:31)
11-25 22:52:22.643: E/Database(12209):  at android.app.Instrumentation.callActivityOnCreate(Instrumentation.java:1069)
11-25 22:52:22.643: E/Database(12209):  at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2751)
11-25 22:52:22.643: E/Database(12209):  at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:2803)
11-25 22:52:22.643: E/Database(12209):  at android.app.ActivityThread.access$2300(ActivityThread.java:135)
11-25 22:52:22.643: E/Database(12209):  at android.app.ActivityThread$H.handleMessage(ActivityThread.java:2136)
11-25 22:52:22.643: E/Database(12209):  at android.os.Handler.dispatchMessage(Handler.java:99)
11-25 22:52:22.643: E/Database(12209):  at android.os.Looper.loop(Looper.java:144)
11-25 22:52:22.643: E/Database(12209):  at android.app.ActivityThread.main(ActivityThread.java:4937)
11-25 22:52:22.643: E/Database(12209):  at java.lang.reflect.Method.invokeNative(Native Method)
11-25 22:52:22.643: E/Database(12209):  at java.lang.reflect.Method.invoke(Method.java:521)
11-25 22:52:22.643: E/Database(12209):  at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:858)
11-25 22:52:22.643: E/Database(12209):  at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:616)
11-25 22:52:22.643: E/Database(12209):  at dalvik.system.NativeStart.main(Native Method)

Did you tried uninstalling the app and then reinstalling it? — user
– user, Commented Nov 25, 2012 at 18:04
@Luksprog yesssss it works. if u plz tel me what was the prolem??? — Ahmad Abbasi
– Ahmad Abbasi, Commented Nov 25, 2012 at 18:09
onCreate() is called the first time the database is created. onUpgrade() is called when the DB_VERSION has changed. So in your case, you had an existing Db with the same version, and hence no modifications were made even after you added the other tables (I'm assuming you did this after you already had one table earlier) — varevarao
– varevarao, Commented Nov 25, 2012 at 18:11

Yaqub Ahmad · Accepted Answer · 2012-12-03 17:29:07Z

1

onUpgrade is called when databse version number is changed. When updating your table you need to increment database version number, specify new table creation query in onCreate method and put ALTER TABLE to onUpgrade method to update previous version of table. When Android detects database version mismatch, it will call onUpgrade method automatically

answered Dec 3, 2012 at 17:29

Yaqub Ahmad

27.7k24 gold badges105 silver badges151 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

saeed khalafinejad · Accepted Answer · 2013-10-19 20:56:04Z

0

Note that onCreate is only called the first time the database physical file is being created. It seems that the onCreate method is not called because the database already exists somewhere in the SDCard. Hence the table SMSFilter is not created. You need to copy the code within onCreate to the onUpgrade method (after the drop table commands). Also, to trigger onUpgrade you must increase the database version.

answered Oct 19, 2013 at 20:56

saeed khalafinejad

1,1519 silver badges22 bronze badges

Comments

BenMorel · Accepted Answer · 2014-06-17 20:11:54Z

0

String CREATE_PROFILE_SCHEDULE_TABLE = "CREATE TABLE "
            + TABLE_ProfileSchedule + "(" + ProfileSchedule_ID
            + " INTEGER PRIMARY KEY autoincrement,"
            + ProfileSchedule_NUMBER + " TEXT, "
            + ProfileSchedule_ProfileMode + " TEXT,"
            + ProfileSchedule_CalendarID + "INTEGER)";

Update this to

String CREATE_PROFILE_SCHEDULE_TABLE = "CREATE TABLE "
            + TABLE_ProfileSchedule + "(" + ProfileSchedule_ID
            + " INTEGER PRIMARY KEY autoincrement,"
            + ProfileSchedule_NUMBER + " TEXT, "
            + ProfileSchedule_ProfileMode + " TEXT,"
            + ProfileSchedule_CalendarID + " INTEGER)";

Your schedule table create code is not working as in the last line no space b/w integer and column name is creating problem.

edited Jun 17, 2014 at 20:11

BenMorel

37k52 gold badges208 silver badges339 bronze badges

answered Aug 20, 2013 at 11:57

Rajnish Mishra

8365 silver badges22 bronze badges

Collectives™ on Stack Overflow

problems in creating multiple tables in sqlite

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related