1

I am running this function:

public int getSubjectId(int level, String subject){
    SQLiteDatabase db = this.getWritableDatabase();
    Cursor subjectCursor = db.rawQuery("select * from " + SUBJECTS_TABLE_NAME + " where "+ SUBJECTS_COL_2 + " = " + subject + " and " + SUBJECTS_COL_3 + "="+level,null);
    ArrayList<Integer> mArrayList = new ArrayList<Integer>();
    for(subjectCursor.moveToFirst(); !subjectCursor.isAfterLast(); subjectCursor.moveToNext()) {
        mArrayList.add(subjectCursor.getInt(0));
    }
    return mArrayList.get(0);
}

in an attempt to return the id of the first item in the table where both column 2 and 3 match the function arguments. However when it does run I get an error message as the function is searching for a column with the name of the subject inputted and not the row containing that value.

I get the error: 

Error Code : 1 (SQLITE_ERROR)
Caused By : SQL(query) error or missing database.
(no such column: Maths (code 1):

Normally I would fix this problem using a prepared statement, but I have read that they should not be used when you are trying to return a value, which in this case I am as I want to return the ArrayList. Perhaps I have misinterpreted and prepared statements are ok in this situation, but either way I would appreciate any help finding the best solution to the error I am getting.

Thanks

EDIT:

SQLiteDatabase db = this.getWritableDatabase();
    String sql = "select * from " + SUBJECTS_TABLE_NAME + " where "+ SUBJECTS_COL_2 + " =?" + " and " + SUBJECTS_COL_3 + "=? ";
    SQLiteStatement statement = db.compileStatement(sql);
    statement.bindString(1, subject);
    statement.bindString(2, level);

    Cursor subjectCursor = statement.???();
Roonil
  • 486
  • 1
  • 4
  • 13
  • 1
    Do use prepared statements, don't use `select *`. What does `subjectCursor.getInt(0)` even mean here? Which column are you actually accessing? – Tim Biegeleisen Jan 14 '18 at 08:07
  • 4
    Where did you read that prepared statements should not be used when you want to return a value? – RealSkeptic Jan 14 '18 at 08:07
  • `subjectCursor.getInt(0)`: column numbers start at 1 not 0 (yes, confusing, but that's how it is). – Henry Jan 14 '18 at 08:17
  • @TimBiegeleisen Ok I probably meant getInt(1), sorry about that. How would I go about executing the prepared statement in this case? – Roonil Jan 14 '18 at 08:19
  • @RealSkeptic the second (better) answer to [this](https://stackoverflow.com/questions/433392/how-do-i-use-prepared-statements-in-sqlite-in-android/436162) question: – Roonil Jan 14 '18 at 08:21
  • 1
    You should list the columns you want to select in the actual select list. – Tim Biegeleisen Jan 14 '18 at 08:28
  • @Tim Biegeleisen, how do I assign to the cursor? I've updated my question with the code I have so far – Roonil Jan 14 '18 at 08:49
  • @Roonil First, [`PreparedStatement`](https://docs.oracle.com/javase/7/docs/api/java/sql/PreparedStatement.html) is great for both queries that return data, and manipulation statements (`INSERT`, `UPDATE`, `DELETE`) that don't return data. Second, you're not using `PreparedStatement` anywhere anyway, neither in the linked answer, which is using special SQLite classes. It certainly doesn't say not to use `PreparedStatement`, it says not to use `SQLiteStatement`. The link also shows how to do queries using `db.query(...)`, so why haven't you tried that? You're the one who linked to the answer. – Andreas Jan 14 '18 at 08:50
  • 1
    Hi @Andreas, I'm really sorry but I don't know what you mean. I gather that I've misinterpretted what I read that's fine, but the link I commented goes to a question and answer thread about using prepared statements so I was trying to figure out how to get those to work. Is there a reason I should be using db.query instead? My question was about the best way to go about this and I'm still unsure on that. – Roonil Jan 14 '18 at 08:54
  • Please don't confuse the SQLite-specific [`SQLiteStatement`](https://developer.android.com/reference/android/database/sqlite/SQLiteStatement.html) class with the JDBC generic [`PreparedStatement`](https://docs.oracle.com/javase/7/docs/api/java/sql/PreparedStatement.html) class. `SQLiteStatement` is *not* a `PreparedStatement`, and it *"cannot return multiple rows or columns, but single value (1 x 1) result sets are supported"*. `PreparedStatement` can be used for all SQL statements. – Andreas Jan 14 '18 at 08:56
  • @Andreas ok I'm sorry, I didn't know I was dealing with 2 different things here. The answer said prepared SQL statement so I just went with that. To clarify, is there a certain way that it is best for me to do this? – Roonil Jan 14 '18 at 08:58
  • Do what? Execute a query? Then yes, read that [second answer you linked to](https://stackoverflow.com/a/29797229/5221149). Read *all* of it. You'll find the part about queries at the end. It even ends with a link to more information. – Andreas Jan 14 '18 at 09:00
  • I just meant a best way to go about what I said I was trying to achieve in my question, but I'll have a read about using a query. Thanks for your help. – Roonil Jan 14 '18 at 09:01

0 Answers0