Failing to query the maximum number in database table

Question

I am trying, unsucessfully, to query my database to find the maximum 'area number', in my areas table for a certain inspection, so that I can set the text in a form to the next area number.

The database table consists of four columns; _id, inpsection_link, area_number, area-reference.

I have created the following in my database helper class (using this post as a guide: SQLiteDatabase.query method):

 public int selectMaxAreaNumber (long inspectionId) {
    String inspectionIdString = String.valueOf(inspectionId);
    String[] tableColumns = new String[] { 
            AREA_NUMBER, 
            "(SELECT max(" + AREA_NUMBER + ") FROM " + AREAS_TABLE + ") AS max" 
        }; 
    String whereClause = INSPECTION_LINK + " = ?";
    String[] whereArgs = new String[] {
            inspectionIdString          
        };
    Cursor c = rmDb.query(AREAS_TABLE, tableColumns, whereClause, whereArgs, 
            null, null, null);
    int maxAreaNumber = c.getColumnIndex("max");
    return maxAreaNumber;
}

Which I then call in the areaEdit class as follows:

protected void onCreate(Bundle savedInstanceState) {
    super.onCreate(savedInstanceState);
    rmDbHelper = new RMDbAdapter(this);
    rmDbHelper.open();
    Intent i = getIntent();
    inspectionId = i.getLongExtra("Intent_InspectionID", -1);
    areaId = i.getLongExtra("Intent_AreaID", -1);
    if (areaId == -1) {
        nextAreaNumber = rmDbHelper.selectMaxAreaNumber(inspectionId) + 1;
        Toast.makeText(getApplicationContext(), String.valueOf(nextAreaNumber), 
                Toast.LENGTH_LONG).show();  
    }
    setContentView(R.layout.edit_area);
    setUpViews();
    populateFields();
    setTextChangedListeners();
}

However, it just returns 1 everytime (even if there are numbers higher than that stored in the database).

Confused.com!! Any help much appreciated.

florianmski · Accepted Answer · 2012-09-09T20:17:58.467

2

Your issue is here :

int maxAreaNumber = c.getColumnIndex("max");

You're getting the column index of max, which is 1 because you only have one column in your query. Instead, do something like this :

int maxAreaNumber = 0;
if(c.moveToFirst())
{
    maxAreaNumber = c.getInt(1);
    // or cleaner
    maxAreaNumber = c.getInt(c.getColumnIndex("max"));
}
else
    // no data in cursor

edited Sep 09 '12 at 20:17

answered Sep 09 '12 at 17:23

florianmski

5,603
1
17
14

Hi florianmski, thanks for the response, but this didn't work - I got a CursorIndexOutOfBoundsException: Index -1 requested, with a size of 4 – Scamparelli Sep 09 '12 at 18:19
@Scamparelli I've updated my answer, don't forget the cursor.moveToFirst() method! – florianmski Sep 09 '12 at 18:25
Thank you so much, seems to work now apart from when there is no data in the table. I guess I need to test for null, but it doesn't seem to let me - is there an alternative for int? Cheers. – Scamparelli Sep 09 '12 at 20:07
@Scamparelli A good practice is to check that c.moveToFirst() return true, if not it means there is not data in the cursor, again I've updated my answer. – florianmski Sep 09 '12 at 20:09

Failing to query the maximum number in database table

1 Answers1