Android SQLite : Index -1 requested with a size of 1

Question

I need a database for my application, but it keeps force closing because of the error below. It works in one of my activity, but not the other

Database Handler:

public class DBGestion {

private static final int VERSION_BDD = 1;
private static final String NOM_BDD = "cityquest.db";

private static final String TABLE_QUEST = "table_quest";
private static final String COL_ID = "ID";
private static final int NUM_COL_ID = 0;
private static final String COL_USERNAME = "UserName";
private static final int NUM_COL_USERNAME = 1;
private static final String COL_NIVEAU ="Niveau";
private static final int NUM_COL_NIVEAU = 2;
private static final String COL_NBRIGOLETTES ="NbRigolettes";
private static final int NUM_COL_NBRIGOLETTES = 3;


private SQLiteDatabase bdd;

private MaBaseSQLite maBaseSQLite;  // MaBaseSQLite extends SQLiteOpenHelper

public DBGestion(Context context) {
    maBaseSQLite = new MaBaseSQLite(context, NOM_BDD, null, VERSION_BDD);
}

public void open() {
    bdd = maBaseSQLite.getWritableDatabase();
}

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

public SQLiteDatabase getBDD() {
        return bdd;
}

public long initDB(String userName){
    ContentValues values = new ContentValues();
    values.put(COL_USERNAME, userName);
    values.put(COL_NIVEAU,  0);
    values.put(COL_NBRIGOLETTES, 0);
    return bdd.insert(TABLE_QUEST,null, values);
}


public int getNiveau() {
    Cursor c = bdd.query(TABLE_QUEST, new String[] {COL_NIVEAU}, null, null, null, null, null);
    int niveau = c.getInt(NUM_COL_NIVEAU);
    return niveau;
}

public long setNiveau(int niveau) {
    ContentValues values = new ContentValues();
    values.put(COL_NIVEAU, niveau);
    return bdd.update(TABLE_QUEST,values, COL_ID + " = 0", null);
}

}

And here's the code where I use the get and set method (Sorry for all the french variable names!). Basically I first check if the question was already answered (getNiveau() == 1), if not, I check the answer and then make some Widget INVISIBLE, and setNiveau(1) (it is impossible to close() dbGestion 2 times because the button is hidden)

I hadn't got any problem before I tried to use the database..

public class Question extends Activity {

[variable declaration]

DBGestion dbGestion;


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

        Intent i = getIntent();
        int format = i.getIntExtra(MainActivity.FORMAT,0);


        dbGestion = new DBGestion(this);

        [findViewById for all my variables]

if (format == 1){



        dbGestion.open();

        if (dbGestion.getNiveau() == 1) {
            bonneReponse.setVisibility(View.VISIBLE);
            [......]
            dbGestion.close();
        }  


bouton.setOnClickListener(new OnClickListener() { 

            @Override
            public void onClick(View v) {


            String reponse = new String("");
                reponse = editText.getText().toString();

            if(reponse.equalsIgnoreCase("poete") ||..){

                    bonneReponse.setVisibility(View.VISIBLE);
                                   [...]
                            }
                            else {...}


                                    dbGestion.setNiveau(1); 
                dbGestion.close();
}});

Do you know where does the problem comes from?

Here's how I initiate my database (in MainActivity onCreate methode):

final DBGestion dbGestion = new DBGestion(this);

                dbGestion.open();
            dbGestion.initDB("Alex");
            dbGestion.close();

And here's my SQLiteOpenHelper class :

public class MaBaseSQLite extends SQLiteOpenHelper {

private static final String TABLE_QUEST ="table_quest";
private static final String COL_ID ="ID";
private static final String COL_NIVEAU ="Niveau";
private static final String COL_USERNAME ="UserName";
private static final String COL_NBRIGOLETTES ="NbRigolettes";

private static final String CREATE_DB = "CREATE TABLE " + TABLE_QUEST + " (" + COL_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " 
+ COL_USERNAME + " TEXT NOT NULL, " + COL_NIVEAU + " TEXT NOT NULL, " + COL_NBRIGOLETTES + " TEXT NOT NULL); ";      

public MaBaseSQLite(Context context, String name, CursorFactory factory, int version) {
    super(context, name, factory, version);

}

@Override
public void onCreate(SQLiteDatabase db) {

    db.execSQL(CREATE_DB);

}

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

    db.execSQL("DROP TABLE" + TABLE_QUEST + ";");
    onCreate(db);


}

}

Link to Logcat : http://pastebin.com/ds9bKFcS

Thanks again..

Answer 1

From the error code your getting you can trace it back to this piece of code:

public int getNiveau() {
    Cursor c = bdd.query(TABLE_QUEST, new String[] {COL_NIVEAU}, null, null, null, null, null);
    int niveau = c.getInt(NUM_COL_NIVEAU);
    return niveau;
}

Firstly when you get a Cursor from the database it is your job to check the cursor and move it to the appropriate row. For example in your case if know you only have one row for your team you could do this:

public int getNiveau() {
    Cursor c = bdd.query(TABLE_QUEST,new String[] { COL_NIVEAU},null,null,null,null,null);
    if(c.getCount() == 1) {
        c.moveToFirst();
        int niveau = c.getInt(c.getColumnIndexOrThrow(MaBaseSQLite.COL_NIVEAU));
        c.close();
        return niveau;
    }
    c.close();
    return -1;
}

So essentially what I'm doing is checking if I only have one result column if I do I move to the first column and then get the Integer at the positon. Another thing you have to keep in mind is to get your Column index from the name of the column. You get better portability doing this if you ever decide to change your database schema. All of your questions could of been answered by the Cursor documentation located here: http://developer.android.com/reference/android/database/Cursor.html

Hope that answers all your questions.