0

I am trying to save data from API to sqlite database, all my data are saved but when I try to retrieve the data (driver_id) returns null value. Am I getting this problem because I have defined my value driver_id as integer and the API data is coming in string?

My log cat when data is saved:

E/LoginCheck: ValueCheck drivername=user driver_id=2 drivercontact=ba 1 kha 2345 busnumber=999999

My SQLiteHandler is shown below:

 public class SQLiteHandler extends SQLiteOpenHelper {
 private static final String TAG = SQLiteHandler.class.getSimpleName();

// All Static variables
// Database Version
private static final int DATABASE_VERSION = 1;

// Database Name
private static final String DATABASE_NAME = "android_api";

// Login table name
private static final String TABLE_USER = "user";

// Login Table Columns names
private static final String KEY_DRIVER_ID = "driver_id";
private static final String KEY_DRIVER_NAME = "drivername";
private static final String KEY_BUS_NUMBER = "busnumber";
private static final String KEY_SCHOOL_UID = "school_id";
private static final String KEY_CONTACT_NUMBER = "drivercontact";

public SQLiteHandler(Context context) {
    super(context, DATABASE_NAME, null, DATABASE_VERSION);
}

// Creating Tables
@Override
public void onCreate(SQLiteDatabase db) {
    String CREATE_LOGIN_TABLE = "CREATE TABLE " + TABLE_USER + "("
            + KEY_DRIVER_ID + " INTEGER PRIMARY KEY," + KEY_DRIVER_NAME + " TEXT,"
            + KEY_BUS_NUMBER + " TEXT UNIQUE," + KEY_CONTACT_NUMBER + " TEXT" + ")";
    db.execSQL(CREATE_LOGIN_TABLE);

    Log.d(TAG, "Database tables created" +CREATE_LOGIN_TABLE);
}

// Upgrading database
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    // Drop older table if existed
    db.execSQL("DROP TABLE IF EXISTS " + TABLE_USER);

    // Create tables again
    onCreate(db);
}

/**
 * Storing user details in database
 * */
public void addUser(String drivername, String busnumber, String driver_id, String drivercontact) {
    SQLiteDatabase db = this.getWritableDatabase();

    ContentValues values = new ContentValues();
    values.put(KEY_DRIVER_NAME, drivername); 
    values.put(KEY_BUS_NUMBER, busnumber); 
    values.put(KEY_DRIVER_ID, driver_id); 
    values.put(KEY_CONTACT_NUMBER, drivercontact);

    Log.e("LoginCheck", "ValueCheck " + values.toString());
    // Inserting Row
    long id = db.insert(TABLE_USER, null, values);
    db.close(); // Closing database connection

    Log.d(TAG, "New user inserted into sqlite: " + id);
}

/**
 * Getting user data from database
 * */
public HashMap<String, String> getUserDetails() {
    HashMap<String, String> user = new HashMap<String, String>();
    String selectQuery = "SELECT  * FROM " + TABLE_USER;

    SQLiteDatabase db = this.getReadableDatabase();
    Cursor cursor = db.rawQuery(selectQuery, null);
    // Move to first row
    cursor.moveToFirst();
    if (cursor.getCount() > 0) {
        user.put("drivername", cursor.getString(1));
        user.put("busnumber", cursor.getString(2));
        user.put("driver_id", cursor.getString(3));
        user.put("drivercontact", cursor.getString(4));
        Log.e("newvaluecheck", "newvaluecheck " + user.toString());
    }
    cursor.close();
    db.close();
    // return user
    Log.d(TAG, "Fetching user from Sqlite: " + user.toString());

    return user;
}

/**
 * Re crate database Delete all tables and create them again
 * */
public void deleteUsers() {
    SQLiteDatabase db = this.getWritableDatabase();
    // Delete All Rows
    db.delete(TABLE_USER, null, null);
    db.close();

    Log.d(TAG, "Deleted all user info from sqlite");
}

}

My logcat:

E/newvaluecheck: newvaluecheck {drivername=user, driver_id=null, drivercontact=ba 1 kha 2345, busnumber=999999}
3
  • This is an unclear question IMO. You need to point out which lines of code concern the problem. We shouldn't have to digest 100+ lines of code to find your problem. Commented Sep 18, 2017 at 3:35
  • my problem is the driver_id data is saved but when i try to access it, the value is null as shown in logcat Commented Sep 18, 2017 at 3:39
  • Integer.parseInt(cursor.getString(3)) use this to convert string to int Commented Sep 18, 2017 at 3:57

2 Answers 2

1

because you define the table with

String CREATE_LOGIN_TABLE = "CREATE TABLE " + TABLE_USER + "(" + KEY_DRIVER_ID + " INTEGER PRIMARY KEY," + KEY_DRIVER_NAME + " TEXT," + KEY_BUS_NUMBER + " TEXT UNIQUE," + KEY_CONTACT_NUMBER + " TEXT" + ")";

and then extract the data with SELECT * (i.e. all columns), the driver_id offset will be 0 (the very first column) not 3.

I'd suggest not using hard coded offsets, but rather utilise the Cursor method getColumnIndex(columname as String) to provide the actual offset according to the column name.

So I'd suggest using :-

if (cursor.getCount() > 0) {
        user.put("drivername", cursor.getString(cursor.getColumnIndex(KEY_DRIVER_NAME)));
        user.put("busnumber", cursor.getString(cursor.getColumnIndex(KEY_BUS_NUMBER)));
        user.put("driver_id", cursor.getString(getColumnIndex(KEY_DRIVER_ID)));
        user.put("drivercontact", cursor.getString(getColumnIndex(KEY_CONTACT_NUMBER)));
        Log.e("newvaluecheck", "newvaluecheck " + user.toString());

P.S. not checked so there may be the odd typo.

You could also replace :-

    cursor.moveToFirst();
    if (cursor.getCount() > 0) {

with

    if(cursor.moveToFirst) {

as cursor.MoveToFirst will return false if there is no first row to move to.

You should also really use the correct Cursor get????? method appropriate for the column, e.g. for the driver_id column, this would be stored as a long, so rather than:-

cursor.getString(getColumnIndex(KEY_DRIVER_ID)) you should use :-

cursor.getLong(getColumnIndex(KEY_DRIVER_ID))

Not using the appropriate type can result in unexpected results, especially for doubles where String may lose precision.

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

Comments

0

Just replace this cursor.getString(1) with cursor.getInt(cursor.getColumnIndex("KEY_DRIVER_ID"))

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.