SimpleCursorAdapter does not load external sqlite

Question

The error says: column _id does not exists but the column is in the database (set as primary key) and this one is located in the external SD folder. I'm trying to return the values contained in the database on the initial load of the activity but it seems like the cursor is not returning anything.

public class ComponentsDbAdapter {

  public static final String COLUMN_ID = "_id";
  public static final String COLUMN_SUBSTRUCTURE = "substructure";
  public static final String COLUMN_TYPE = "type";
  public static final String COLUMN_ORDERNUM = "ordernum";
  public static final String COLUMN_INSTALLATION = "installation";
  private static final String TAG = "ComponentsDbAdapter";
  private DatabaseHelper mDbHelper;
  private SQLiteDatabase mDb;
  private static final String DATABASE_PATH = Environment.getExternalStorageDirectory().getAbsoluteFile()+ "/DATABASE_BACKUP/IMPORTED/";
  private static final String DATABASE_NAME = "android.db";
  private static final String TABLE_NAME = "TAB_WORKSCPE";
  private static final int DATABASE_VERSION = 1; 
  private final Context mCtx;


  public ComponentsDbAdapter open() throws SQLException {
    mDbHelper = new DatabaseHelper(mCtx);
    mDb = mDbHelper.getWritableDatabase();
    return this;
  }

  private static class DatabaseHelper extends SQLiteOpenHelper {
    DatabaseHelper(Context context) {
        super(context, DATABASE_PATH+DATABASE_NAME, null, DATABASE_VERSION);
    }
    @Override
    public void onCreate(SQLiteDatabase db) {
        db.query(TABLE_NAME, new String[] {COLUMN_ID, COLUMN_SUBSTRUCTURE, COLUMN_TYPE, COLUMN_ORDERNUM, COLUMN_INSTALLATION}, null, null, null, null, null); 
    }
    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        Log.w(TAG, "Upgrading database from version " + oldVersion + " to " + newVersion + ", which will destroy all old data");
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME);
        onCreate(db);
    }
  }

  public ComponentsDbAdapter(Context ctx) {
    this.mCtx = ctx;
  }

  public void close() {
    if (mDbHelper != null) {
        mDbHelper.close();
    }
 }

  public Cursor fetchComponentsByName(String inputText) throws SQLException {
    Log.w(TAG, inputText);
    Cursor mCursor = null;
    if (inputText == null  ||  inputText.length () == 0)  {
        mCursor = mDb.query(TABLE_NAME, new String[] {COLUMN_ID, COLUMN_SUBSTRUCTURE, COLUMN_TYPE, COLUMN_ORDERNUM, COLUMN_INSTALLATION}, null, null, null, null, null); 
} else {
    mCursor = mDb.query(true, TABLE_NAME, new String[] {COLUMN_ID, COLUMN_SUBSTRUCTURE, COLUMN_TYPE, COLUMN_ORDERNUM, COLUMN_INSTALLATION}, COLUMN_TYPE + " like '%" + inputText + "%'", null, null, null, null, null);
}
    if (mCursor != null) {
        mCursor.moveToFirst();
    }
    return mCursor; 
}

public Cursor fetchAllComponents() {
    Cursor mCursor = mDb.query(TABLE_NAME, new String[] {COLUMN_ID, COLUMN_SUBSTRUCTURE, COLUMN_TYPE, COLUMN_ORDERNUM, COLUMN_INSTALLATION}, null, null, null, null, null);
    if (mCursor != null) {
        mCursor.moveToFirst();
    }
    return mCursor;
  }
}





