0

I am writing an Android app that needs to execute an UPDATE query on a SQLite database, but for some reason, the query is not having any effect when I run it.

Here's the function that is supposed to execute the query.

public void query()
{
    try
    {
        cursor = this.db.rawQuery("UPDATE data SET saved=1 WHERE number=1", null);
    }
    catch(SQLiteException e)
    {
        System.out.println("Database Query Failed: " + e.getCause().getMessage());
    }
}

Although the query does not work, I do not get any output from the catch clause, and adding an additional catch to check for Exception does not output anything either.

What's the problem? How can I get UPDATE queries to work? I have not tried executing an INSERT query, but I will need to do that as well later on. Will I have similar issues with all queries that write to the database?

DaveTheMinion
  • 664
  • 3
  • 22
  • 45
  • 1
    [SQLiteDatabase](https://developer.android.com/reference/android/database/sqlite/SQLiteDatabase.html) has methods for insert, update, delete, etc. I suggest you explore the javadoc for that class. – Karakuri Aug 30 '15 at 18:42

2 Answers2

2

Use execSQL() and not rawQuery() to run the SQL.

rawQuery() just compiles the SQL but does not run it. execSQL() both compiles and runs the SQL.

laalto
  • 150,114
  • 66
  • 286
  • 303
  • Thanks! Can I use `execSQL()` for `SELECT` queries as well, or do I still need to use `rawQuery()` for that? – DaveTheMinion Aug 30 '15 at 18:40
  • "rawQuery() just compiles the SQL but does not run it." -- could you provide a reference for this? Official documentation says that it runs the query. However, it's undocumented what happens when you submit a query that returns no cursor. – 323go Aug 30 '15 at 18:40
  • @DavidB For `SELECT`s use `rawQuery()`. When you move the returned `Cursor` the SQL program is run until next result row. – laalto Aug 30 '15 at 18:43
  • @laalto Okay. Just wanted to see if I could use just one or another. – DaveTheMinion Aug 30 '15 at 18:46
  • @323go It's basically the sqlite C API design - http://stackoverflow.com/questions/20110274/what-is-the-correct-way-to-do-inserts-updates-deletes-in-android-sqlitedatabase/20118910#20118910 – laalto Aug 30 '15 at 18:50
1

rawQuery() is to return a cursor. An UPDATE statement does not return a cursor. Use execSQL() instead:

public void query() {
    try {
        this.db.execSQL("UPDATE data SET saved=1 WHERE number=1", null);
    } catch(SQLiteException e) {
        System.out.println("Database Query Failed: " + e.getCause().getMessage());
    }
}

It would be wiser to use parameter substitution, instead of including your parameters in the query string, as follows:

public void query( int savedState, int number ) {
    try {
        Object[] params = new Object[]{ new Integer(savedState), new Integer(number) };

        this.db.execSQL("UPDATE data SET saved=? WHERE number=?", params );
    } catch(SQLiteException e) {
        System.out.println("Database Query Failed: " + e.getCause().getMessage());
    }
}
323go
  • 14,143
  • 6
  • 33
  • 41