how to get every row from SQLite from a function that returns only one row at a time

Question

I have a SQLite DB which contains N rows, I need to retrieve every row by knowing the number of rows in the DB, what I've tried is this:

public CategoriesItem returnCategory(int index){
        SQLiteDatabase db = this.getWritableDatabase();
        Cursor mRow= db.rawQuery("select * from " + TABLE_CATEGORY + " LIMIT 0, " + index, null);
        if (null!=mRow) {
            mRow.moveToNext();
            item.auto_id = Integer.toString(mRow.getInt(mRow.getColumnIndex(KEY_ID)));
            item.title = mRow.getString(mRow.getColumnIndex(KEY_CATEGORY_NAME));
            item.subTitle = mRow.getString(mRow.getColumnIndex(KEY_CATEGORY_DESCRIPTION));
        }

        return item;
    }

The problem is that it returns only the first row, everytime even if the index is not 1. What is wrong with the query? How can I get a single row values by knowing the amount of rows in the DB?

You don't have a WHERE in your SQL... What does `"LIMIT 0, " + index` do? — OneCricketeer, Jan 03 '16 at 12:51
@cricket_007 I just have the amount of rows in the DB, then I can the returnCategory function N times in order to retrieve every single row of the db, but I can't understand how to get one by one — LS_, Jan 03 '16 at 12:53
It is not answer for your question, but I think it's worth to add here - **Always** close `Cursor` when you finish your operations. Wrap your code in `try`-`catch`-`finally` and place `mRow.close();` in `finally` block. — Damian Kozlak, Jan 03 '16 at 12:53
If you call this function N times, you will be running the same query N times and only retrieving the top result. You need to **move the cursor** `index` times. — OneCricketeer, Jan 03 '16 at 12:55
@cricket_007 I've closed the cursor and I've edited the query, i've used limit 1 and offset index, now it seems to work — LS_, Jan 03 '16 at 12:58
To get the total number of records (rows) within a table: "SELECT COUNT(*) FROM table_name;". To get all records: "SELECT * FROM tablename WHERE recordIDFieldName <> 0;" — DevilsHnd - 退職した, Jan 03 '16 at 13:50

score 1 · Answer 1 · answered Jan 03 '16 at 12:57

1

This seem to work, I'll do some test but this should be the solution:

Cursor mRow= db.rawQuery("select * from " + TABLE_CATEGORY + " LIMIT 1 OFFSET " + index, null);

answered Jan 03 '16 at 12:57

LS_

6,763
9
52
88

score 0 · Answer 2 · edited May 23 '17 at 12:04

0

What you probably want to do is iterate over the Cursor, you can then use the data from every single row. What's the best way to iterate an Android Cursor?

edited May 23 '17 at 12:04

Community

1
1

answered Jan 03 '16 at 12:57

fweigl

21,278
20
114
205

how to get every row from SQLite from a function that returns only one row at a time

2 Answers2