database - Android SQLite Column Not Found Error When Executing Raw Query

Question

I am making an android app that searches for restaurants in a SQLite database. I currently use a custom loader (that I found online) to load a cursor from the database and then take that cursor and populate a listview with a SimpleCursorAdapter. I want all the data to be bound but only the name of the restaurant to be displayed (because when I click on a list item I want to get all the data and send it to a new activity that displays the data). If I specify the restaurant name and restaurant type as the search parameters, I run the raw query:

 "SELECT * FROM Restaurant WHERE name = " + name + " AND rType = " + restaurantType; 

in my SQLiteCursorLoader class and I get an error saying that the column rType does not exist, even thought I know the column exists in the Restuarant table. It seems that I get this error when I have two WHERE clauses in my query but this does not happen when I have one WHERE clause. Any help would be GREATLY appreciated! Here is my code:

RestuarantSearchResultsActivity:

package com.studentapplication;
import android.app.Activity;
import android.app.ListActivity;
import android.app.LoaderManager;
import android.content.Loader;
import android.database.Cursor;
import android.os.Bundle;
import android.util.Log;
import android.view.View;
import android.widget.ListView;
import android.widget.SimpleCursorAdapter;

public class RestaurantSearchResultsActivity extends ListActivity implements
LoaderManager.LoaderCallbacks<Cursor>{

DatabaseHelper databaseHelper;
SimpleCursorAdapter mAdapter;
Bundle searchParameters;

@Override
public void onCreate(Bundle savedInstanceState)
{
    Log.w("CoolBeans", "Results Activity Created");
    super.onCreate(savedInstanceState);

    databaseHelper = new DatabaseHelper(getApplicationContext());
    //setContentView(R.layout.restaurant_search_results);

    Bundle searchParameters = getIntent().getExtras();
    String n = searchParameters.getString("name");
    Log.i("0)", n);


    //first N columns should match toViews first N views
    String[] fromColumns = {getString(R.string.database_column_name),
                           getString(R.string.database_column_restaurant_type),
                           getString(R.string.database_column_phone_number),
                           getString(R.string.database_column_address),
               getString(R.string.database_column_csz),
               getString(R.string.database_column_brief_description),
               getString(R.string.database_column_url),
               getString(R.string.database_column_price)};

    int[] toViews = {android.R.id.text1};

    mAdapter = new SimpleCursorAdapter( getApplicationContext(),
                        this.getListView().getId(),
                        null,
                            fromColumns,
                        toViews,
                        0);
    setListAdapter(mAdapter);
    getLoaderManager().initLoader(0, searchParameters, this);       
}

@Override
public Loader<Cursor> onCreateLoader(int id, Bundle searchParameters) {
    Log.i("3)", "Correct Loader Being Built");
    return new SQLiteCursorLoader(getApplicationContext(),
            databaseHelper,
            searchParameters,
            null);

}

@Override
public void onLoadFinished(Loader<Cursor> loader, Cursor data) {
    mAdapter.swapCursor(data);

}

@Override
public void onLoaderReset(Loader<Cursor> loader) {
    mAdapter.swapCursor(null);

}

@Override
    public void onListItemClick(ListView l, View v, int position, long id)
{
    Cursor myCursor = (Cursor)l.getItemAtPosition(position);
    if(l.isItemChecked(position) == true)
    {
        l.setItemChecked(position, false);
        //shut down previously selected item details

    }
    else
    {
        l.setItemChecked(position, true);

        //start previously selected item details

    }
}


}

SQLiteCursorLoader:

package com.studentapplication;

import java.io.FileDescriptor;
import java.io.PrintWriter;
import java.util.Arrays;
import android.content.ContentValues;
import android.content.Context;
import android.content.res.Resources;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.database.sqlite.SQLiteQueryBuilder;
import android.os.Bundle;
import android.util.Log;

