SQLite - Bug in database update method

Question

EDIT: [SOLVED]

The update method was getting a wrong argument from its calling method. Hence the output was not as expected. Thanks for pointing out that there might not be any matches for the update statement.

I have various database update methods written and all of them are working fine, except one. I am not able to understand what is going wrong there.

Here is my code:

BaseDbAdapter:

protected int updateRow(String table, ContentValues initialValues,
                        String whereClause) {
    if (Const.DEBUGGING_DB)
        Log.d(Const.DEBUG, "Row in table " + table + " updated "
                + whereClause);
    return mDb.update(table, initialValues, whereClause, null);
}

PetitionsTableDbAdapter:

public void updateStatus(String member_id, String e_pno, String pno, String status) {

    ContentValues values = new ContentValues();
    values.put(DatabaseHelper.SENT_SUPPORT, status);

    String whereClause = DatabaseHelper.MEMBER_ID_KEY + " = " + member_id + " AND " + ""
            + DatabaseHelper.E_PETITION_NUMBER_KEY + " = " + e_pno + " AND " + ""
            + DatabaseHelper.PETITION_NUMBER_KEY + " = '" + pno + "'";

    super.updateRow(DatabaseHelper.PETITIONS_TABLE, values, whereClause);
}

public String getStatus(String petition_number) {

    String status = "";

    String query = "SELECT " + DatabaseHelper.SENT_SUPPORT + " FROM " + DatabaseHelper.PETITIONS_TABLE
            + " WHERE " + DatabaseHelper.PETITION_NUMBER_KEY + " = '" + petition_number + "'";

    c = super.query(query);

    if ((c.equals(null) || c.getCount() == 0 || !c.moveToFirst())) {

    } else {
        c.moveToFirst();

        status = c.getString(c.getColumnIndex(DatabaseHelper.SENT_SUPPORT));
    }

    return status;
}

Code in Service:

mPetitionsTableDbAdapter = DatabaseHelper.get(getApplicationContext()).getPetitionsTableDbAdapter();
                                                mPetitionsTableDbAdapter.beginTransaction();
                                                try {

                                                    mPetitionsTableDbAdapter.updateStatus(member_id, e_petition_no, petition_no, "1");
                                                    mPetitionsTableDbAdapter.setTransactionSuccessful();


                                                } catch (Exception e) {
                                                    e.printStackTrace();
                                                } finally {
                                                    mPetitionsTableDbAdapter.endTransaction();
                                                }

                                                String status = mPetitionsTableDbAdapter.getStatus(petition_no);
                                                Log.d(Const.DEBUG, "Status: " + status);

My Logcat:

Begin Transaction
10-12 18:36:55.104 17865-19023/com.xxx.xx D/xx: Row in table petitions_table updated member_id = 2199683 AND e_petition_number = 31 AND petition_number = '31T8'
10-12 18:36:55.104 17865-19023/com.xxx.xx D/xx: Transaction Successful
10-12 18:36:55.104 17865-19023/com.xxx.xx D/xx: End Transaction
10-12 18:36:55.104 17865-19023/com.xxx.xx D/xx: Query 'SELECT sent_support FROM petitions_table WHERE petition_number = '31T8'' returned 1 rows
10-12 18:36:55.104 17865-19023/com.xxx.xx D/xx: Status: 0

I am trying to set SENT_SUPPORT value to 1, and read it after the transaction. It always returns 0, which is the default value i inserted while creating the row. If you need any other part of the code, let me know.

Note: There is only 1 row for a particular petition_number.

Your update query has much stricter whereClause thatn your select query. It's possible the update matches no rows. — laalto, Oct 12 '15 at 14:23
You were right, the update query was getting a wrong parameter from its caller. Thanks.. — Vamsi Challa, Oct 13 '15 at 09:15

score 1 · Answer 1 · edited May 23 '17 at 11:58

1

You might need to escape characters like ' in your queries

For more information, please check SO question How to escape special characters like ' in sqlite in android

edited May 23 '17 at 11:58

Community

1
1

answered Oct 12 '15 at 13:29

Gökhan Barış Aker

4,445
5
25
35

But, it seems like the query is just fine. Where do you think, i am using wrong 's? – Vamsi Challa Oct 12 '15 at 13:33
@VamsiChalla inside your updateStatus method where clause's last line '" + pno + "'" – Gökhan Barış Aker Oct 12 '15 at 13:37
@VamsiChalla and getStatus query has the same mistake – Gökhan Barış Aker Oct 12 '15 at 13:39
If i don't use the single quote, i get an error saying, no such column xyz, also similar syntax is working in other update methods. – Vamsi Challa Oct 12 '15 at 13:40
Sorry for not being clear enough :). You need to keep using single quotes in your query string, while escaping them with a back slash "\'". – Gökhan Barış Aker Oct 13 '15 at 05:54

score 0 · Accepted Answer · answered Nov 02 '15 at 10:38

0

Just to close the question.

Answer : The update method was getting a wrong argument from its calling method. Hence the output was not as expected. Thanks for pointing out that there might not be any matches for the update statement.

answered Nov 02 '15 at 10:38

Vamsi Challa

11,038
31
99
149

SQLite - Bug in database update method

2 Answers2