SQLite Android select query issues

Question

I am trying to select a country from my countries database and return it to a cursor to be displayed in a TextView. However each time I run this activity my app crashes. Here is my code:

Database class:

package com.mypackage.msdassignment;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.database.sqlite.SQLiteDatabase.CursorFactory;

public class DBManager 
{
    public static final String KEY_ID = "_id";
    public static final String KEY_CONTENT = "country";
    public static final String KEY_YEAR = "year";
    public static final String KEY_MONTH = "month";
    public static final String KEY_DESC = "description";

    public static final String DATABASE_NAME = "Countries database";
    public static final String DATABASE_TABLE = "Country";
    public static final int DATABASE_VERSION = 1;

    //create table MY_DATABASE (ID integer primary key, Content text not null);
    private static final String SCRIPT_CREATE_DATABASE =
            "create table " + DATABASE_TABLE + " ("
            + KEY_ID + " integer primary key autoincrement, "
            + KEY_CONTENT + " text ,"
            + KEY_YEAR + " integer ,"
            + KEY_MONTH + " month ,"
            + KEY_DESC + " text);";

    private Context context;
    private DBHelper DBHelper;
    private SQLiteDatabase db;

    public DBManager(Context ctx)
    {
        context = ctx;
    }

    //beginning of helper class
    public class DBHelper extends SQLiteOpenHelper 
    {
        public DBHelper(Context context, String name, CursorFactory factory, int version) 
        {
            super(context, name, factory, version);
        }

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

        @Override
        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) 
        {
            // TODO Auto-generated method stub
        }
    }

    public DBManager openToRead() throws SQLException 
    {
        DBHelper = new DBHelper(context, DATABASE_NAME, null, DATABASE_VERSION);
        db = DBHelper.getReadableDatabase();
        return this;
    }

    public DBManager openToWrite() throws SQLException 
    {
        DBHelper = new DBHelper(context, DATABASE_NAME, null, DATABASE_VERSION);
        db = DBHelper.getWritableDatabase();
        return this;
    }

    public void close()
    {
        DBHelper.close();
    }

    public long insert(String content, int year, String month, String description)
    {
        ContentValues contentValues = new ContentValues();
        contentValues.put(KEY_CONTENT, content);
        contentValues.put(KEY_YEAR, year);
        contentValues.put(KEY_MONTH, month);
        contentValues.put(KEY_DESC, description);
        return db.insert(DATABASE_TABLE, null, contentValues);
    }

    public int deleteAll()
    {
        return db.delete(DATABASE_TABLE, null, null);
    }

    public void deleteDatabase()
    {
        context.deleteDatabase(DATABASE_NAME);
    }

    public Cursor queueAll()
    {
        String[] columns = new String[]{
                KEY_ID, 
                KEY_CONTENT,
                KEY_YEAR,
                KEY_MONTH,
                KEY_DESC};
        Cursor cursor = db.query(DATABASE_TABLE, columns,
          null, null, null, null, null);

        return cursor;
    }

    //this is the method with the problem
    public Cursor getCountry()
    {
        Cursor cursor = db.query(DATABASE_TABLE, new String[] {"country", "month"}, 
                "country like " + "'%Ireland%'", null, null, null, null);

        return cursor;
    }
}

Activity where I'm trying to get the country:

package com.mypackage.msdassignment;

import android.app.Activity;
import android.database.Cursor;
import android.os.Bundle;
import android.widget.SimpleCursorAdapter;
import android.widget.TextView;

public class CountryInfo extends Activity
{
    private DBManager db;
    Cursor cursor;
    String passedValue;
    TextView mytextView;

    @Override
    protected void onCreate(Bundle savedInstanceState) 
    {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.country_layout);

        //intent passed data
        passedValue = getIntent().getStringExtra(MainActivity.ID_EXTRA);
        int num = Integer.parseInt(passedValue);
        mytextView = (TextView)findViewById(R.id.country);

        db = new DBManager(this);
        db.openToRead();

        cursor = db.getCountry();
        String s = cursor.getString(1);
        mytextView.setText(s);
    }
}

Logcat errors:

