CODEWITHSUNDEEP
javaandroiddatabasesqliteprepared-statement
Daan Seuntjens
Daan Seuntjens

Reputation: 960

How to use prepared statement for sql?

I'm trying to have an SQLite database in android but I have a problem with that:

I'm trying to update the text value in the "response" column with id 0. The first problem I had was that the string I was using for the update used an apostrophe (') and it had syntax errors because sql closes the string with an '. So I now am using a prepared sql statement for that. The problem now is that the long that is returning gives a -1, so that means that no rows were effected. So how can I update my current string to the row with id=0?

Note: the first string also has an ' but was added using the addData funtion and it didn't give any errors just using db.insert, is that the problem, should I replace all my code with prepared statements?

public boolean addData(String item) {
    SQLiteDatabase db = this.getWritableDatabase();
    ContentValues contentValues = new ContentValues();
    contentValues.put(COL2, item);

    Log.d(TAG, "addData: Adding " + item + " to " + TABLE_NAME);
    long result = db.insert(TABLE_NAME, null, contentValues);

    //if date as inserted incorrectly it will return -1
    if (result == -1) {
        return false;
    } else {
        return true;
    }
}

public long updateData(String newName){
    SQLiteDatabase db = this.getWritableDatabase();

    String sql = "UPDATE json_response SET response=? WHERE ID='0'";
    SQLiteStatement statement = db.compileStatement(sql);

    statement.bindString(1, newName);  // matches second '?' in sql string
    long rowId = statement.executeInsert();
    return rowId;
}

Upvotes: 1

Views: 226

Answers (2)

varro
varro

Reputation: 2482

The problem is, I think, that WHERE ID='0' will always fail; what you want is WHERE ID=0

Upvotes: 1

Logan
Logan

Reputation: 92

I have not used prepared statements much so I can't say why that is not working, but why not use the db.update() method? It takes ContentValues as an argument similar to tour addData() method. Give this a shot.

public int updateData(String newName){
    SQLiteDatabase db = this.getWritableDatabase();

    ContentValues cv = new ContentValues();
    cv.put("json_response",newName);

    int rows = db.update(TABLE_NAME, cv, "ID=0", null);
    return rows;
}

[EDIT] update() returns an integer representing the number of rows affected instead of which row was affected. Keep that in mind as your variable name rowId implies that is not what you are looking for.

[EDIT 2] And no, there is no problem with the addData() method that I can see. The apostrophe that was added did not cause an error because ContentValues parameterizes the string values before adding them into the database. Basically, all SQL-like syntax will be ignored when inserting values, which is great for security reasons.

Upvotes: 2

Related Questions