androidnerd
androidnerd

Reputation: 209

Passing a string and use it in cursor query

My main activity passes a string to another activity (let's just call it sub activity). In this sub activity, I need to use that string in a cursor raw query to select data from the database that matches the string.

public class RestaurantsInfo extends ListActivity {

static final String restaurantListTable = "RestaurantList";
static final String colRestaurantID = "RestaurantID";
static final String colRestaurantName = "RestaurantName";
static final String colRestaurantStore = "StoreNo";
static final String colRestaurantAddress = "StoreAddress";
public String strRestaurantName;
RestaurantDB db = null;

@Override
public void onCreate(Bundle savedInstanceState) {
    super.onCreate(savedInstanceState);
    setContentView(R.layout.activity_listview);
    db=new RestaurantDB(RestaurantsInfo.this);

    Intent intent = getIntent();
    Bundle extras = intent.getExtras();

    String strRestaurantName = extras.getString("RestaurantName");
    setTitle(strRestaurantName);
    getData();
  }

private Cursor doQuery(String strRestaurantName) {
    return(db.getReadableDatabase().rawQuery("SELECT "+colRestaurantID+" AS _id, "+colRestaurantName+", "+colRestaurantStore+", "+colRestaurantAddress+
            " FROM "+restaurantListTable+" WHERE "+colRestaurantName+" = strRestaurantName ORDER BY "+colRestaurantStore+"", null));
    }


public void getData() {
    SimpleCursorAdapter adapter;

    if (Build.VERSION.SDK_INT>=Build.VERSION_CODES.HONEYCOMB) {
        adapter=new SimpleCursorAdapter(this, R.layout.activity_address,
                doQuery(strRestaurantName), new String[] {
            RestaurantDB.colRestaurantName,
            RestaurantDB.colRestaurantStore,
            RestaurantDB.colRestaurantAddress },
            new int[] { R.id.textView1, R.id.textView2, R.id.textView3 },
            0);
        }
    else {
        adapter=new SimpleCursorAdapter(this, R.layout.activity_address,
                doQuery(strRestaurantName), new String[] {
            RestaurantDB.colRestaurantName,
            RestaurantDB.colRestaurantStore,
            RestaurantDB.colRestaurantAddress },
            new int[] { R.id.textView1, R.id.textView2, R.id.textView3 });
        }

    setListAdapter(adapter);

    }
}

As you can see from the codes, I try to pass the string strRestaurantName and use it in WHERE in the query, but it just doesn't work! The log file says "no such column as strRestaurantName" but I don't mean it as a column. I tried doQuery() instead of doQuery(strRestaurantName), but it gave the same error too. What should I do?

Upvotes: 0

Views: 1298

Answers (3)

Sam
Sam

Reputation: 86948

You must wrap SQL Strings in quotes:

"WHERE "+colRestaurantName+" = '" + strRestaurantName + "'"

(I had this backwards at one point, sorry).


Though really you should use parametrization to prevent SQL injection attacks. As in:

return(db.getReadableDatabase().rawQuery(
    "SELECT "+colRestaurantID+" AS _id, "+colRestaurantName+", "+colRestaurantStore+", "+colRestaurantAddress+
    " FROM "+restaurantListTable+
    " WHERE "+colRestaurantStore+" = ?" + 
    " ORDER BY "+colRestaurantStore, 
    new String[] {strRestaurantName}));

It seems that my private doQuery() can't retrieve the value of strRestaurantName when the intent and bundles are placed inside onCreate().

You have accidentally created two variables named strRestaurantName... You have the field variable and a local variable in onCreate(). Change this line:

String strRestaurantName = extras.getString("RestaurantName");

To this:

strRestaurantName = extras.getString("RestaurantName");

Now you will only have one strRestaurantName and it will have the appropriate value in doQuery().

Upvotes: 1

Hardik Thaker
Hardik Thaker

Reputation: 3078

This is not a valid SQL Query in your method You can try this :

private Cursor doQuery(String strRestaurantName) {
    return(db.getReadableDatabase().rawQuery("SELECT "+colRestaurantID+" AS _id, "+colRestaurantName+", "+colRestaurantStore+", "+colRestaurantAddress+
            " FROM "+restaurantListTable+" WHERE "+colRestaurantName+" = "+ strRestaurantName +" ORDER BY "+colRestaurantStore+"", null));
    }

Upvotes: 0

nandeesh
nandeesh

Reputation: 24820

You are not appending the variable, instead you are just appending string Donot put quotes around the variable name strRestaurantName

+" WHERE "+colRestaurantName+" = " +strRestaurantName+ "ORDER BY "+colRestaurantStore+""

Upvotes: 0

Related Questions