Tomzi Tomzi - 16 days ago 6
Android Question

Cursor returning incorrect values

I am creating an application for monitoring personal finances. The user creates accounts, categories and transactions. The

dBHelper.java
class creates the database as shown below:

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 function create the three databases. The problem occurs when I try to get the list of all the transactions or any other report configured by the 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 a
Cursor
to show the list of accounts before and it worked perfect. I don't know why it isn't working anymore. I didn't use foreign keys in the tables or anything like that and it should be working. If I execute the code I will get the wrong values and plenty of errors. I get the errors on these specific 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"));


The application will run and I will get the information printed. All of the information printed is correct. The string
racun
is same as the string
kategorija
when they shouldn't be. I am unsure on why I keep getting the wrong values from the
racun
column. I tested my SQL using SQLite 3 with the
adb
shell and it was working fine. Here are the errors from the LogCat:

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 there something wrong because I used the alias in SQL? This is should be 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>


This was the rest I received. Any suggestions in how to solve this?

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(...)