SQLite insertion error

Question

This is the calling file:

@Override
public void onCreate(Bundle savedInstanceState) {

    super.onCreate(savedInstanceState);
    setContentView(R.layout.main);
    dbActivities = new DBActivities(this);
    dbActivities.open();    // Create or open the existing DB if already created
}

private final BroadcastReceiver receiver = new BroadcastReceiver() {
        @Override
        public void onReceive(Context context, Intent intent) {
            if(intent.getAction().equals("android.provider.Telephony.SMS_RECEIVED")) {
                smsCount++;
                Log.d(LOG_TAG, "smsCount=" +smsCount);
                long dbInsertReturn = dbActivities.insertIntoDB(smsCount);
            }
        }
};

This is the file that does the actual DB operations:

public class DBActivities {
    private static final String LOG_TAG = "SMSCounterActivity";

    private static final String DATABASE_NAME = "sms_details_database";
    private static final String DATABASE_TABLE = "sms_details_table";
    private static final String KEY_DATE = "date";
    private static final String KEY_SMS_CURRENT_COUNT = "sms_current_count";
    private static final String CREATE_SMS_DETAILS_TABLE = "CREATE TABLE " + DATABASE_TABLE + "(" + KEY_DATE + " TEXT, " + KEY_SMS_CURRENT_COUNT + " INTEGER);";
    private static final int DATABASE_VERSION = 1;

    private final Context mContext;
    private DatabaseHelper mDbHelper;
    private SQLiteDatabase mDb;

    public DBActivities(Context param_context) {
        this.mContext = param_context;
    }

    // Database helper class for creating and updating database
     private static class DatabaseHelper extends SQLiteOpenHelper {
         DatabaseHelper(Context context) {
             super(context, DATABASE_NAME, null, DATABASE_VERSION);
         }

         @Override
         public void onCreate(SQLiteDatabase db) {
             Log.i(LOG_TAG, "Creating DataBase: " + CREATE_SMS_DETAILS_TABLE);
             db.execSQL(CREATE_SMS_DETAILS_TABLE);
         }

         @Override
         public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
             Log.w(LOG_TAG, "Upgrading database from version " + oldVersion + " to " + newVersion);
         }
     }

     // Create a new DB. If DB already exists, open it !
    public DBActivities open() throws SQLException {
        Log.d(LOG_TAG, "Trying to open DB now. Table query=" + CREATE_SMS_DETAILS_TABLE);
        mDbHelper = new DatabaseHelper(mContext);
        mDb = mDbHelper.getWritableDatabase();
        return this;
    }

    public void close() {
        mDbHelper.close();
    }

    // DB format: date | sms_current_count
    // date = YYYY-MM-DD
    public long insertIntoDB (int db_value_to_insert) {
        ContentValues dbValues = new ContentValues();
        dbValues.put(KEY_DATE, getCurrentDate());
        dbValues.put(KEY_SMS_CURRENT_COUNT, db_value_to_insert);
        return mDb.insert(DATABASE_TABLE, null, dbValues);
    }

    private String getCurrentDate() {
        Calendar calendar = Calendar.getInstance();
        String currentDate = calendar.get(Calendar.YEAR) + "-" + (calendar.get(Calendar.MONTH)+1) + "-" + calendar.get(Calendar.DAY_OF_MONTH);
        Log.d(LOG_TAG, "currentDate=" +currentDate);
        return currentDate;
    }
}

This is the logcat log:

05-09 01:52:33.363: D/SMSCounterActivity(444): Trying to open DB now. Table query=CREATE TABLE sms_details_table(date TEXT, sms_current_count INTEGER);
05-09 01:54:30.472: D/SMSCounterActivity(444): smsCount=1
05-09 01:54:30.482: D/SMSCounterActivity(444): currentDate=2012-5-9
05-09 01:54:30.592: I/Database(444): sqlite returned: error code = 1, msg = table sms_details_table has no column named sms_current_count
05-09 01:54:30.612: E/Database(444): Error inserting sms_current_count=1 date=2012-5-9
05-09 01:54:30.612: E/Database(444): android.database.sqlite.SQLiteException: table sms_details_table has no column named sms_current_count: , while compiling: INSERT INTO sms_details_table(sms_current_count, date) VALUES(?, ?);
05-09 01:54:30.612: E/Database(444):    at android.database.sqlite.SQLiteCompiledSql.native_compile(Native Method)
05-09 01:54:30.612: E/Database(444):    at android.database.sqlite.SQLiteCompiledSql.compile(SQLiteCompiledSql.java:91)
05-09 01:54:30.612: E/Database(444):    at android.database.sqlite.SQLiteCompiledSql.<init>(SQLiteCompiledSql.java:64)
05-09 01:54:30.612: E/Database(444):    at android.database.sqlite.SQLiteProgram.<init>(SQLiteProgram.java:80)
05-09 01:54:30.612: E/Database(444):    at android.database.sqlite.SQLiteStatement.<init>(SQLiteStatement.java:36)
05-09 01:54:30.612: E/Database(444):    at android.database.sqlite.SQLiteDatabase.compileStatement(SQLiteDatabase.java:1145)
05-09 01:54:30.612: E/Database(444):    at android.database.sqlite.SQLiteDatabase.insertWithOnConflict(SQLiteDatabase.java:1536)
05-09 01:54:30.612: E/Database(444):    at android.database.sqlite.SQLiteDatabase.insert(SQLiteDatabase.java:1410)
05-09 01:54:30.612: E/Database(444):    at com.android.main.DBActivities.insertIntoDB(DBActivities.java:66)
05-09 01:54:30.612: E/Database(444):    at com.android.main.SMSCounterActivity$1.onReceive(SMSCounterActivity.java:52)
05-09 01:54:30.612: E/Database(444):    at android.app.ActivityThread$PackageInfo$ReceiverDispatcher$Args.run(ActivityThread.java:892)
05-09 01:54:30.612: E/Database(444):    at android.os.Handler.handleCallback(Handler.java:587)
05-09 01:54:30.612: E/Database(444):    at android.os.Handler.dispatchMessage(Handler.java:92)
05-09 01:54:30.612: E/Database(444):    at android.os.Looper.loop(Looper.java:123)
05-09 01:54:30.612: E/Database(444):    at android.app.ActivityThread.main(ActivityThread.java:4627)
05-09 01:54:30.612: E/Database(444):    at java.lang.reflect.Method.invokeNative(Native Method)
05-09 01:54:30.612: E/Database(444):    at java.lang.reflect.Method.invoke(Method.java:521)
05-09 01:54:30.612: E/Database(444):    at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:868)
05-09 01:54:30.612: E/Database(444):    at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:626)
05-09 01:54:30.612: E/Database(444):    at dalvik.system.NativeStart.main(Native Method)

I am not able to figure out why the DB insertion is failing. logcat shows that that the table creation query is normal. Still sms_current_count column is missing in the table.

Answer 1

Usually when your create script is fine and there's a column that doesn't exist in the db it's because you changed the schema without incrementing the db version. If this is the case, the only thing wrong with your CREATE script is that it has not been run yet