public class SQLiteCursorLoader extends AbstractCursorLoader
{
SQLiteOpenHelper db=null;
String rawQuery=null;
String[] args=null;

/**
 * Creates a fully-specified SQLiteCursorLoader. See
 * {@link SQLiteDatabase#rawQuery(SQLiteDatabase, String, String[])
 * SQLiteDatabase.rawQuery()} for documentation on the
 * meaning of the parameters. These will be passed as-is
 * to that call.
 */
public SQLiteCursorLoader(Context context, SQLiteOpenHelper db, Bundle
    searchParameters, String[] args)
{
    super(context);
    this.db=db;
    this.rawQuery=createQuery(searchParameters);
    this.args=args;
}

private String createQuery(Bundle searchParameters) {
    String name = "'";
    name = name + searchParameters.getString("name");
    name = name + "'";
    int price = searchParameters.getInt("price");
    String restaurantType = "'";
    restaurantType = restaurantType +
            searchParameters.getString("restaurant_type");
    restaurantType = restaurantType + "'";

    //String dist = searchParameters.getString("dist");

    String theQuery = "";

    if(!name.equals("''"))
    {
        if(!restaurantType.equals("'_'"))
        {
            theQuery = "SELECT * FROM Restaurant WHERE name 
= "+ name + " AND rType = " + restaurantType;
        }
        else if(price != 0)
        {
            theQuery = "SELECT * FROM Restaurant WHERE name = " + name
+ " AND price = " + ((Integer)price).toString();
        }
        else
        {
            theQuery="SELECT * FROM Restaurant WHERE name = " + name;
        }
    }
    else if(!restaurantType.equals("''"))
    {
        theQuery = "SELECT * FROM Restaurant WHERE type = " +
restaurantType + " SORT BY name"; 
    }
    else if(price != 0)
    {
        theQuery="SELECT * FROM Restaurant WHERE price=" + price + " SORT
BY name";
    }



    return theQuery;
}


/**
 * Runs on a worker thread and performs the actual
 * database query to retrieve the Cursor.
 */
@Override
protected Cursor buildCursor()
{
    return(db.getReadableDatabase().rawQuery(rawQuery, args));
}

/**
 * Writes a semi-user-readable roster of contents to
 * supplied output.
 */
@Override
public void dump(String prefix, FileDescriptor fd, PrintWriter writer, String[]
    args)
{
    super.dump(prefix, fd, writer, args);
    writer.print(prefix);
    writer.print("rawQuery=");
    writer.println(rawQuery);
    writer.print(prefix);
    writer.print("args=");
    writer.println(Arrays.toString(args));
}

public void insert(String table, String nullColumnHack, ContentValues values)
{
    new InsertTask(this).execute(db, table, nullColumnHack, values);
}

public void update(String table, ContentValues values, String whereClause, String[]
    whereArgs)
{
    new UpdateTask(this).execute(db, table, values, whereClause, whereArgs);
}

public void replace(String table, String nullColumnHack, ContentValues values)
{
    new ReplaceTask(this).execute(db, table, nullColumnHack, values);
}

public void delete(String table, String whereClause, String[] whereArgs)
{
    new DeleteTask(this).execute(db, table, whereClause, whereArgs);
}

public void execSQL(String sql, Object[] bindArgs)
{
    new ExecSQLTask(this).execute(db, sql, bindArgs);
}

//Private Classes

private class InsertTask extends ContentChangingTask<Object, Void, Void>
{
    InsertTask(SQLiteCursorLoader loader)
    {
        super(loader);
    }

    @Override
    protected Void doInBackground(Object... params)
    {
        SQLiteOpenHelper db=(SQLiteOpenHelper)params[0];
        String table=(String)params[1];
        String nullColumnHack=(String)params[2];
        ContentValues values=(ContentValues)params[3];

        db.getWritableDatabase().insert(table, nullColumnHack, values);

        return(null);
    }
}

private class UpdateTask extends ContentChangingTask<Object, Void, Void>
{
    UpdateTask(SQLiteCursorLoader loader)
    {
        super(loader);
    }

