dimanche 14 juin 2015

Android unable to write datetime to sqllite database

I'm using the Google Tasks API in my app. One of the fields in my app "due date" requires a DateTime object. Im using the Android datepicker dailog and an EditText view to capture the due date and then converting the user input into the datetime format. When I try to write to my SQLite database I get the following exception.

Error inserting due=2015-06-14T15:58:38.572-04:00 title=Test app _id=TaskId0.07429873487580996 status=needsAction notes=Test write
    android.database.sqlite.SQLiteException: table tasks has no column named due (code 1): , while compiling: INSERT INTO tasks(due,title,_id,status,notes) VALUES (?,?,?,?,?)

which is confusing because I do define a column named "due". Further up in the logs there is another exception.

06-14 15:58:38.607  15395-15395/com.github.idclark.forgetmenot E/EDITFRAGMENT﹕ null
    java.text.ParseException: Unparseable date: "06/14/15" (at offset 8)
            at java.text.DateFormat.parse(DateFormat.java:579)
            at com.github.idclark.forgetmenot.EditFragment.getTaskDueDate(EditFragment.java:64)

The Schema is defined as

public static final class TaskEntry implements BaseColumns {

        public static final String TABLE_NAME = "tasks";
        public static final String COLUMN_TASK_ID = "_id";
        public static final String COLUMN_TASK_TITLE = "title";
        public static final String COLUMN_TASK_UPDATED = "updated";
        public static final String COLUMN_TASK_SELFLINK = "selfLink";
        public static final String COLUMN_TASK_PARENT = "parent";
        public static final String COLUMN_TASK_POSITION = "position";
        public static final String COLUMN_TASK_NOTES = "notes";
        public static final String COLUMN_TASK_STATUS = "status";
        public static final String COLUMN_TASK_DUE = "due";
        public static final String COLUMN_TASK_COMPLETED = "completed";
        public static final String COLUMN_TASK_DELETED = "deleted";
        public static final String COLUMN_TASK_HIDDEN = "hidden";

so the "due" column does exist. And the table is created by

@Override
    public void onCreate(SQLiteDatabase db) {

        //status must be either needsAction or completed

        final String CREATE_TASK_TABLE =
                "CREATE TABLE " + TaskEntry.TABLE_NAME + " (" +
                        TaskEntry._ID + " INTEGER PRIMARY KEY," +
                        TaskEntry.COLUMN_TASK_ID + "TEXT NOT NULL, " +
                        TaskEntry.COLUMN_TASK_TITLE + "TEXT NOT NULL, " +
                        TaskEntry.COLUMN_TASK_COMPLETED + "TEXT, " +
                        TaskEntry.COLUMN_TASK_NOTES + "TEXT, " +
                        TaskEntry.COLUMN_TASK_STATUS + "TEXT NOT NULL, " +
                        TaskEntry.COLUMN_TASK_DUE + "DATETIME, " +
                        TaskEntry.COLUMN_TASK_UPDATED + "DATETIME, " +
                        TaskEntry.COLUMN_TASK_PARENT + "TEXT, " +
                        TaskEntry.COLUMN_TASK_DELETED + "BOOLEAN, " +
                        TaskEntry.COLUMN_TASK_SELFLINK + "TEXT, " +
                        TaskEntry.COLUMN_TASK_POSITION + "TEXT, " +
                        TaskEntry.COLUMN_TASK_HIDDEN + "TEXT" + ")";

        db.execSQL(CREATE_TASK_TABLE);
    }

The due date conversion takes place in this method.

public DateTime getTaskDueDate() {
        mDueDate = (EditText) getActivity().findViewById(R.id.task_due_date);

        Date date = new Date();
        DateTime googleDate = new DateTime(date);
        try {
            SimpleDateFormat sdf = new SimpleDateFormat();
            Date taskDueDate = sdf.parse(mDueDate.getText().toString());
            googleDate = new DateTime(taskDueDate);
        } catch (ParseException ex) {
            Logger.getLogger("EDITFRAGMENT").log(Level.SEVERE, null, ex);
        } finally {

        }
        return googleDate;
    }

The data is then finally written to the database with

public boolean insertRow(Task task) {
        ContentValues values = new ContentValues();
        values.put(TaskContract.TaskEntry.COLUMN_TASK_STATUS, task.getStatus());
        values.put(TaskContract.TaskEntry.COLUMN_TASK_ID,task.getId());
        values.put(TaskContract.TaskEntry.COLUMN_TASK_TITLE,task.getTitle());
        values.put(TaskContract.TaskEntry.COLUMN_TASK_DUE, task.getDue().toString());
        values.put(TaskContract.TaskEntry.COLUMN_TASK_NOTES, task.getNotes());

        SQLiteDatabase db = this.getWritableDatabase();
        boolean createSuccessful = db.insert(TaskContract.TaskEntry.TABLE_NAME, null, values) > 0;
        db.close();
        return createSuccessful;
    }

I'm confused as to why this write fails, and sql claims that there is no "due" column. Even though there is a parse exception, I see a timestamp in the logs as well. Is part of the problem that i'm calling .toString() on the datetime object before writing it to the db? I don't have a lot of sql experience and am genuinely confused as to what to make of these exceptions.

Aucun commentaire:

Enregistrer un commentaire