Error Inserting TimeStamp column in SQLite Database

Question

I am trying to add TimeStamp field in my SQLite Table, see my code below:

myDBClass.java

   @Override
public void onCreate(SQLiteDatabase db) {
    // TODO Auto-generated method stub
    // Create Table Name
    db.execSQL("CREATE TABLE " + TABLE_MEMBER + 
              "(OperatorID INTEGER PRIMARY KEY AUTOINCREMENT," +
              " DeviceID TEXT(100)," +
              " EmailID TEXT(100)," +
              " Operator TEXT(100)," +
              " Created_at DATETIME DEFAULT CURRENT_TIMESTAMP);");

    Log.d("CREATE TABLE","Create Table Successfully.");
}

// Insert Data
public long InsertData(String strDeviceID, String strEmailID, String strEventOperator, String strCreated_at) {
    // TODO Auto-generated method stub

     try {
        SQLiteDatabase db;
        db = this.getWritableDatabase(); // Write Data

        ContentValues Val = new ContentValues();
        Val.put("DeviceID", strDeviceID);
        Val.put("EmailID", strEmailID);
        Val.put("Operator", strEventOperator);
        Val.put("Created_at", getDateTime());

        long rows = db.insert(TABLE_MEMBER, null, Val);

        db.close();
        return rows; // return rows inserted.

     } catch (Exception e) {
        return -1;
     }

}

private String getDateTime() {
    SimpleDateFormat dateFormat = new SimpleDateFormat(
            "yyyy-MM-dd HH:mm:ss", Locale.getDefault());
    Date date = new Date();
    return dateFormat.format(date);
  }


// Select Data
public String[] SelectData(String strOperatorID) {
    // TODO Auto-generated method stub

     try {
        String arrData[] = null;    

         SQLiteDatabase db;
         db = this.getReadableDatabase(); // Read Data

         Cursor cursor = db.query(TABLE_MEMBER, new String[] { "*" }, 
                    "OperatorID=?",
                    new String[] { String.valueOf(strOperatorID) }, null, null, null, null);

            if(cursor != null)
            {
                if (cursor.moveToFirst()) {
                    arrData = new String[cursor.getColumnCount()];
                    /***
                     *  0 = DeviceID
                     *  1 = EmailID
                     *  2 = EventOperator
                     */
                    arrData[0] = cursor.getString(0);
                    arrData[1] = cursor.getString(1);
                    arrData[2] = cursor.getString(2);
                }
            }
            cursor.close();
            db.close();
            return arrData;

     } catch (Exception e) {
        return null;
     }

}

AddActivity.java

public boolean SaveData()
    {       
        final EditText editDeviceID = (EditText) findViewById(R.id.editDeviceID);
        final EditText editEmailID = (EditText) findViewById(R.id.editEmailID);
        final EditText editEveOpe = (EditText) findViewById(R.id.editEveOpe);       
        // Dialog
        final AlertDialog.Builder adb = new AlertDialog.Builder(this);
        AlertDialog ad = adb.create();

        // new Class DB
        final myDBClass myDb = new myDBClass(this);

        // Save Data
        long saveStatus = myDb.InsertData(
                editDeviceID.getText().toString(),
                editEmailID.getText().toString(),
                editEveOpe.getText().toString()
                );
        if(saveStatus <=  0)
        {
            ad.setMessage("Error!! ");
            ad.show();
            return false;
        }

         Toast.makeText(AddActivity.this,"Added Data Successfully. ",
                 Toast.LENGTH_SHORT).show();   

        return true;
    }

}

Log:

