Forset1 Forset1 - 6 months ago 10
SQL Question

I'm getting 0 results in this query. What am I doing wrong? (Android)

I am using SQLite on Android. These are my three tables:

String query = "create table " + TABLE_PERSON + " (" +
PERSON_COL_ID + " integer PRIMARY KEY AUTOINCREMENT, " +
PERSON_COL_NAME + " text, " +
PERSON_COL_SEX + " text CHECK (" + PERSON_COL_SEX + " IN ('M', 'F'))" +
");";
db.execSQL(query);

query = "create table " + TABLE_CONCEPT + " (" +
CONCEPT_COL_ID + " integer PRIMARY KEY AUTOINCREMENT, " +
CONCEPT_COL_NAME + " text, " +
CONCEPT_COL_DATE + " text " +
");";
db.execSQL(query);

query = "create table " + TABLE_DEBT + " (" +
DEBT_COL_ID + " integer PRIMARY KEY AUTOINCREMENT, " +
DEBT_COL_CONCEPT + " integer REFERENCES " + TABLE_CONCEPT + ", " +
DEBT_COL_CREDITOR + " integer REFERENCES " + TABLE_PERSON + ", " +
DEBT_COL_DEBTOR + " integer REFERENCES " + TABLE_PERSON + ", " +
DEBT_COL_ORIGAMT + " integer, " +
DEBT_COL_PAIDSOFAR + " integer, " +
DEBT_COL_DATEREPAID + " text" +
");";
db.execSQL(query);


After inserting 3 persons, 2 concepts and 1 debt, I am getting 0 results after making the following query:

Cursor cur = databaseHelper.getDebtsByConcept(0);


Where the query carried out is the following:

public Cursor getDebtsByConcept(int conceptID) {
String query = "select con.name, per1.name, per2.name, deb.origamt, deb.paidsofar, deb.daterepaid" +
" from " +
TABLE_PERSON + " per1, " + TABLE_PERSON + " per2, " + TABLE_CONCEPT + " con, " + TABLE_DEBT + " deb" +
" where deb.concept = " + conceptID +
" and con.id = deb.concept " +
" and per1.id = deb.creditorID" +
" and per2.id = deb.debtorID;";

Cursor res = db.rawQuery(query, null);
return res;
}

Answer

there will not be any entry in database with conecptID with value 0, reason for the same is that you have declared conceptID in concept table as Integer Auto Increment so when you insert first record value will be conceptId value will be 1,

Can you try passing 1 in your query, and just to check initially try and fetch all the records from database using select * from ....

Comments