Unable to retrieve data from SQLite table

Question

I am adding data to an sqlite table as follows:

public void addAuthDetails(String authCode, int sessionId, int paramsCrC, int returnStock, int firstTime){
        SQLiteDatabase db = getWritableDatabase();
        ContentValues authValue = new ContentValues();

        authValue.put(AUTH_CODE, authCode);
        authValue.put(SESSION_ID, sessionId);
        authValue.put(PARAM_CRC, paramsCrC);
        authValue.put(RETURN_STOCK, returnStock);
        authValue.put(FIRST_TIME_RUN, firstTime);

        db.insert(TABLE_AUTH, null, authValue);
    }

There is only ever one row in this database, however, when I try to query the session id it returns zero, and I've debugged and the value of session id isn't zero.

Here is how I query to obtain the session id.

 public int getSessionId(){
    int sessionId = 0;
    String sessionIdQuery = "SELECT * FROM " + TABLE_AUTH;

    SQLiteDatabase db = getReadableDatabase();

    Cursor cursor = db.rawQuery(sessionIdQuery, null);

    if(cursor.moveToFirst()){
        sessionId = cursor.getInt(cursor.getColumnIndex(SESSION_ID));
    }
    return sessionId;
}

I'm not sure why I'm getting a zero value? Could anyone help me out on this, I'd greatly appreciate it.

EDIT Some DBManager code

 public DBManager(Context ctx) {
    super(ctx, DATABASE_NAME, null, DATABASE_VERSION);
}


@Override
public void onCreate(SQLiteDatabase db) {
    db.execSQL(CREATE_USER_TABLE);
    db.execSQL(CREATE_AUTH_TABLE);
    db.execSQL(CREATE_PARAMS_TABLE);
    db.execSQL(CREATE_PRODUCTS_TABLE);
    db.execSQL(CREATE_VENDORS_TABLE);
    db.execSQL(CREATE_STOCK_TABLE);
}

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    db.execSQL("DROP TABLE IF EXISTS " + TABLE_USERS);
    db.execSQL("DROP TABLE IF EXISTS " + TABLE_STOCK);
    db.execSQL("DROP TABLE IF EXISTS " + TABLE_VENDORS);
    db.execSQL("DROP TABLE IF EXISTS " + TABLE_PRODUCTS);
    db.execSQL("DROP TABLE IF EXISTS " + TABLE_PARAMS);
    db.execSQL("DROP TABLE IF EXISTS " + TABLE_AUTH);

    onCreate(db);
}


   //TABLE_AUTH Create Statement
    public static final String CREATE_AUTH_TABLE = "CREATE TABLE "
            + TABLE_AUTH + "("
            + AUTH_CODE + " TEXT, "
            + SESSION_ID + " TEXT, "
            + AUTH_TIME + " INTEGER, "
            + TOKEN + " TEXT, "
            + PARAM_CRC + " INTEGER, "
            + FIRST_TIME_RUN + "INTEGER, "
            + RETURN_STOCK + "INTEGER, "
            + ENCRYPT_KEY
            + " TEXT" + ")";

Answer 1

ok, I apologise for any time wasted on this issue.

I have managed to remedy the problem.

ok, I have managed to fix it...the reason for the above error was I was missing a space

+ FIRST_TIME_RUN + "INTEGER, "

+ RETURN_STOCK + "INTEGER, " between " and INTEGER. So the table was created without these columns, then when I tried to insert data into all columns and they didn't exist, I'm assuming no data was written to the db, so when I tried to pull the data it wasn't there...I didn't know spacing in the creation of tables was so important!!! Important lesson learned! Thanks for all the help

Answer 2

I don't know if that's the problem creating your database to fail, but the OnUpgrade method is deleting the database everytime you are calling it. Look to this discussion Confusion: How does SQLiteOpenHelper onUpgrade() behave? And together with import of an old database backup?

You are deleting the data and creating the database from scratch.

I suggest you to include an "if" statement behind all the onupgrade lines you already have:

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

if (newVersion > oldVersion) {
   db.execSQL("DROP TABLE IF EXISTS " + TABLE_USERS);
   db.execSQL("DROP TABLE IF EXISTS " + TABLE_STOCK);
   db.execSQL("DROP TABLE IF EXISTS " + TABLE_VENDORS);
   db.execSQL("DROP TABLE IF EXISTS " + TABLE_PRODUCTS);
   db.execSQL("DROP TABLE IF EXISTS " + TABLE_PARAMS);
   db.execSQL("DROP TABLE IF EXISTS " + TABLE_AUTH);

   onCreate(db);
 }

}

Still what I introduced is dangerous, everytime you have a new version of the database it will kill the table, delete the data and create a new one. Unless your app needs to delete all data on each onupgrade.

I suggest to include a copy of the database for later on transfer the data to a new one.