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" + ")";

It is better to test first does your database have any values.You can test it using add a SQLiteManager plugin for eclipse. — Anand Phadke, Nov 14 '14 at 09:39
yes, when I step in to the getSessionId() method, cursor has a size of one, (which it should) and it steps in and hits the line sessionId = cursor.getInt.... but it is still returned as zero and I've initialised it inside the first line of the method! It's very confusing as all the tuts I've followed, this is how it's done — DJ-DOO, Nov 14 '14 at 09:39
@andrewww I'm working with Android studio, I'll see if there's something similar — DJ-DOO, Nov 14 '14 at 09:40
@DhavalGondaliya I have that in my code...if (cursor.moveToFirst()) and it stepped in to the method — DJ-DOO, Nov 14 '14 at 09:47
it logs the sessionId as 0, so does that mean there is something wrong with my insert code? — DJ-DOO, Nov 14 '14 at 10:00
Could you include part of the helper code? I have seen some codes that on the OnUpdate method are killing the database completely, and maybe you are killing your own data. Just double checking. — Trebia Project., Nov 14 '14 at 10:31

score 1 · Answer 1 · edited May 23 '17 at 12:12

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.

hi, in my log cat I notice that when I try to enter the data into the auth table I get this: Error inserting ParamsCRC=1 SessionId=77615 AuthCode=56766340 FirstTime=0 ReturnStock=0 android.database.sqlite.SQLiteException: table auth has no column named FirstTime (code 1): , while compiling: INSERT INTO auth(ParamsCRC,SessionId,AuthCode,FirstTime,ReturnStock) VALUES (?,?,?,?,?)' Even though the column is created in my table create string, any ideas? This may be causing the issue — DJ-DOO, Nov 14 '14 at 10:57
Um... could you include the complete code? Specially I will need to now the strings: FIRST_TIME_RUN, TABLE_AUTH and CREATE_AUTH_TABLE — Trebia Project., Nov 14 '14 at 11:00
Is the value of TABLE_AUTH "auth" and the value of FIRST_TIME_RUN is "FirstTime"?? — Trebia Project., Nov 14 '14 at 11:13

score 0 · Answer 2 · answered Nov 14 '14 at 11:13

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

Unable to retrieve data from SQLite table

2 Answers2