Program wont pull more rows than one from database

Question

I have problem with pulling more than one row of info from database. I create and fill database like this:

String KREIRAJ_TABELU = "CREATE TABLE " + IME_TABELE + "("
        + ID + " INTEGER PRIMARY KEY," + PITANJE + " TEXT,"
        + PRVI_NETACAN + " TEXT," + DRUGI_NETACAN + " TEXT," + TRECI_NETACAN + " TEXT," + TACAN_ODGOVOR + " TEXT" + ")";
        db.execSQL(KREIRAJ_TABELU);
        String dodajPitanjaIOdgovore = "INSERT INTO pitanja (ID,PITANJE,PRVI_NETACAN,DRUGI_NETACAN,TRECI_NETACAN,TACAN_ODGOVOR)\n" +
                "VALUES (0,\"Ovo je prvo pitanje:\",\"Netacan odgovor\",\"Netacan odgovor\",\"Netacan odgovor\",\"Tacan odgovor\");\n" +
                "\n" +
                "INSERT INTO pitanja (ID,PITANJE,PRVI_NETACAN,DRUGI_NETACAN,TRECI_NETACAN,TACAN_ODGOVOR)\n" +
                "VALUES (1,\"Ovo je drugo pitanje:\",\"Netacan odgovor\",\"Netacan odgovor\",\"Netacan odgovor\",\"Tacan odgovor\");\n" +
                "\n" +
                "INSERT INTO pitanja (ID,PITANJE,PRVI_NETACAN,DRUGI_NETACAN,TRECI_NETACAN,TACAN_ODGOVOR)\n" +
                "VALUES (2,\"Ovo je trece pitanje:\",\"Netacan odgovor\",\"Netacan odgovor\",\"Netacan odgovor\",\"Tacan odgovor\");\n" +
                "\n" +
                "INSERT INTO pitanja (ID,PITANJE,PRVI_NETACAN,DRUGI_NETACAN,TRECI_NETACAN,TACAN_ODGOVOR)\n" +
                "VALUES (3,\"Ovo je cetvrto pitanje:\",\"Netacan odgovor\",\"Netacan odgovor\",\"Netacan odgovor\",\"Tacan odgovor\");\n" +
                "\n" +
                "INSERT INTO pitanja (ID,PITANJE,PRVI_NETACAN,DRUGI_NETACAN,TRECI_NETACAN,TACAN_ODGOVOR)\n" +
                "VALUES (4,\"Ovo je peto pitanje:\",\"Netacan odgovor\",\"Netacan odgovor\",\"Netacan odgovor\",\"Tacan odgovor\");\n" +
                "\n" +
                "INSERT INTO pitanja (ID,PITANJE,PRVI_NETACAN,DRUGI_NETACAN,TRECI_NETACAN,TACAN_ODGOVOR)\n" +
                "VALUES (5,\"Ovo je sesto pitanje:\",\"Netacan odgovor\",\"Netacan odgovor\",\"Netacan odgovor\",\"Tacan odgovor\");\n" +
                "\n" +
                "INSERT INTO pitanja (ID,PITANJE,PRVI_NETACAN,DRUGI_NETACAN,TRECI_NETACAN,TACAN_ODGOVOR)\n" +
                "VALUES (6,\"Ovo je sedmo pitanje:\",\"Netacan odgovor\",\"Netacan odgovor\",\"Netacan odgovor\",\"Tacan odgovor\");\n" +
                "\n" +
                "INSERT INTO pitanja (ID,PITANJE,PRVI_NETACAN,DRUGI_NETACAN,TRECI_NETACAN,TACAN_ODGOVOR)\n" +
                "VALUES (7,\"Ovo je osmo pitanje:\",\"Netacan odgovor\",\"Netacan odgovor\",\"Netacan odgovor\",\"Tacan odgovor\");\n" +
                "\n" +
                "INSERT INTO pitanja (ID,PITANJE,PRVI_NETACAN,DRUGI_NETACAN,TRECI_NETACAN,TACAN_ODGOVOR)\n" +
                "VALUES (8,\"Ovo je deveto pitanje:\",\"Netacan odgovor\",\"Netacan odgovor\",\"Netacan odgovor\",\"Tacan odgovor\");\n" +
                "\n" +
                "INSERT INTO pitanja (ID,PITANJE,PRVI_NETACAN,DRUGI_NETACAN,TRECI_NETACAN,TACAN_ODGOVOR)\n" +
                "VALUES (9,\"Ovo je deseto pitanje:\",\"Netacan odgovor\",\"Netacan odgovor\",\"Netacan odgovor\",\"Tacan odgovor\");";
        db.execSQL(dodajPitanjaIOdgovore);

For getting database values I created this method:

public podaciOPitanjima getPitanje(int id){
    SQLiteDatabase db = this.getReadableDatabase();
    Cursor cursor = db.query(IME_TABELE, new String[]{ID, PITANJE, PRVI_NETACAN, DRUGI_NETACAN, TRECI_NETACAN, TACAN_ODGOVOR}, ID + "=?", new String[]{String.valueOf(id)}, null, null, null, null);
    podaciOPitanjima podaci = null;
    if (cursor.moveToFirst()) {
        podaci = new podaciOPitanjima(Integer.parseInt(cursor.getString(0)), cursor.getString(1), cursor.getString(2), cursor.getString(3), cursor.getString(4), cursor.getString(5));
    }
    return podaci;
}

And when I want to pull all 10 rows I use this piece of code:

private static List<podaciOPitanjima> pitanja = new ArrayList<podaciOPitanjima>();
    for(int i = 0; i < 10; i++){
        testPitanje = db.getPitanje(i);
        if(testPitanje != null){
            pitanja.add(testPitanje);
        }
    }

It is working and it is pulling row of first ID and putting into my dynamical array, but just first row, not all 10. Why?

Please show a complete example. All code must be in a method in a class just like in your real code. Also be sure to include all variable declarations. — Code-Apprentice, Mar 07 '17 at 12:56
I suggest using a Cursor directly rather than putting your data into a List. If you display the data in a ListView, you should use a CursorAdapter rather than an ArrayAdapter. — Code-Apprentice, Mar 07 '17 at 13:01
this is bad approach to load records into an array, why keep querying database for 1 record 10 times, get them all ,and fill the whole array in 1 hit! — Yazan, Mar 07 '17 at 13:14
Can you advise me piece of code for that? I'm not quite sure how to implement that — someRandomSerbianGuy, Mar 07 '17 at 13:37
for that, there is a lot of questions and sample code about it, like this http://stackoverflow.com/questions/1354006/how-can-i-create-a-list-array-with-the-cursor-data-in-android, but the **good** point is what CL posted below, your code to fill array should work OK, unless there is only 1 row in the table!!, that's what CL explains in the answer below :) — Yazan, Mar 07 '17 at 14:37

score 0 · Answer 1 · edited May 23 '17 at 11:46

0

The documentation of execSQL() says:

Execute a single SQL statement

So you have to insert multiple rows with a single statement, or call execSQL() for each statement. (Or call insert() instead, or ship your app with a pre-filled database file.)

edited May 23 '17 at 11:46

Community

1
1

answered Mar 07 '17 at 13:55

CL.

158,085
15
181
214

Do not change the question after is has been answered. If you want to know why that code does not get executed, ask a new question (which will be closed as a duplicate of http://stackoverflow.com/questions/21881992/when-is-sqliteopenhelper-oncreate-onupgrade-run). – CL. Mar 07 '17 at 14:41

Program wont pull more rows than one from database

1 Answers1