3

I am trying to query my Android SQLite database using the following INTERSECT query, but it crashes. The two select queries work fine on their own and they are definitely producing a matched result, so I can't understand why the INTERSECT statement will not work:

SQLiteDatabase db = (new DatabaseHelper(this)).getWritableDatabase();
Cursor cursor = db.rawQuery("SELECT tblsyndromes._id, synname" +
"  FROM tblsyndromes JOIN tblsymsynlink ON tblsymsynlink.synId =      tblsyndromes._id" +
" WHERE tblsymsynlink.symId = "+intCondition+" " +
" INTERSECT SELECT tblsyndromes._id, synname FROM tblsyndromes JOIN synconlink" +
" ON synconlink.synId = tblsyndromes._id" +
" WHERE synconlink.conId = "+intCondition2+"", null);

New Code that I am using - 

String sqlString = "SELECT tblsyndromes._id, tblsyndromes.synname FROM tblsyndromes       JOIN synconlink ON synconlink.synId = tblsyndromes._id WHERE synconlink.conId = 55 INTERSECT SELECT tblsyndromes._id, tblsyndromes.synname FROM tblsyndromes JOIN tblsymsynlink ON tblsymsynlink.synId = tblsyndromes._id WHERE tblsymsynlink.symId = 136";

SQLiteDatabase db = (new DatabaseHelper(this)).getWritableDatabase();

Cursor cursor = db.rawQuery(sqlString,null);

ArrayList<String> mArrayList = new ArrayList<String>();
cursor.moveToFirst();
while(!cursor.isAfterLast()) {
     mArrayList.add(cursor.getString(cursor.getColumnIndex("synname")));
     cursor.moveToNext();
}

I have tested the sql repeatedly using sqlite3 and it works - both the select queries work independently within android but as soon as I try to use INTERSECT I get a crash - I am now getting the the following error

Bad request for field slot 0,-1. numRows = 3, numColumns = 2

user985869
  • 93
  • 1
  • 8
  • what does the error say? – zapl Mar 19 '12 at 21:20
  • Sorry for the delay the error says 03-26 20:45:45.949: INFO/Database(31208): sqlite returned: error code = 0, msg = Recovered 63 frames from WAL file /data/data/
    03-26 20:45:45.999: INFO/Database(31208): sqlite returned: error code = 1, msg = near "synconlink": syntax error     – user985869 Mar 26 '12 at 20:10
  • I don't see any error there. The error says you wrote something wrong near "synconlink" which should be the one after the JOIN. Have you tried to execute the same query via sqlite directly? Maybe that gives you more insight. – zapl Mar 26 '12 at 20:55
  • Hi still plugging away at this with no joy except that I have established that the sql is fine - it is the cursor that does not seem able to deal with the intersect result, I have tried many different cursor methods and listviews and every time it fails. Do you have any examples of an android sqlite intersect query implementing preferably a listview or even just any cursor that will display the resulting data. – user985869 Apr 04 '12 at 06:22
  • Cursor just wraps the sqlite query result and should work with anything sqlite gives you. A `db.rawQuery` is pretty much just forwarded to sqlite3 - there should be nothing that can be wrong here. But if you get an sqlite syntax error then something must be wrong with the query. Check your query with sqlite3 (or some gui tool like [sqliteman](http://sourceforge.net/projects/sqliteman/files/sqliteman/1.2.2/)) on the database that you are using (if you run it on the emulator you can get it via "adb pull /data/data/your.package/databases/your.db" – zapl Apr 04 '12 at 13:11
  • Still going on this and I have done as you said - my query is 100% correct using sqlite3 - I have tried a slightly different approach which I have added to my original post. I get the feeling that it may be something to do with the _id column - I am desperate to resolve this – user985869 Apr 08 '12 at 20:04

1 Answers1

-1
public Long insert_todoinfo(String a, String b, String c, String d,
            String e, String f, String g) {
        // TODO Auto-generated method stub
        ContentValues con = new ContentValues();
        con.put("title", a);
        con.put("description", b);
        con.put("category", e);
        con.put("due_date", c);
        con.put("alarm_time", d);
        con.put("alarm_set", f);
        con.put("priority", g);
        con.put("parform", "false");
        return mDb.insert(DATABASE_TABLE_TODO_LIST, null, con);
    }
Android
  • 1,417
  • 9
  • 11