12-19 01:39:08.147: E/SQLiteLog(6963): (1) table members has no column named Created_at
12-19 01:39:08.203: E/SQLiteDatabase(6963): Error inserting DeviceID=safdasfewrwesd23432 Created_at=2013-12-19 01:39:08 EventOperator=adsfewvweceEvent EmailID=sdfasdf@desa.cond
12-19 01:39:08.203: E/SQLiteDatabase(6963): android.database.sqlite.SQLiteException: table members has no column named Created_at (code 1): , while compiling: INSERT INTO members(DeviceID,Created_at,EventOperator,EmailID) VALUES (?,?,?,?)
12-19 01:39:08.203: E/SQLiteDatabase(6963):     at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method)
12-19 01:39:08.203: E/SQLiteDatabase(6963):     at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:889)
12-19 01:39:08.203: E/SQLiteDatabase(6963):     at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:500)
12-19 01:39:08.203: E/SQLiteDatabase(6963):     at android.database.sqlite.SQLiteSession.prepare(SQLiteSession.java:588)
12-19 01:39:08.203: E/SQLiteDatabase(6963):     at android.database.sqlite.SQLiteProgram.<init>(SQLiteProgram.java:58)
12-19 01:39:08.203: E/SQLiteDatabase(6963):     at android.database.sqlite.SQLiteStatement.<init>(SQLiteStatement.java:31)
12-19 01:39:08.203: E/SQLiteDatabase(6963):     at android.database.sqlite.SQLiteDatabase.insertWithOnConflict(SQLiteDatabase.java:1467)
12-19 01:39:08.203: E/SQLiteDatabase(6963):     at android.database.sqlite.SQLiteDatabase.insert(SQLiteDatabase.java:1339)
12-19 01:39:08.203: E/SQLiteDatabase(6963):     at com.example.database.myDBClass.InsertData(myDBClass.java:59)
12-19 01:39:08.203: E/SQLiteDatabase(6963):     at com.example.database.AddActivity.SaveData(AddActivity.java:49)
12-19 01:39:08.203: E/SQLiteDatabase(6963):     at com.example.database.AddActivity$1.onClick(AddActivity.java:26)
12-19 01:39:08.203: E/SQLiteDatabase(6963):     at android.view.View.performClick(View.java:4240)
12-19 01:39:08.203: E/SQLiteDatabase(6963):     at android.view.View$PerformClick.run(View.java:17721)
12-19 01:39:08.203: E/SQLiteDatabase(6963):     at android.os.Handler.handleCallback(Handler.java:730)
12-19 01:39:08.203: E/SQLiteDatabase(6963):     at android.os.Handler.dispatchMessage(Handler.java:92)
12-19 01:39:08.203: E/SQLiteDatabase(6963):     at android.os.Looper.loop(Looper.java:137)
12-19 01:39:08.203: E/SQLiteDatabase(6963):     at android.app.ActivityThread.main(ActivityThread.java:5103)
12-19 01:39:08.203: E/SQLiteDatabase(6963):     at java.lang.reflect.Method.invokeNative(Native Method)
12-19 01:39:08.203: E/SQLiteDatabase(6963):     at java.lang.reflect.Method.invoke(Method.java:525)
12-19 01:39:08.203: E/SQLiteDatabase(6963):     at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:737)
12-19 01:39:08.203: E/SQLiteDatabase(6963):     at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:553)
12-19 01:39:08.203: E/SQLiteDatabase(6963):     at dalvik.system.NativeStart.main(Native Method)
12-19 01:39:08.434: D/dalvikvm(6963): GC_FOR_ALLOC freed 138K, 7% free 3169K/3384K, paused 28ms, total 38ms

I am getting table members has no column named Created_at

I am little bit confuse what to use for *Created_at column in Insert Query* *(AddActivity.java)*

Still in my members table, fields are: OperatorID, DeviceID, EmailID, Operator but i am also trying to add Created_at field in members table., but facing problem

Answer 1

When you change your database schema e.g. the CREATE TABLE in code, you'll have to make sure the same changes are also done to the actual database file.

SQLiteOpenHelper will version your database file. It's the version number you pass to SQLiteOpenHelper constructor that tells the current code schema version. When you change your schema, you should increase that version number.

SQLiteOpenHelper will call your onUpgrade() when the schema version in your code is higher than in the database file. You can either call ALTER TABLE and the like to upgrade your database, or just call DROP TABLE on the existing tables and call onCreate() to create them using the up-to-date schema. In this latter case you lose any data in the database.

Another, simpler way: Instead of increasing the schema version number, just delete the old database file. You can do this by clearing app data via settings -> manage applications. Or just uninstall and reinstall the app. Don't rely on this trick for released versions though, only do it for schema updates during development.

Answer 2

Sqlite doesn't support DATETIME type. Instead, you can store your timestamp as Integer(milisecond) or Text.

Answer 3

There is no datatype like DATETIME in sqlite. so just change your code like below:

db.execSQL("CREATE TABLE " + TABLE_MEMBER + 
              "(OperatorID INTEGER PRIMARY KEY AUTOINCREMENT," +
              " DeviceID TEXT(100)," +
              " EmailID TEXT(100)," +
              " Operator TEXT(100)," +
              " Created_at TIMESTAMP NOT NULL DEFAULT current_timestamp);");