Android SQLite how to get particular column:row value

Question

I have a database and I am doing a query to it using

Cursor mCursor = mapDb.query(MY_TABLE, new String[] {KEY_ONEID, KEY_TWOID}, "trim("+KEY_TWOID + ") != '' ", null, null, null, null);

which in SQL terms literally means: SELECT OneId, TwoId FROM auth WHERE trim(TwoId) != ''

Using the raw SQL query this works in my SQLite browser to show me the rows in question, so the Cursor object should contain the same results.

Secondly in my java method I am using a condition to check if this result has anything

    if(mCursor.getColumnIndex(KEY_ONEID) > -1)  //if > -1 then the mCursor returned a row
    {

       if(mCursor.getString(mCursor.getColumnIndex(KEY_ONEID)).contains(id)) //breaks here
                return mCursor.getString(mCursor.getColumnIndex(KEY_TWOID));
            else
                return "";
    }

But for some reason, even though the mCursor hashmap should have all the values returned, this next conditional statement

mCursor.getString(mCursor.getColumnIndex(KEY_ONEID)).contains(id)

still returns: An exception occurred: android.database.CursorIndexOutOfBoundsException

this is the line that throws the exception: mCursor.getString(mCursor.getColumnIndex(KEY_ONEID)) and so does my next item in the return statement

I'm baffled at how to retrieve a particular row value then! because I have pulled the database and run the same query and can clearly see that both of the values I want, do in fact exist!

the Cursor function doesn't provide many other ways to retrieve values, so insight appreciated!

Deutoplasm

by the way I have coded a lightweight ORM framework for Android. github.com/ahmetalpbalkan/orman you can use it for such things. It handles all the cursor cases and you don't see any cursors or SQLs at all. It can give you desired column_name:row pair. let me know if you want to use. – Herlindaherm

Answer 1

You need to position the Cursor to the first row before you can get data from it. To do this you can call: mCursor.moveToFirst(). Also, this method call returns a boolean, which will be false if there are no results; so you can also use it to guard against that case.

If you need to iterate through multiple results, then after calling mCursor.moveToFirst(), you can use the mCursor.moveToNext() method go through the result rows one by one. Once again, this returns false when it reaches the end of the data set.

Hope that makes sense.

Tragedy

Answer 2

You can iterate the cursor like this...

Cursor c=dbhelper.getQueById(i);
if(c.getCount()>0)
{
    for(c.moveToFirst();!c.isAfterLast();c.moveToNext())
    {
        txt1.setText(c.getString(0));
        txt2.setText(c.getString(1));
        txt3.setText(c.getString(2));   

    }
}

else
{
    Log.d(" Null value in cursor ", " null ");
}
c.close();
dbhelper.close();

Entrenchment

Answer 3

Try calling mCursor.moveToFirst() before you try and call mCursor.getString.

Hydropic

How would I iterate with this? I need conditions for if there are 1 rows and if there are multiple rows – Deutoplasm

if(mCursor.moveToFirst()){ do{ // process the row }while(mCursor.moveToNext()); – Hydropic

Answer 4

you Just need to call cursor.moveToNext() move to next start from the beginning so there we don't need to call moveToFirst. moveToFirst is actually called in middle of something.

while (cursor.moveToNext()) {
//whatever you want to do 
}

Achlamydeous