Simple SQLite database getting Cursor error

Question

-- Edit - Solution --

Thanks to laalto I realized that I forgot to update my allColumns array in my DataSource to show my new column in my SQL query:

private String[] allColumns = {ClientDataSQLHelper.COLUMN_ID, ClientDataSQLHelper.COLUMN_CLIENT_NAME,
                        ClientDataSQLHelper.COLUMN_CLIENT_ADDRESS, ClientDataSQLHelper.COLUMN_CLIENT_NUMBER,
                        ClientDataSQLHelper.COLUMN_EMPLOYEE_ID};

--Initial Problem--

So I am trying to create a simple database on Android that will maintain client names, addresses, and numbers as well as attaching an employee id for the employee that is working with the client (think cleaning or in-home nursing services). The problem that I am encountering is that my table should (from how I think I am designing it) should have 5 rows, given the following SQL query string:

private static final String DB_CREATE = "create table " + TABLE_CLIENTS + "(" +
        COLUMN_ID + " integer primary key autoincrement, " +
        COLUMN_EMPLOYEE_ID + " integer, " +
        COLUMN_CLIENT_NAME + " text not null, " +
        COLUMN_CLIENT_ADDRESS + " text not null, " +
        COLUMN_CLIENT_NUMBER + " text not null);";

within my SQLHelper class, and with the respective variables:

public static final String TABLE_CLIENTS = "clients";
public static final String COLUMN_ID = "_id"; 
public static final String COLUMN_CLIENT_NAME = "name";
public static final String COLUMN_CLIENT_ADDRESS = "address";
public static final String COLUMN_CLIENT_NUMBER = "number";
public static final String COLUMN_EMPLOYEE_ID = "employee_id";

This query is performed in my SQLHelper's Overriden SQL query:

@Override
public void onCreate(SQLiteDatabase db) {
    Log.d(TAG, "creating database");
    db.execSQL(DB_CREATE);
}

The problem that I am encountering, is that when I try to implement my DataSource class, where my getAllClients() method:

public List <ClientHolder> getAllClients(){
    List<ClientHolder> clients = new ArrayList<ClientHolder>();

    // Again, without our Cursor, we can't actually point at any of the data we want to
    // work with/manipulate
    Cursor cursor = db.query(SQLHelper.TABLE_CLIENTS, allColumns, 
            null, null, null, null, null);
    cursor.moveToFirst();

    while(!cursor.isAfterLast()){
        ClientHolder client = cursorToClient(cursor);
        clients.add(client);
        cursor.moveToNext();
    }

    cursor.close();
    if(clients.size() == 0){
        return null;
    }
    return clients;
}

takes advantage of my cursorToClient() method to actually get the data from the cursor position in my database:

private ClientHolder cursorToClient(Cursor cursor){
    ClientHolder client = new ClientHolder();
    long id = cursor.getLong(0);
    String name = cursor.getString(1);
    String address = cursor.getString(2);
    String number = cursor.getString(3);

    Log.d(TAG, "Client " + id + ": " + name + ", " + address + ", " + number);

    int employeeId = cursor.getInt(4);  // THIS IS MY ERROR LINE

    client.setClientID(id);
    client.setClientName(name);
    client.setClientAddress(address);
    client.setClientNumber(number);
    client.setEmployeeID(employeeId);
    return client;
}

On the line Integer employeeId = cursor.getInt(4); I am getting the error Failed to read row 0, column 4 from a CursorWindow which has 1 rows, 4 columns. But I should have 5 columns according to my SQL create query (_id, name, address, number, employee_id), no? My Log prints out correctly, so my values up to that point are correct, it just seems as though the COLUMN_EMPLOYEE_ID + " integer, " portion of my SQL isn't effectively being implemented.

Does allColumns contain all 5 columns? Did you recently add the fifth column and forgot to update the database file? — laalto
– laalto, Commented Mar 31, 2014 at 17:03
@zgc7009 - As a tip. Your while loop for mapping records from the Cursor is confusing and over-complicated. get rid of your moveToFirst() call directly after the query. Get rid of the moveToNext within the while loop Instead write the loop to be while(cursor.moveToNext()){ ... } — dymmeh
– dymmeh, Commented Mar 31, 2014 at 17:09
@dymmeh Thanks, was working off of an old template I made from an example. Efficiency wasn't the key :P — zgc7009
– zgc7009, Commented Mar 31, 2014 at 17:11

Community · Accepted Answer · 2017-05-23 10:28:39Z

1

Possibly it's either one of the following:

You allColumns projection has only 4 columns.
You edited the table schema and forgot to update the database file. See When is SQLiteOpenHelper onCreate() / onUpgrade() run?

Your updated question shows it was reason 1.

edited May 23, 2017 at 10:28

CommunityBot

11 silver badge

answered Mar 31, 2014 at 17:10

laalto

153k68 gold badges293 silver badges306 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Simple SQLite database getting Cursor error

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related