mercredi 27 janvier 2016

SQLite: Geeting value 0 from a select when insert works correct

I have a Table Notes (_Id, Title, Body, Category) and a Table Categories (_Id, Name), referencing Category to Categories._Id.

Whit this method I create a Note in my SQLite Database.

/**
 * Create a new note using the title and body provided, and associated to
 * the category with row id provided. If the note is successfully created
 * return the new rowId for that note, otherwise return  a -1 to indicate failure.
 *
 * @param title the title of the note
 * @param body the body of the note
 * @param category the category associated to the note
 * @return rowId or -1 if failed
 */
public long createNote(String title, String body, String category) {
    if (title == null || title.equals("") || body == null) {
        return -1;
    }
    else {
        ContentValues initialValues = new ContentValues();
        initialValues.put(NOTE_KEY_TITLE, title);
        initialValues.put(NOTE_KEY_BODY, body);
        // If it has associated a category
        if (!category.equals("No Category")) {
            Cursor idCategory = fetchCategory(category);
            long catId = idCategory.getLong(idCategory.getColumnIndex("_id"));
            initialValues.put(NOTE_KEY_CAT, catId);
        // Else, it has no category
        } else {
            initialValues.put(NOTE_KEY_CAT, (Byte) null);
        }
        return mDb.insert(DATABASE_NOTE_TABLE, null, initialValues);
    }
}

And whit this another I get all the notes of my database.

/**
 * Return a Cursor over the list of all notes in the database
 *
 * @param mode - if TITLE, it returns the list ordered by the title of the notes.
 *             - if CATEGORY, it returns the list ordered by the category of the notes.
 *             - it returns null in another case.
 * @param category - category of the notes to return (No Category if none)
 * @return Cursor over all notes
 */
public Cursor fetchAllNotes(String mode, String category) {
    //// Order
    String order;
    // Mode is Title
    if (mode.equals("TITLE")) {
        order = NOTE_KEY_TITLE;
    // Mode is Categories
    } else if (mode.equals("CATEGORY")) {
        order = CAT_KEY_NAME;
    // A forbidden mode
    } else {
        return null;
    }
    // No category filter if it is No Category
    String cat;
    if (category.equals("No Category")) {
        cat = "";
    } else {
        cat = " WHERE " + CAT_KEY_NAME + "='" + category + "'";
    }                                     // Left outer because there are notes without category
    String query = "SELECT * FROM " + DATABASE_NOTE_TABLE + " LEFT OUTER JOIN " + DATABASE_CAT_TABLE +
            " C ON " + NOTE_KEY_CAT + "=C." + CAT_KEY_ROWID + cat + " ORDER BY " + order;
    return mDb.rawQuery(query, null);
}

When I insert a new Note, createNote() returns me the correct rowId. When I realize a fetchAllNotes() operation, two things happens: when a Note has a category, it returns the correct note with its proper attributes. When another Note has not Category (value No Category), the note with the desired attributes is returned, but not its identifier (a 0 value is returned as _id).

Any idea of what is happening?

Aucun commentaire:

Enregistrer un commentaire