04-02 22:29:30.980: D/AndroidRuntime(17632): procName from cmdline: com.mypackage.msdassignment
04-02 22:29:30.980: E/AndroidRuntime(17632): in writeCrashedAppName, pkgName :com.mypackage.msdassignment
04-02 22:29:30.980: D/AndroidRuntime(17632): file written successfully with content: com.mypackage.msdassignment StringBuffer : ;com.mypackage.msdassignment
04-02 22:29:30.980: E/AndroidRuntime(17632): FATAL EXCEPTION: main
04-02 22:29:30.980: E/AndroidRuntime(17632): java.lang.RuntimeException: Unable to start activity ComponentInfo{com.mypackage.msdassignment/com.mypackage.msdassignment.CountryInfo}: android.database.CursorIndexOutOfBoundsException: Index -1 requested, with a size of 1
04-02 22:29:30.980: E/AndroidRuntime(17632):    at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:1647)
04-02 22:29:30.980: E/AndroidRuntime(17632):    at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:1663)
04-02 22:29:30.980: E/AndroidRuntime(17632):    at android.app.ActivityThread.access$1500(ActivityThread.java:117)
04-02 22:29:30.980: E/AndroidRuntime(17632):    at android.app.ActivityThread$H.handleMessage(ActivityThread.java:931)
04-02 22:29:30.980: E/AndroidRuntime(17632):    at android.os.Handler.dispatchMessage(Handler.java:99)
04-02 22:29:30.980: E/AndroidRuntime(17632):    at android.os.Looper.loop(Looper.java:130)
04-02 22:29:30.980: E/AndroidRuntime(17632):    at android.app.ActivityThread.main(ActivityThread.java:3683)
04-02 22:29:30.980: E/AndroidRuntime(17632):    at java.lang.reflect.Method.invokeNative(Native Method)
04-02 22:29:30.980: E/AndroidRuntime(17632):    at java.lang.reflect.Method.invoke(Method.java:507)
04-02 22:29:30.980: E/AndroidRuntime(17632):    at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:880)
04-02 22:29:30.980: E/AndroidRuntime(17632):    at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:638)
04-02 22:29:30.980: E/AndroidRuntime(17632):    at dalvik.system.NativeStart.main(Native Method)
04-02 22:29:30.980: E/AndroidRuntime(17632): Caused by: android.database.CursorIndexOutOfBoundsException: Index -1 requested, with a size of 1
04-02 22:29:30.980: E/AndroidRuntime(17632):    at android.database.AbstractCursor.checkPosition(AbstractCursor.java:580)
04-02 22:29:30.980: E/AndroidRuntime(17632):    at android.database.AbstractWindowedCursor.checkPosition(AbstractWindowedCursor.java:214)
04-02 22:29:30.980: E/AndroidRuntime(17632):    at android.database.AbstractWindowedCursor.getString(AbstractWindowedCursor.java:41)
04-02 22:29:30.980: E/AndroidRuntime(17632):    at com.mypackage.msdassignment.CountryInfo.onCreate(CountryInfo.java:31)
04-02 22:29:30.980: E/AndroidRuntime(17632):    at android.app.Instrumentation.callActivityOnCreate(Instrumentation.java:1049)
04-02 22:29:30.980: E/AndroidRuntime(17632):    at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:1611)
04-02 22:29:30.980: E/AndroidRuntime(17632):    ... 11 more
04-02 22:29:31.330: W/ResourceType(17644): Skipping entry 0x7f060002 in package table 0 because it is not complex!
04-02 22:29:31.330: W/ResourceType(17644): Skipping entry 0x7f060002 in package table 0 because it is not complex!
04-02 22:29:31.330: W/ResourceType(17644): Skipping entry 0x7f060002 in package table 0 because it is not complex!
04-02 22:29:31.330: W/ResourceType(17644): Skipping entry 0x7f060002 in package table 0 because it is not complex!
04-02 22:29:31.330: W/ResourceType(17644): Skipping entry 0x7f060002 in package table 0 because it is not complex!

Answer 1

I cant seem to find a .moveToFirst() method call on your cursor, which moves the cursor to the first row of your result set. I believe this might very well be the cause of your problems.

So, try something like

cursor = db.getCountry();

if(cursor.moveToFirst()) {
    String s = cursor.getString(1); 
    mytextView.setText(s);
}

Also, remember the getString method is zero based (as others have pointed out in their answers as well). Using 1 will give you the column 'Month', due to the name of your textfields id i suspect this isn't what you are looking for :)

Answer 2

Caused by: android.database.CursorIndexOutOfBoundsException: Index -1 requested, with a size of 1

There is no problem. Just you forgot to call moveToFirst() method on your Cursor.

Explanation:

Each Cursor is always implicitly positioned before first row. This is reason why you app crashed. You tried to get value from -1 row that really does not exist. Solution is to call this method that moves your Cursor to first row and is ready for reading.

if (c.moveToFirst()) {
   // do your stuff
}

Recommendations:

If you want to get values from Cursor, always use c.getColumnIndex("columnName") method to avoid "typo" and "numbering" faults.

Answer 3

You probably mean getString(0), also you probably want to call moveToFirst().