SELECT statement not returning MAX number

Question

I'm having an issue with the SELECT statement in the following code (this is in my database helper class):

public Cursor 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); 


if (c != null) { 
    c.moveToFirst(); 
} 
c.close(); 
return c; 

}

I then call this in my activity as follows:

Cursor c = rmDbHelper.selectMaxAreaNumber(inspectionId); 
    startManagingCursor(c); 
    c.moveToFirst(); 
    nextAreaNumber = c.getInt(c.getColumnIndex("max")) + 1;

It is pulling the maximum number from the table, so it seems the WHERE statement is failing. However, I can't see what the issue is.

As part of this question, can anyone confirm if I am doing the right thing by converting the ID into a String before it is used in the query and do I need to write c.moveToFirst() in both bits?

Many thanks in advance!

Answer 1

Try this instead:

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

This will return a Cursor with the largest AREA_NUMBER that has the appropriate inspectionId.

A couple notes:

• When you use a function like Max() you only get one row as a result, there is no need to ask for AREA_NUMBERS and Max(AREA_NUMBERS)
• A Cursor might be empty, but it won't be null
• Don't close a Cursor before you have used it

So you don't need this:

if (c != null) { 
    c.moveToFirst(); 
} 
c.close(); 

Understand that if c somehow was null, you will still receive a NullPointerException on c.close()

---

(Optional) You could remove the whereArgs and just use:

String whereClause = INSPECTION_LINK + " = " + inspectionId;

(Only because inspectionId is long data type, you need a String to perform an injection attack.)

---

Finally you should check for an empty Cursor here:

Cursor c = rmDbHelper.selectMaxAreaNumber(inspectionId); 
startManagingCursor(c); 
if(c.moveToFirst()) 
    nextAreaNumber = c.getInt(c.getColumnIndex("max")) + 1;
else //empty Cursor, return a default value
    nextAreaNumber = 0;

Answer 2

Try change

"(SELECT max(" + AREA_NUMBER + ") FROM " + AREAS_TABLE + ") AS max"

to

"SELECT max(" + AREA_NUMBER + ") AS max FROM " + AREAS_TABLE