CODEWITHSUNDEEP
androidsqlitenulllong-integer
AJW
AJW

Reputation: 1649

SQLite: store an empty value in a column type of long?

I have an app with a RecyclerView list of CardViews. A CardView is created when a user enters some data and the data is then saved in an SQLite database. When a user enters a date with a Datepicker for a single CardView, the SQLite column "COLUMN_NOTIFTIME" saves the system time (System.currentTimeMillis() + 2 hours) using a long "notiftime" from the model class. I use the "notiftime" to fire a Notification for the user. If the user does not enter a date for a CardView, I would like the "COLUMN_NOTIFTIME" to hold a a null value or some other indicator to show that there is no data. I understand a long can't be null. So how do I save some type of value or indicator to show that "notiftime" is empty for the case where the user does not enter any date for that CardView? Or should I just change the long "notiftime" to a String and format the System time + 2 hours to a String so that I can use NULL, isEmpty(), isNull(), and putNull()?

Model class:    
public class CardViewItem {      

    private int id; 
    private String duedate;
    private String duetime;
    private long notiftime;
}

DB class:    
public class SQLiteDB extends SQLiteOpenHelper {

    ...
    private static final String SQL_CREATE_ENTRIES =
    "CREATE TABLE IF NOT EXISTS " + TABLE_NAME + "(" +

        COLUMN_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
        COLUMN_DUEDATE + " TEXT," +
        COLUMN_DUETIME + " TEXT," +
        COLUMN_NOTIFTIME + " INTEGER" + ")";    
}

Upvotes: 0

Views: 1130

Answers (1)

MikeT
MikeT

Reputation: 56948

You could store any value you wished due to SQLite's flexibitlity (with one exception that of an alias of the rowid, which you ID column is, you can store any type of value in any type of column).

You could store null by not specifying a value for the column (as long as it isn't defined with the NOT NULL constraint) which would require specifying the columns to be inserted.

So in your case the SQL used for an insert would have to equate to :-

INSERT INTO your_table (due_date_column_name,due_time_column_name) VALUES('2018-01-01','10:30');

Using the insert method this would be along the lines of :-

ContentValues cv = new ContentValues();
cv.put(COLUMN_DUEDATE,"2018-01-01");
cv.put(COLUMN_DUETIME,"10:00");
long inserted_id = db.insert(TABLE_NAME,null,cv);

Instead of :-

ContentValues cv = new ContentValues();
cv.put(COLUMN_DUEDATE,"2018-01-01");
cv.put(COLUMN_DUETIME,"10:00");
cv.putCOLUMN_NOTIFTIME,calculated_notiftime_as_long);
long inserted_id = db.insert(TABLE_NAME,null,cv);

However the simplest way would be to store a long that would not be logically valid and therefore easily detectable, the same date time ( so due date + due_time - notiftime = 0), 0 would probably be the easiest, -1 is also often used to indicate nothing (generally when 0 could be valid).

Upvotes: 2

Related Questions