public class AndroidListViewCursorAdaptorActivity extends Activity {

private ComponentsDbAdapter dbHelper;
private SimpleCursorAdapter dataAdapter;

@Override
public void onCreate(Bundle savedInstanceState) {
    super.onCreate(savedInstanceState);
    setContentView(R.layout.main);

    dbHelper = new ComponentsDbAdapter(this);
    dbHelper.open();

    //Generate ListView from SQLite Database
    displayListView();
}

private void displayListView() {
    Cursor cursor = dbHelper.fetchAllComponents();

    // The desired columns to be bound
    String[] columns = new String[] {
        ComponentsDbAdapter.COLUMN_SUBSTRUCTURE,
        ComponentsDbAdapter.COLUMN_TYPE,
        ComponentsDbAdapter.COLUMN_ORDERNUM,
        ComponentsDbAdapter.COLUMN_INSTALLATION
    };

    // the XML defined views which the data will be bound to
    int[] to = new int[] {
        R.id.inst,
        R.id.subdt,
        R.id.type,
        R.id.ordernum,
    };

    // create the adapter using the cursor pointing to the desired data
    //as well as the layout information
    dataAdapter = new SimpleCursorAdapter(
        this, 
        R.layout.country_info,
        cursor,
        columns,
        to,
        0);

    ListView listView = (ListView) findViewById(R.id.listView1);
    // Assign adapter to ListView
    listView.setAdapter(dataAdapter);


    listView.setOnItemClickListener(new OnItemClickListener() {
        @Override
        public void onItemClick(AdapterView<?> listView, View view,
            int position, long id) {
            // Get the cursor, positioned to the corresponding row in the result set
            Cursor cursor = (Cursor) listView.getItemAtPosition(position);

            // Get the state's capital from this row in the database.
            String compSubdt = cursor.getString(cursor.getColumnIndexOrThrow("subdt"));
            Toast.makeText(getApplicationContext(), compSubdt, Toast.LENGTH_SHORT).show();
        }
    });

    EditText myFilter = (EditText) findViewById(R.id.myFilter);
    myFilter.addTextChangedListener(new TextWatcher() {

        public void afterTextChanged(Editable s) {
        }

        public void beforeTextChanged(CharSequence s, int start,int count, int after) {
        }

        public void onTextChanged(CharSequence s, int start,int before, int count) {
            dataAdapter.getFilter().filter(s.toString());
        }
    });

    dataAdapter.setFilterQueryProvider(new FilterQueryProvider() {
        public Cursor runQuery(CharSequence constraint) {
            return dbHelper.fetchComponentsByName(constraint.toString());
        }
    });
 }
 }

Answer 1

It doesn't appear from your code that you've created the table yet, so no columns will be found.

You do this within the onCreate method by creating a query to create the table. In your code you appear to be doing a select rather than create.

private static final String TABLE_CREATE = "create table "
    + TABLE_NAME
    + "("
    + COLUMN_ID + " integer primary key autoincrement, "
    + COLUMN_TYPE + " text not null default '', "
    + COLUMN_ORDERNUM + " integer not null default 0, "
    + COLUMN_INSTALLATION + " integer not null default 0, "
    + COLUMN_SUBSTRUCTURE + " text not null default ''"
    + ");";

@Override
public void onCreate(SQLiteDatabase database) {
    database.execSQL(TABLE_CREATE);
}

To store this on the external storage, you'll need to override getDatabasePath(...). A similar solution is here https://stackoverflow.com/a/8385537/935779

@Override
public File getDatabasePath(String name) {
    // reference where you would like the file to be here.
    File result = new File(getExternalFilesDir(null), name);
    return result;
}

I believe you'll want to override this with your Application class since it's a member of ContextWrapper.

The method getDatabaseFile(...) is used inside of openOrCreateDatabase(...) to determine the location.

Alternatively you could just override openOrCreateDatabase(...) and set the file location there.

Answer 2

Ok, this took me almost week and a lot of stress but here is the solution. I started to go through a lot of tutorials and got it working in this one:

http://www.mysamplecode.com/2012/11/android-database-content-provider.html

I extracted the database from the virtual device and manually added more data. Then copied the database to the desired folder on my device folder (Its just to make sure the database consistency/columns are exactly the same). Then changed MyDatabaseHelper class as follows:

public class MyDatabaseHelper extends SQLiteOpenHelper {

    private static final String DATABASE_PATH = Environment.getExternalStorageDirectory().getAbsoluteFile()+ "/MYFOLDER/";
    private static final String DATABASE_NAME = "TheWorld.db";
    private static final int DATABASE_VERSION = 1;

    MyDatabaseHelper(Context context) {
        super(context, DATABASE_PATH+DATABASE_NAME, null, DATABASE_VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        CountriesDb.onCreate(db);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        CountriesDb.onUpgrade(db, oldVersion, newVersion);
    }
}

Don't forget to add permissions to your manifest:

<uses-permission
    android:name="android.permission.WRITE_EXTERNAL_STORAGE" />

Done! If you read through the posts above the answer is based on Kirks advice so reading his recommended link helps. I still have more tests to do just in case my database structure was wrong before.

Answer 3

I don't think you can change or even specify the location of the database, only the name. Leave off the path and don't try to put it in External Storage - let Android determine the path.