android sqlite: getting wrong values

Question

Currently I got a lot of troubles in android with sqlite databases. This time, it's really weird.

Everytime I enter a certain Activity, I want to display the latest three entries, like "team 1 vs. team 2 goals - goals". Bizarrely, the database is returning wrong values. Let's start with the current database content:

enter image description here

important for my question here are columns "id", "goals1" and "goals2". Now as you see, the latest three games are "4: dfsadf vs. asf 3-2", "3: df vs. as 3-2" and "2: dfa vs. dfas 2-0". In my Activity, following is displaid:

enter image description here

As you see, the goals of the away team goals are wrong (and it seems the entry's ID is set as away goals). I set the text of these three buttons like this:

entries = datasource.getLatestFootballEntry(challengeName);
button = (Button) findViewById(R.id.bt_overview_latest1);
            entry = entries.elementAt(0);
            buttonText = entry.date + ": " + entry.team1 + " vs. " + entry.team2 + " " + entry.goals1 + "-" + entry.goals2;
            button.setText(buttonText);
            button.setClickable(true);

            button = (Button) findViewById(R.id.bt_overview_latest2);
            entry = entries.elementAt(1);
            buttonText = entry.date + ": " + entry.team1 + " vs. " + entry.team2 + " " + entry.goals1 + "-" + entry.goals2;
            button.setText(buttonText);
            button.setClickable(true);

            button = (Button) findViewById(R.id.bt_overview_latest3);
            entry = entries.elementAt(2);
            buttonText = entry.date + ": " + entry.team1 + " vs. " + entry.team2 + " " + entry.goals1 + "-" + entry.goals2;
            button.setText(buttonText);
            button.setClickable(true);

Here I get the specific entries:

public Vector<FootballEntry> getLatestFootballEntry(String challengeName){
        Vector<FootballEntry> entries = new Vector<FootballEntry>();
        Cursor cursor = database.query(CustomSQLiteHelper.TABLE_FOOTBALL, allColumns2, null, null, null, null, null);
        cursor.moveToLast();
        int i=0;
        while(i<3 && !cursor.isBeforeFirst()){
            if(checkIfCorrectChallengeName(cursor, challengeName)){
                entries.add(cursorToFootballEntry(cursor));
                i++;
            }
            cursor.moveToPrevious();
        }
        return entries;
    }

Now the strange part comes. When I do following changes in the method where I set the text on buttons, everything is fine.

button = (Button) findViewById(R.id.bt_overview_latest1);
            datasource.open();
            entry = datasource.getSpecificFootballEntry(entries.elementAt(0).id);
            datasource.close();
            buttonText = entry.date + ": " + entry.team1 + " vs. " + entry.team2 + " " + entry.goals1 + "-" + entry.goals2;
            button.setText(buttonText);
            button.setClickable(true);

now displaid

enter image description here

This is my cursorToFootballEntry method

private FootballEntry cursorToFootballEntry(Cursor cursor){
    FootballEntry entry = new FootballEntry();
    entry.id = cursor.getLong(0);
    entry.challengeName = cursor.getString(1);
    entry.date = cursor.getString(2);
    entry.home = cursor.getInt(3);
    entry.platform = cursor.getString(4);
    entry.team1 = cursor.getString(5);
    entry.stars1 = cursor.getDouble(6);
    entry.team2 = cursor.getString(7);
    entry.stars2 = cursor.getDouble(8);
    entry.goals1 = cursor.getInt(9);
    Log.i("dfasdf", "IN CURSOR TO FOOENRTY  " + cursor.getInt(9) + "-" + cursor.getInt(10) + "   id: " + cursor.getLong(0));
    entry.goals2 = cursor.getInt(10);
    entry.finished = cursor.getInt(11);
    return entry;
}

As you see, I print some infos into the log (namely the two values of the team's goals). The Output is

09-29 22:50:18.191: I/dfasdf(7039): IN CURSOR TO FOOENRTY  3-4   id: 4
09-29 22:50:18.191: I/dfasdf(7039): IN CURSOR TO FOOENRTY  3-3   id: 3
09-29 22:50:18.201: I/dfasdf(7039): IN CURSOR TO FOOENRTY  2-2   id: 2
09-29 22:50:18.241: I/dfasdf(7039): IN CURSOR TO FOOENRTY  3-2   id: 4

So basically, the entry with ID 4 is read out two times, but there are two different away goal values. What can cause this? I mean, it's no difference whether I get 3 games at once or three times a single game, as long they are the same games, what obviously must be (seeing the id's). Getting a single game returns always the right values. The same thing happens, if I get all games (needed in a different Activity). In the database creation declaration, there is only one value set to "autoincrement", and thast the _id, nothing else.

Now to "prove" this, I entered a new entry, score 1-0: following is displaid (with the second variant of setting the text, means the first will be correct)

enter image description here

Log

09-29 23:02:13.281: I/dfasdf(7039): IN CURSOR TO FOOENRTY  1-5   id: 5
09-29 23:02:13.291: I/dfasdf(7039): IN CURSOR TO FOOENRTY  3-4   id: 4
09-29 23:02:13.291: I/dfasdf(7039): IN CURSOR TO FOOENRTY  3-3   id: 3
09-29 23:02:13.311: I/dfasdf(7039): IN CURSOR TO FOOENRTY  1-0   id: 5

EDIT: of course a solution would be to get only the ID's of the latest games, and the get them seperately, but the motivation of my question is more to understand why this can happen.

Since I'm very new to databases, which query exactly do you mean? As I understand it by now, by working with extension of SQLiteOpenHelper, with querys I put the cursor to a specific position (and then I'm moving it "by hand", like "moveToNext() as long xy"). — Valentino Ru
– Valentino Ru, Commented Sep 29, 2012 at 23:27
can you post the queries, like the part when you do the SELECT or the likes (whichever did you use, contentresolver or wat) — lemoncodes
– lemoncodes, Commented Sep 30, 2012 at 0:28

CL. · Accepted Answer · 2012-10-01 10:29:20Z

2

Moving backwards through the cursor does not seem to work.

However, there is no guarantee that records are returned in any particular order if you don't explicitly specify an ordering, so it would be altogether easier to modify the query so that it returns exactly the records you want:

cursor = database.query(CustomSQLiteHelper.TABLE_FOOTBALL,
                        allColumns2,
                        null, null,
                        null, null,
                        "id DESC",  // return largest id values first
                        "3");       // return no more than 3 records
cursor.moveToFirst();
while (!cursor.isAfterLast()) {
    ...
    cursor.moveToNext();
}

edited Oct 1, 2012 at 10:29

answered Sep 30, 2012 at 9:47

CL.

182k18 gold badges241 silver badges282 bronze badges

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

6 Comments

Valentino Ru Over a year ago

I'm getting an java.lang.IllegalArgumentException: invalid LIMIT clauses: LIMIT 3

CL. Over a year ago

Edited; the LIMIT is automatically inserted by query().

kishore Over a year ago

How to specify skip in the above syntax

CL. Over a year ago

@kishore Not at all; use rawQuery.

kishore Over a year ago

@CL hey no need of raw query i specified 3,2 and it worked where 3 is skip value and 2 is limit value.

|

Collectives™ on Stack Overflow

android sqlite: getting wrong values

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related