I'm fairly new to Android, Kotlin, and SQLLite.
I have this table creation.
val create = "CREATE TABLE IF NOT EXISTS $TABLE_NAME (" +
"$COLUMN_ID INTEGER PRIMARY KEY AUTOINCREMENT, " +
"$COLUMN_USERNAME STRING, $COLUMN_PASSWORD STRING, $COLUMN_EMAIL STRING, $COLUMN_LAST_LOGIN DATETIME NULL, " +
"$COLUMN_PRIV_ACCESS_ADMIN_LIST BOOLEAN, $COLUMN_PRIV_CHANGE_ADMIN_LIST BOOLEAN, $COLUMN_PRIV_SEND_EMAIL_REPORT BOOLEAN, " +
"$COLUMN_IS_ACTIVE BOOLEAN" +
")"
db.execSQL(create)
I can successfully add new data with this:
val values = ContentValues()
values.put(COLUMN_USERNAME, "admin")
values.put(COLUMN_PASSWORD, "password")
values.put(COLUMN_EMAIL, "test@test.com")
values.put(COLUMN_PRIV_ACCESS_ADMIN_LIST, true)
values.put(COLUMN_PRIV_CHANGE_ADMIN_LIST, true)
values.put(COLUMN_PRIV_SEND_EMAIL_REPORT, true)
values.put(COLUMN_IS_ACTIVE, admin.isActive)
val db = this.writableDatabase
db.insert(TABLE_NAME, null, values)
I also can successfully retrieve data with this:
val db = this.readableDatabase
val cursor = db.rawQuery("SELECT * FROM $TABLE_NAME", null)
by which I get the info that the data I put there has the ID = 1
But I cannot change the data into something else, either with this code:
val values = ContentValues()
values.put(COLUMN_USERNAME, "adminssss")
values.put(COLUMN_PASSWORD, "passwordsss")
values.put(COLUMN_EMAIL, "test@test.org")
values.put(COLUMN_PRIV_ACCESS_ADMIN_LIST, false)
values.put(COLUMN_PRIV_CHANGE_ADMIN_LIST, true)
values.put(COLUMN_PRIV_SEND_EMAIL_REPORT, false)
values.put(COLUMN_IS_ACTIVE, true)
val db = this.writableDatabase
db.update(TABLE_NAME, values, "$COLUMN_ID=1", arrayOf())
// or
db.update(TABLE_NAME, values, "$COLUMN_ID=?", arrayOf("1"))
Or
val db = this.writableDatabase
val query = "UPDATE $TABLE_NAME " +
"SET $COLUMN_USERNAME = 'adminssss', $COLUMN_PASSWORD = 'passwordsss', $COLUMN_EMAIL = 'test@test.org', " +
"$COLUMN_PRIV_ACCESS_ADMIN_LIST = 0, " +
"$COLUMN_PRIV_CHANGE_ADMIN_LIST = 1, " +
"$COLUMN_PRIV_SEND_EMAIL_REPORT = 0, " +
"$COLUMN_IS_ACTIVE = 1 " +
"WHERE $COLUMN_ID = 1"
db.rawQuery(query, null)
// or
db.execQuery(query)
I'm not too strong with query language, but I've tried to double check the query and can't found anything wrong with it. When the update query is run, which ever version it is from above example, all can run without error, but when I re-select the data again, nothing is changed. The data stays the same like the original first time I put them in.
Can somebody help?
There is nothing wrong with your first update code (both work), it works (see Working example below).
I suspect that you issue might be that you have deleted a row and the added another row. In such a case, because you have AUTOINCREMENT coded such a row would not have an ID as 1 but perhaps two.
If using the code from the working example then the issues in attempting to use rawQuery and execQuery become moot.
I believe that your issue is how you are viewing the result (or is as previously explained). Consider the following working example :-
The Database Helper class, DBHelper.kt (based upon the available code) including the code, such as update as functions :-
class DBHelper(context: Context) : SQLiteOpenHelper(context, DBNAME, null, DBVERSION) {
val all: Cursor
get() = this.writableDatabase.query(TABLE_NAME, null, null, null, null, null, null)
override fun onCreate(db: SQLiteDatabase) {
db.execSQL("CREATE TABLE IF NOT EXISTS " + TABLE_NAME + "(" +
COLUMN_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
COLUMN_USERNAME + " STRING, " +
COLUMN_PASSWORD + " STRING, " +
COLUMN_EMAIL + " STRING, " +
COLUMN_LAST_LOGIN + " DATETIME DEFAULT NULL, " +
COLUMN_PRIV_ACCESS_ADMIN_LIST + " BOOLEAN, " +
COLUMN_PRIV_CHANGE_ADMIN_LIST + " BOOLEAN," +
COLUMN_PRIV_SEND_EMAIL_REPORT + " BOOLEAN, " +
COLUMN_IS_ACTIVE + " BOOLEAN" +
")"
)
}
fun add(
username: String,
password: String,
email: String,
privaccessadminlist: Boolean,
privchangeadminlist: Boolean,
privsendemailreport: Boolean,
is_active: Boolean): Long {
val cv = ContentValues()
cv.put(COLUMN_USERNAME, username)
cv.put(COLUMN_PASSWORD, password)
cv.put(COLUMN_EMAIL, email)
cv.put(COLUMN_PRIV_ACCESS_ADMIN_LIST, privaccessadminlist)
cv.put(COLUMN_PRIV_CHANGE_ADMIN_LIST, privchangeadminlist)
cv.put(COLUMN_PRIV_SEND_EMAIL_REPORT, privsendemailreport)
return this.writableDatabase.insert(TABLE_NAME, null, cv)
}
fun logAll() {
val csr = all
var sb = StringBuilder()
while (csr.moveToNext()) {
sb = StringBuilder().append("Row is " + csr.position.toString())
sb.append("\n\tUserName is :").append(csr.getString(csr.getColumnIndex(COLUMN_USERNAME)))
sb.append("\n\tPasword is :").append(csr.getString(csr.getColumnIndex(COLUMN_PASSWORD)))
sb.append("\n\tEmail is :").append(csr.getString(csr.getColumnIndex(COLUMN_EMAIL)))
sb.append("\n\t PRIVACCESSAL is ").append((csr.getInt(csr.getColumnIndex(COLUMN_PRIV_ACCESS_ADMIN_LIST)) > 0).toString())
sb.append("\n\t PRIVCHGAL is ").append((csr.getInt(csr.getColumnIndex(COLUMN_PRIV_CHANGE_ADMIN_LIST)) > 0).toString())
sb.append("\n\t PRIVSNDEMAIL is ").append((csr.getInt(csr.getColumnIndex(COLUMN_PRIV_SEND_EMAIL_REPORT)) > 0).toString())
Log.d("LOGDATA", sb.toString())
}
}
// Suggested update code
fun updateById(id: Long,
username: String,
password: String,
email: String,
privaccessadminlist: Boolean,
privchangeadminlist: Boolean,
privsendemailreport: Boolean,
is_active: Boolean): Int {
val cv = ContentValues()
cv.put(COLUMN_USERNAME, username)
cv.put(COLUMN_PASSWORD, password)
cv.put(COLUMN_EMAIL, email)
cv.put(COLUMN_PRIV_ACCESS_ADMIN_LIST, privaccessadminlist)
cv.put(COLUMN_PRIV_CHANGE_ADMIN_LIST, privchangeadminlist)
cv.put(COLUMN_PRIV_SEND_EMAIL_REPORT, privsendemailreport)
val whereclause = "$COLUMN_ID=?"
val whereargs = arrayOf(id.toString())
return this.writableDatabase.update(TABLE_NAME, cv, whereclause, whereargs)
}
//Your code
fun update() {
val values = ContentValues()
values.put(COLUMN_USERNAME, "adminszzz")
values.put(COLUMN_PASSWORD, "passwordzzz")
values.put(COLUMN_EMAIL, "test@test.orgzzz")
values.put(COLUMN_PRIV_ACCESS_ADMIN_LIST, false)
values.put(COLUMN_PRIV_CHANGE_ADMIN_LIST, true)
values.put(COLUMN_PRIV_SEND_EMAIL_REPORT, false)
values.put(COLUMN_IS_ACTIVE, true)
val db = this.writableDatabase
db.update(TABLE_NAME, values, "$COLUMN_ID=1", arrayOf())
// or
//db.update(TABLE_NAME, values, "$COLUMN_ID=?", arrayOf("1"))
}
override fun onUpgrade(db: SQLiteDatabase, oldVersion: Int, newVersion: Int) {
}
companion object {
val DBNAME = "mydb"
val DBVERSION = 1
val TABLE_NAME = "mytable"
val COLUMN_ID = BaseColumns._ID
val COLUMN_USERNAME = "username"
val COLUMN_PASSWORD = "password"
val COLUMN_EMAIL = "email"
val COLUMN_LAST_LOGIN = "last_login"
val COLUMN_PRIV_ACCESS_ADMIN_LIST = "priv_access_admin_list"
val COLUMN_PRIV_CHANGE_ADMIN_LIST = "priv_change_admin_list"
val COLUMN_PRIV_SEND_EMAIL_REPORT = "priv_send_email_report"
val COLUMN_IS_ACTIVE = "is_active"
}
}
And an Activity to invoke the functions MainAvctivity.kt :-
class MainActivity : AppCompatActivity() {
override fun onCreate(savedInstanceState: Bundle?) {
super.onCreate(savedInstanceState)
setContentView(R.layout.activity_main)
var dbhlpr = DBHelper(this)
dbhlpr = DBHelper(this)
dbhlpr.add("admin", "password", "test@test.com", true, true, true, true)
dbhlpr.logAll()
dbhlpr.updateById(1, "adminssss", "passwordsss", "test@test.org", false, true, false, true)
dbhlpr.logAll()
dbhlpr.update();
dbhlpr.logAll();
}
}
When run the following is output to the log :-
2019-05-17 16:12:07.182 18164-18164/aso.so56179532update D/LOGDATA: Row is 0
UserName is :admin
Pasword is :password
Email is :test@test.com
PRIVACCESSAL is true
PRIVCHGAL is true
PRIVSNDEMAIL is true
2019-05-17 16:12:07.184 18164-18164/aso.so56179532update D/LOGDATA: Row is 0
UserName is :adminssss
Pasword is :passwordsss
Email is :test@test.org
PRIVACCESSAL is false
PRIVCHGAL is true
PRIVSNDEMAIL is false
2019-05-17 16:12:07.186 18164-18164/aso.so56179532update D/LOGDATA: Row is 0
UserName is :adminszzz
Pasword is :passwordzzz
Email is :test@test.orgzzz
PRIVACCESSAL is false
PRIVCHGAL is true
PRIVSNDEMAIL is false
i.e. There are 3 lots, each showing the single row from the database. The second two show that the data has been updated accordingly.