Update SQLite Database

Question

I have a SQLite database set up from this tutorial changing it around to my own project: http://www.androidhive.info/2011/11/android-sqlite-database-tutorial/

However the tutorial does not mention how to update a field.. I have tried this:

db = new NotesDatabaseHandler(ViewNoteActivity.this);
...
db.updateNote(new Note(identifier, editTextTitle.getText().toString(), editTextContent.getText().toString(), "Updated on: " + printStandardDate()));

Although have had no luck.. I run the application and the note will not update.

Note class:

package com.timmo.notes;

class Note {

//private variables
private int _id;
private String _title;
private String _content;
private String _metadata;

// Empty constructor
public Note() {

}

// constructor
public Note(int id, String title, String content, String metadata) {
    this._id = id;
    this._title = title;
    this._content = content;
    this._metadata = metadata;
}

public int getID() {
    return this._id;
}

public void setID(int id) {
    this._id = id;
}

public String getTitle() {
    return this._title;
}

public void setTitle(String title) {
    this._title = title;
}

public String getContent() {
    return this._content;
}

public void setContent(String content) {
    this._content = content;
}

public String getMetadata() {
    return this._metadata;
}

public void setMetadata(String metadata) {
    this._metadata = metadata;
}

}

From NotesDatabaseHandler():

// Updating single note
public int updateNote(Note note) {
    SQLiteDatabase db = this.getWritableDatabase();

    ContentValues values = new ContentValues();
    values.put(KEY_TITLE, note.getTitle());
    values.put(KEY_CONTENT, note.getContent());
    values.put(KEY_METADATA, note.getMetadata());

    // updating row
    int ret = db.update(TABLE_NOTES, values, KEY_ID + " = ?",
            new String[]{String.valueOf(note.getID())});
    db.close();
    return ret;
}

Previously, I have deleted the note, then added it in again, but this adds it to the end of the database instead of in the same position.

So, to the question, how do I properly update one of my notes (contacts) from the method I am using? Thanks

Update: I've tried using a raw UPDATE:

public void updateNote(Note note) {
    SQLiteDatabase db = this.getWritableDatabase();

    String strSQL = "UPDATE "
            + TABLE_NOTES + " SET "
            + KEY_TITLE + "='" + note.getTitle() + "', "
            + KEY_CONTENT + "='" + note.getContent() + "', "
            + KEY_METADATA + "='" + note.getMetadata() + "'"
            + " WHERE " + KEY_ID + "='" + note.getID() + "'";
    Log.d("SQL: ", strSQL);
    db.execSQL(strSQL);
    db.close();
}

Yet still nothing happens. My Log outputs:

UPDATE notes SET title='hello', content='there', metadata='Updated on: 09:48 AM - 08 Aug 2015' WHERE id='7'

I can't see what's wrong...

Answer 1

What do you want to update? To update, you need to pass valid (=existing) row id (primary key)

• read (get id)
• edit
• update (with old id)

Keep in mind

on conflict

You can try also use raw:

String strSQL = "UPDATE myTable SET Column1 = someValue WHERE columnId = "+ someValue;
myDataBase.execSQL(strSQL);

Or try:

String where = "_id=" + note.getId(); 
myDataBase.update(table,cv,where,null) ;

I've tried raw and yet nothing actually happens.. "UPDATE notes SET title='hello fgwejfneqdfjoe', content='there', metadata='Updated on: 06:48 AM - 08 Aug 2015' WHERE id='7'" which look like it should be updating..

get rid of Apostrophe from your sql statement

If you are using SQL strings in your database operations, chances are you have come across problem with strings in SQL statement. One apostrophe screws up the SQL string, causing the SQL statement to fail.

Building an SQL statement by concatenating strings is not advisable. It is:

• unscalable -- The database will "hard parse" for every execution of the SQL -
• prone to failure for every last name input like O'Connor (or similar surname), possessive, or contracted input.
• vulnerable to SQL injection by the user if input parameter values are used as-is.

How do I escape single quotes in SQL queries?

Answer 2

I managed to fix my problems. Kudos to @Tomasz Best for the pointers.

Fixed updateNote:

public void updateNote(Note note) {
    SQLiteDatabase db = this.getWritableDatabase();

    ContentValues values = new ContentValues();
    values.put(KEY_TITLE, note.getTitle());
    values.put(KEY_CONTENT, note.getContent());
    values.put(KEY_METADATA, note.getMetadata());

    db.update(TABLE_NOTES, values, KEY_ID + " = ?",
            new String[]{String.valueOf(note.getID())});

    db.close();
}

The calling statement:

db.updateNote(new Note(identifier, editTextTitle.getText().toString(), editTextContent.getText().toString(), "Updated on: " + printStandardDate()));

These, along with other fixes an issues that arose when trying to update RecyclerView items which I eventually fixed or created workarounds. The app now works flawlessly and I have published an update to the store: https://play.google.com/store/apps/details?id=com.timmo.notes

I should publish the source to github soon and add a link to the description.

Thanks again to everyone who helped!

Answer 3

s possible to recall update method only if you execute a select before to refresh value, something like this i think:

    @Override
    public void onCreate(Bundle savedInstanceState)
    {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.main);

        DatabaseHandler db = new DatabaseHandler(this);

        /**
         * CRUD Operations
         * */
        // Inserting Contacts
        Log.d("Insert: ", "Inserting .."); 
        db.addContact(new Contact("Ravi", "9100000000"));        
        db.addContact(new Contact("Srinivas", "9199999999"));
        db.addContact(new Contact("Tommy", "9522222222"));
        db.addContact(new Contact("Karthik", "9533333333"));

        // Reading all contacts
        Log.d("Reading: ", "Reading all contacts.."); 
        List contacts = db.getAllContacts();       

        for (Contact cn : contacts)
        {
            Log.d("TEST", "Id: "+cn.getID()+" ,Name: " + cn.getName() + " ,Phone: " + cn.getPhoneNumber());

            if ("Tommy".equals(cn.getName())
            {
                cn.setName("Timmo");
                db.updateContact(cn); //like your updateNote
            }
        }

        Log.d("Reading: ", "Reeeeload all contacts.."); 
        contacts = db.getAllContacts();       

        for (Contact cn : contacts)
        {
            if ("Timmo".equals(cn.getName())
            {
                Log.d("TEST", " Hello Timmo");
            }
        }   
    }