Simple Android Database Query problem

Question

Hi everyone,

I'm new to StackOverflow but so far I love this site. I'm writing an Android application for RC cars. It uses an SQLite Database, and saves the model of the car (carname) as well as other variables.

I want to be able to display only the records that have a certain model of car named in the carname column. So far I can only filter the results by using "'MTX-4'" or some other literal string. I need to be able to replace that with a string variable like String carName or something, so I can filter by a user-defined carname.

Here's my code:

public Cursor fetchAllNotes() {

        return mDb.query(DATABASE_TABLE, new String[] {KEY_ROWID, KEY_TITLE,
                KEY_CARNAME, KEY_TRACKNAME, KEY_TRACKSIZE, KEY_TRACKTRACTION, KEY_TRACKSURFACE, KEY_BODYTYPE, KEY_COMMENTS}, KEY_CARNAME + "=" + "'MTX-4'", null, null, null, null);
    }

Any help and comments warmly appreciated.

Cheers!

Answer 1

Replace the 'MTX-4' with "?" as a parameter variable. Then replace the selectionArgs parameter (null in your example) with your list of parameters, e.g. new String[]{"MTX-4"} or new String[]{carName}

The following snippet is equivalent to yours.

return mDb.query(DATABASE_TABLE, new String[] {KEY_ROWID, KEY_TITLE,
    KEY_CARNAME, KEY_TRACKNAME, KEY_TRACKSIZE, KEY_TRACKTRACTION, KEY_TRACKSURFACE, KEY_BODYTYPE, KEY_COMMENTS}, KEY_CARNAME + "=" + "?", new String[]{"MTX-4"}, null, null, null);

Answer 2

I can't seem to get my query to work using the selectionArgs Array.

I'm not quite sure what to do next, or how to get this to work.

Please take a look at the function below, and let me know if anything obvious can be done.

Thanks!

protected Cursor fetchFilteredEntries(String strName, String strDescr, String strDate, String strAmount, String strType, int intSortOption) throws SQLException {

    int intSelectionArgsCounter = 0;

    String strEntriesFilter = null;
    String[] strEntriesSelArgs = new String[10];

    for (int i = 0; i < 10; i++){
      strEntriesSelArgs[i] = "";
    }

    strEntriesFilter = KEY_NAME + " = '" + strName + "'";

    String strOrderBy = "";
    strOrderBy = OrderBySortOpts(intSortOption);


    if (!strDescr.equals("")){
      strEntriesFilter = strEntriesFilter + " AND " + KEY_NAME + " LIKE ?";
      strEntriesSelArgs[intSelectionArgsCounter] = "%" + strDescr + "%";
      intSelectionArgsCounter = intSelectionArgsCounter + 1;
      }//end if (strReportType.equals("PAYEE"))
    }

    if (!strDate.equals("")){
      strEntriesFilter = strEntriesFilter + " AND " + KEY_DATE + " LIKE ?";
      strEntriesSelArgs[intSelectionArgsCounter] = "%" + strDate + "%";
      intSelectionArgsCounter = intSelectionArgsCounter + 1;
    }
    }//end if (!strDate.equals("")){

    if (!strAmount.equals("")){
      strEntriesFilter = strEntriesFilter + " AND " + KEY_AMT + " LIKE ?";
      strEntriesSelArgs[intSelectionArgsCounter] = "%" + strAmount + "%";
      intSelectionArgsCounter = intSelectionArgsCounter + 1;
    }

    if (!strType.equals("") && !strType.equals("No Filter")){
      strEntriesFilter = strEntriesFilter + " AND " + KEY_TYPE + " = ?";
      strEntriesSelArgs[intSelectionArgsCounter] = strType;
    }

    return mDb.query(MY_DATABASE_TABLE, new String[] {KEY_ROWID, KEY_NAME, KEY_DATE, KEY_AMT, KEY_TYPE}, 
        strEntriesFilter, strEntriesSelArgs, null, null, strOrderBy);
}// end fetchFilteredEntries

Answer 3

Sorry, I didn't state what error or issue I was having.

I am getting an SQLiteException error, 'Bind or column index out of range'.

This usually has happened to me when trying to use selectionArgs with a single string in the past, where I had created a WHERE clause similar to the following:

"WHERE KEYNAME = '?'"

or

"WHERE KEYNAME LIKE '%?%'"

What I did in the past to get around this was to do the following WHERE clause,

"WHERE KEYNAME = ?"

or

"WHERE KEYNAME LIKE ?"

and then in my query, have

mDb.query(MY_DATABASE_TABLE, new String[] {KEY_ROWID, KEY_NAME, KEY_DATE, KEY_AMT, KEY_TYPE}, 
        strEntriesFilter, new String[]{strSelArgs}, null, null, strOrderBy);

or

mDb.query(MY_DATABASE_TABLE, new String[] {KEY_ROWID, KEY_NAME, KEY_DATE, KEY_AMT, KEY_TYPE}, 
        strEntriesFilter, new String[]{%strSelArgs%}, null, null, strOrderBy);

This work around was effective for when I only had one bind variable to deal with in my query.

With the previously posted code, in my previous post, I am dealing with multiple bind variables, and build the selectionArgs array with multiple elements, and it's causing the SQLiteException error to be thrown.