Not getting rows from database. SQLite, Android, Kotlin

Question

I have a function inside DatabaseHelper.kt:

fun readData_Inventory(barcode: String): Inventory{ 
        val p0 = this.readableDatabase
        val selection = "$COL_PRODUCTID = ?"
        val selectionArgs = arrayOf(barcode)
        val cursor = p0.query(TABLE_INVENTORY, null, selection, selectionArgs,null, null, null)
        Toast.makeText(context, "Barcode: $barcode, Count: ${cursor.count}", Toast.LENGTH_LONG).show()
        val inventory: Inventory?
        if (cursor.moveToLast()) {
            cursor.moveToLast()
            val productId = cursor.getString(cursor.getColumnIndex(COL_PRODUCTID))
            val quantity = cursor.getDouble(cursor.getColumnIndex(COL_QUANTITY))
            val source = cursor.getString(cursor.getColumnIndex(COL_SOURCE))
            val dateCheck = cursor.getString(cursor.getColumnIndex(COL_DATECHECK))
            inventory = Inventory(productId, quantity, source, dateCheck)
        } else{
            inventory = Inventory ("000", 0.0, "", "")
        }
        return inventory

But the Toast shows 0 in cursor.count.

I know the table has rows because when I run the next code it brings the rows I'm looking for:

fun readData_Inventory(barcode: String): Inventory{ 
        val p0 = this.readableDatabase
        val sql = "select * from $TABLE_INVENTORY"
        val cursor = p0.rawQuery(sql, null)
        val inventory: Inventory?
        Toast.makeText(context, "Barcode: $barcode, Count: ${cursor.count}", Toast.LENGTH_LONG).show()
        if (cursor.moveToLast()) {
            cursor.moveToLast()
            val productId = cursor.getString(cursor.getColumnIndex(COL_PRODUCTID))
            val quantity = cursor.getDouble(cursor.getColumnIndex(COL_QUANTITY))
            val source = cursor.getString(cursor.getColumnIndex(COL_SOURCE))
            val dateCheck = cursor.getString(cursor.getColumnIndex(COL_DATECHECK))
            inventory = Inventory(productId, quantity, source, dateCheck)
        } else{
            inventory = Inventory ("000", 0.0, "", "")
        }
        return inventory

Any help is appreciated. Thanks in advance.

EDIT: I tried with the next code:

fun readData_Inventory(barcode: String) {
        val p0 = this.readableDatabase
        val inventory: Inventory?
        val sql = "select * from $TABLE_INVENTORY"
        val cursor = p0.rawQuery(sql, null)
        Toast.makeText(context, "Size: ${cursor.count} Move: ${cursor.moveToFirst()}", Toast.LENGTH_LONG).show()
        /////////////////
        cursor.moveToFirst()
        val productId = cursor.getString(cursor.getColumnIndex(COL_PRODUCTID))
        Toast.makeText(context, "ID: $productId", Toast.LENGTH_LONG).show()
    }

When I run the code with only the first part (before the /////// part), the toast shows "Size: 1, true" (that's ok, my table only has 1 row). But when I add the second part (after the ////////) it shows the error: android.database.CursorIndexOutOfBoundsException: Index 0 requested, with a size of 0

I'm adding the part where I insert the data... maybe is that...

fun insertData_Inventory(inventory: Inventory){
        val p0 = this.writableDatabase
        val cv = ContentValues()
        cv.put(COL_PRODUCTID, inventory.product_id)
        cv.put(COL_QUANTITY, inventory.quantity)
        cv.put(COL_SOURCE, inventory.source)
        cv.put(COL_DATECHECK, inventory.checkDate)
        val result = p0.insert(TABLE_INVENTORY, null, cv)
        if (result == (-1).toLong())
            Toast.makeText(context, "Insert Inventory Failed", Toast.LENGTH_SHORT).show()
        else
            Toast.makeText(context, "Insert Inventory Success", Toast.LENGTH_SHORT).show()
        p0.close()
    }

It always inserts and shows "Insert Inventory Success"

Also the part where I declare the table:

val TABLE_INVENTORY = "Inventory"
val COL_INVENTORYID = "inventory_id"
val COL_PRODUCTID = "product_id"
val COL_QUANTITY = "quantity"
val COL_SOURCE = "source"
val COL_DATECHECK = "date_check"
val CREATE_T_INVENTORY = "CREATE TABLE if not exists $TABLE_INVENTORY " +
        "(" +
        "$COL_INVENTORYID integer PRIMARY KEY, " +
        "$COL_PRODUCTID varchar(20), " +
        "$COL_QUANTITY double, " +
        "$COL_SOURCE varchar(20)," +
        "$COL_DATECHECK datetime, " +
        "FOREIGN KEY($COL_PRODUCTID) REFERENCES $TABLE_PRODUCT($COL_PRODUCTID)" +
        ")"

Any other ideas? Thanks in advance!

You should use """ so that you don't need to do all this + stuff. — EpicPandaForce
– EpicPandaForce, Commented Jul 9, 2018 at 22:20
"""CREATE TABLE if not exists $TABLE_INVENTORY ( $COL_INVENTORYID integer PRIMARY KEY, ....""" — EpicPandaForce
– EpicPandaForce, Commented Jul 9, 2018 at 22:45
@EpicPandaForce does that work even with multiple lines? Because I'm not so good at this (you can see this question and how I solved it after 2 days of debugging), I need the structure I put in here or else I become crazy. Thanks! — Said
– Said, Commented Jul 9, 2018 at 22:49

score 1 · Accepted Answer · 2018-07-09 12:48:42Z

1

Try the whole statement in a rawquery:

val sql = "select * from $TABLE_INVENTORY WHERE trim($COL_PRODUCTID) = '$barcode'"
val cursor = p0.rawQuery(sql, null)

I assume barcode is TEXT

edited Jul 9, 2018 at 12:48

answered Jul 9, 2018 at 6:58

user8959091

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

4 Comments

Said Over a year ago

Thanks! Still not working. with '$barcode' and without the apostrophe... barcode is varchar. Any other ideas?

Said Over a year ago

Same, doesn't work... (sorry for the late reply, this is part of my thesis project but I had to go to work, thanks for your time, I appreciate it).

Said Over a year ago

A friend of mine said to try select * from $table where $COL_PRODUCTID like '%$barcode% and it doesn't work either...

Said Over a year ago

If you have time, please check the edit in my original question. Thanks

Said · Accepted Answer · 2018-07-09 22:27:15Z

1

I was missing a line when initializing my object:

inventory.product_id = product.product_id

Thanks, especially @mTak

answered Jul 9, 2018 at 22:27

Said

531 gold badge1 silver badge11 bronze badges

Collectives™ on Stack Overflow

Not getting rows from database. SQLite, Android, Kotlin

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related