    @Override
    protected Void doInBackground(Object... params)
    {
        SQLiteOpenHelper db=(SQLiteOpenHelper)params[0];
        String table=(String)params[1];
        ContentValues values=(ContentValues)params[2];
        String where=(String)params[3];
        String[] whereParams=(String[])params[4];

        db.getWritableDatabase().update(table, values, where, whereParams);

        return(null);
    }
}

private class ReplaceTask extends ContentChangingTask<Object, Void, Void>
{
    ReplaceTask(SQLiteCursorLoader loader)
    {
        super(loader);
    }

    @Override
    protected Void doInBackground(Object... params)
    {
        SQLiteOpenHelper db=(SQLiteOpenHelper)params[0];
        String table=(String)params[1];
        String nullColumnHack=(String)params[2];
        ContentValues values=(ContentValues)params[3];

        db.getWritableDatabase().replace(table, nullColumnHack, values);

        return(null);
    }
}

private class DeleteTask extends ContentChangingTask<Object, Void, Void>
{
    DeleteTask(SQLiteCursorLoader loader)
    {
        super(loader);
    }

    @Override
    protected Void doInBackground(Object... params)
    {
        SQLiteOpenHelper db=(SQLiteOpenHelper)params[0];
        String table=(String)params[1];
        String where=(String)params[2];
        String[] whereParams=(String[])params[3];

        db.getWritableDatabase().delete(table, where, whereParams);

        return(null);
    }
}

private class ExecSQLTask extends ContentChangingTask<Object, Void, Void>
{
    ExecSQLTask(SQLiteCursorLoader loader)
    {
        super(loader);
    }

    @Override
    protected Void doInBackground(Object... params)
    {
        SQLiteOpenHelper db=(SQLiteOpenHelper)params[0];
        String sql=(String)params[1];
        Object[] bindParams=(Object[])params[2];

        db.getWritableDatabase().execSQL(sql, bindParams);

     return(null);
    }
}
}

AbstractCursorLoader:

package com.studentapplication;

import android.annotation.TargetApi;
import android.content.AsyncTaskLoader;
import android.content.Context;
import android.database.Cursor;
import android.os.Build;

@TargetApi(Build.VERSION_CODES.HONEYCOMB)
abstract public class AbstractCursorLoader extends AsyncTaskLoader<Cursor> {

abstract protected Cursor buildCursor();
Cursor lastCursor=null;

public AbstractCursorLoader(Context context) {
  super(context);
}

/** 
 * Runs on a worker thread, loading in our data. Delegates
 * the real work to concrete subclass' buildCursor() method. 
 */
@Override
public Cursor loadInBackground() {
  Cursor cursor=buildCursor();

  if (cursor!=null) {
    // Ensure the cursor window is filled
    cursor.getCount();
  }

  return(cursor);
}

/**
 * Runs on the UI thread, routing the results from the
 * background thread to whatever is using the Cursor
 * (e.g., a CursorAdapter).
 */
@Override
public void deliverResult(Cursor cursor) {
  if (isReset()) {
    // An async query came in while the loader is stopped
    if (cursor!=null) {
      cursor.close();
    }

    return;
  }

  Cursor oldCursor=lastCursor;
  lastCursor=cursor;

  if (isStar

Answer 1

In production, when you modify the schema of your database, you bump the schema version number you pass to the SQLiteOpenHelper constructor, so SQLiteOpenHelper will call onUpgrade() and affect the change.

In development, you can do that too if you wish. But, it's easy to forget that. Hence, if your database structure does not seem to be matching expectations, either:

• Bump that schema version number, to force an onUpgrade() call, or

• Wipe out the existing database, via an uninstall, "Clear Data" from your app's entry in Settings, etc., to force a call to onCreate() of your SQLiteOpenHelper