CODEWITHSUNDEEP
javaandroidsqlitecursor
Jacek Kwiecień
Jacek Kwiecień

Reputation: 12659

Wrong values returned from cursor

It look like complete paranormal activity to me. I got this little function converting database data into objects:

public List<BudgetSummary> getMonnthlyBalance() {
    List<BudgetSummary> list = new ArrayList<BudgetSummary>();

    DbAdapter db = new DbAdapter(context);
    SQLiteDatabase sqldb = db.open();
    MonthlyBalanceSqlSelect sql = new MonthlyBalanceSqlSelect();
    Cursor cursor = sql.getCursor(sqldb);

    while (cursor.moveToNext()) {
        list.add(new BudgetSummary(cursor));
    }

    cursor.close();
    db.close();
    return list;
}

MonthlyBalanceSqlSelect contains sql query, which I copied from the debugger to the Sqliteman. I have extracted database file from the app. Here are the results:

enter image description here

This looks perfect to me. Lets take a look at BudgetSummary constructor now. It's simple as wire.

public BudgetSummary(Cursor cursor) {
    incomes = cursor.getDouble(cursor.getColumnIndex("incomes"));
    expenses = cursor.getDouble(cursor.getColumnIndex("expenses"));     
}

I was step debugging with every iteration inside this constructor. It should correspond to the data presented on the picture but it doesn't...Here are results:

 1. incomes: 4732.0 - wrong   | expenses: -57.59 - wrong     | month and year correct
 2. incomes: 4657.0 - correct | expenses: -3714.96 - correct | month and year correct
 3. incomes: 708.0 - wrong    | expenses: -3383.03 - correct | month and year correct
 4. incomes: 5669.48 - wrong  | expenses: -5669.48 - wrong   | month and year correct
 5. incomes: 3278.5 - correct | expenses: -2685.91 - wrong   | month and year correct
 6. incomes: 4612.5 - wrong   | expenses: -2786.78 - wrong   | month and year correct
 and so on...

What sorcery is this? I really don't know... how some values could be correct and the others completly out of the blue?

Here is the SQL query. I strongly suspect that java sees it different way that Sqliteman accordting to this. Unfortunately I don't know how it should look.

select strftime('%m', b.date) as month , strftime('%Y', b.date) as year, total(case when b.value >= 0 then b.value else 0 end) as incomes, total(case when b.value < 0 then b.value else 0 end) as expenses from budget b, category c where b.category_id = c.id group by month, year order by year desc, month desc

Upvotes: 0

Views: 267

Answers (1)

Jon Skeet
Jon Skeet

Reputation: 1503669

I strongly suspect that the problem is you're assuming the same row ordering in Sqliteman and the Java code. Unless your SQL explicitly specifies the order, you shouldn't assume that it'll be the same. I suggest you include the month and year in your query, and include that in your BudgetSummary as well, at least for diagnostics.

Upvotes: 1

Related Questions