Tomzi Tomzi - 29 days ago 15
Android Question

Android: Sqlite - cursor returning wrong values

I have application for monitoring personal finances. There I have accounts, categories and transactions.

Now I will show you how I created databases.

dBHelper.java
onCreate()
:

public void onCreate(SQLiteDatabase db) {
//executes only once
//create tables
Log.v("TAG", "creating table:" +racuniTable);
String sql = "create table " +racuniTable+" (" +colID+ " INTEGER PRIMARY KEY AUTOINCREMENT, "
+colName+ " TEXT, " +colValue+ " REAL, " +colAccType+ " TEXT);";

Log.v("TAG", "creating table:" +transakcijeTable);
String sql1 = "create table " +transakcijeTable+" (" +colTransID+ " INTEGER PRIMARY KEY AUTOINCREMENT, "
+colTransDate+ " DATE, " +colAmount+ " REAL, "
+colIDracuni+ " INTEGER, " +colIDkategorije+ " INTEGER);";

Log.v("TAG", "creating table:" +kategorijeTable);
String sql2 = "create table " +kategorijeTable+" (" +colCatID+ " INTEGER PRIMARY KEY AUTOINCREMENT, " +colCatName+ " TEXT);";
Log.v("TAG", "adding account:");

db.execSQL(sql);
db.execSQL(sql1);
db.execSQL(sql2);
}


This will create my three databases that I need. The problem appears when I try to get list of all transactions or any other report configured by sql sentence.

private void getTransactions(){

try {
DbHelper dbHelper = new DbHelper(this.getApplicationContext());
newDB = dbHelper.getWritableDatabase();
m_transaction = new ArrayList<transaction>();
Cursor c = newDB.rawQuery("SELECT rac.Ime, trans.Znesek, trans.Datum, kate.Ime FROM Racuni AS rac, " + "Transakcije AS trans, Kategorije
as kate WHERE trans.ID_racuna=rac._id AND trans.ID_kategorije=kate._id", null);

if (c != null ) {
if (c.moveToFirst()) {
do {
String racun = c.getString(c.getColumnIndex("rac.Ime"));
double znesek = c.getDouble(c.getColumnIndex("trans.Znesek"));
String datum = c.getString(c.getColumnIndex("trans.Datum"));
String kategorija = c.getString(c.getColumnIndex("kate.Ime"));

transaction t1 = new transaction();
t1.setracun(racun);
t1.setznesek(znesek);
t1.setdatum(datum);
t1.setkategorija(kategorija);

m_transaction.add(t1);

}while (c.moveToNext());
}
}
} catch (SQLiteException se ) {
Log.e(getClass().getSimpleName(), "Could not create or Open the database");
}


I used
Cursor
to show list of accounts before and it worked perfect. Dunno where is the problem. I didn't use foreign keys in tables or anything and in my mind this should work. If I run this I will get wrong values and plenty of errors.

I get errors on those lines:

String racun = c.getString(c.getColumnIndex("rac.Ime"));
double znesek = c.getDouble(c.getColumnIndex("trans.Znesek"));
String datum = c.getString(c.getColumnIndex("trans.Datum"));
String kategorija = c.getString(c.getColumnIndex("kate.Ime"));


Application will run and I will get info printed. All info printed is correct only
String
racun is same as String kategorija which shouldn't be. Why I'm getting wrong data to String racun?

I tested my
SQL
with
sqlite3
in
adb
shell and its alright.

Errors in
logcat
are:

08-19 17:48:16.279: ERROR/Cursor(625): requesting column name with table name -- rac.Ime

08-19 17:48:16.331: ERROR/Cursor(625): requesting column name with table name -- trans.Znesek

08-19 17:48:16.359: ERROR/Cursor(625): requesting column name with table name -- trans.Datum

08-19 17:48:16.439: ERROR/Cursor(625): requesting column name with table name -- kate.Ime


Is something wrong because I used alias in
SQL
?

This is what I should get printed

SELECT rac.Ime, trans.Znesek, trans.Datum, kate.Ime FROM Racuni AS rac, Transakcije AS trans, Kategorije as kate WHERE trans.ID_racuna=rac._id AND trans.ID_kate
gorije=kate._id;
Tomaz|50.0|3911-08-18|placa
Tom|33.0|3912-07-18|placa
Tomaz|70.0|3800-07-17|zapravlanje
Tom|69.0|2010-07-17|placa
Tomaz|30.0|2011-08-18|placa
Tom|30.0|2011-08-12|zapravlanje
Tom|50.0|2011-08-01|placa
sqlite>


And this is what I get: picture.

Please help.
Thanks.

Answer

Change

SELECT rac.Ime, trans.Znesek, trans.Datum, kate.Ime FROM ...

to:

SELECT rac.Ime racun, trans.Znesek, trans.Datum, kate.Ime kategorja FROM ...

and then change respectively

c.getColumnIndex(...)