The task I've been given is to write a query to return the number of orphaned rows. I have achieved this but another task is to then not use the rawQuery method to achieve the same result using the query method.
The issue is that I get java.lang.IllegalStateException: Invalid tables
The tables, there are 3 are
The SQL to create and to put rows into the tables, including some orphans is like
CREATE TABLE parent(_id INTEGER PRIMARY KEY,parentname TEXT);
CREATE TABLE child(_id INTEGER PRIMARY KEY,childname TEXT, childtoparentlink INTEGER);
CREATE TABLE friend(_id INTEGER PRIMARY KEY,friendname TEXT, friendtochildlink INTEGER);
INSERT INTO parent VALUES(null,'Parent A');
INSERT INTO parent VALUES(null,'Parent B');
INSERT INTO child VALUES(null,'Child A',1);
INSERT INTO child VALUES(null,'Child B',2);
INSERT INTO child VALUES(null,'Child X',10); -- orphan
INSERT INTO friend VALUES(null,'Friend A',1);
INSERT INTO friend VALUES(null,'Friend B',2);
INSERT INTO friend VALUES(null,'Friend X',100); -- orphan
The query that works and gives the right values when using rawQuery is
SELECT
(
SELECT count() FROM child
LEFT JOIN parent ON child.childtoparentlink = parent._id
WHERE parent.parentname IS NULL
) AS child_mismatches,
(
SELECT count() FROM friend
LEFT JOIN child ON friend.friendtochildlink = child._id
WHERE child.childname IS NULL
) AS friend_mismatches
My actual code is :-
public ArrayList<String> checkLinkIntegrity() {
ArrayList<String> return_value = new ArrayList<>();
String suffix = "_mismatches";
String child_result_cl = TB_CHILD + suffix;
String sq_child_mismatches = "(SELECT count() FROM " +
TB_CHILD +
" LEFT JOIN " + TB_PARENT +
" ON " + TB_CHILD + "." + CL_CHILDTOPARENTLINK + " = " +
TB_PARENT + "." + CL_PARENTID +
" WHERE " + TB_PARENT + "." + CL_PARENTNAME + " IS NULL)" +
" AS " + child_result_cl;
String friend_result_cl = TB_FRIEND + suffix;
String sq_friend_mismatches = "(SELECT count() FROM " +
TB_FRIEND +
" LEFT JOIN " + TB_CHILD +
" ON " + TB_FRIEND + "." + CL_FRIENDTOCHILDLINK + " = " +
TB_CHILD + "." + CL_CHILD_ID +
" WHERE " + TB_CHILD + "." + CL_CHILDNAME + " IS NULL)" +
" AS " + friend_result_cl;
String full_query = "SELECT " + sq_child_mismatches + "," + sq_friend_mismatches;
SQLiteDatabase db = this.getWritableDatabase();
Cursor csr;
Log.d("RAWQUERYSQL",full_query);
csr = db.rawQuery(full_query,null);
return_value.addAll(dumpCursorToStringArrayList(csr,"RAWQUERY"));
// Fails invalid table
csr = db.query(null,new String[]{sq_child_mismatches,sq_friend_mismatches},null,null,null,null,null);
return_value.addAll(dumpCursorToStringArrayList(csr,"SECONDTRY"));
csr.close();
return return_value;
}
and the dumpCursortoStringArrayList method is :-
private ArrayList<String> dumpCursorToStringArrayList(Cursor csr, String tablename) {
ArrayList<String> rv = new ArrayList<>();
int original_position = csr.getPosition();
csr.moveToPosition(-1);
rv.add("Table: " + tablename);
while (csr.moveToNext()) {
rv.add("\tRow # " + String.valueOf(csr.getPosition() + 1));
for (String column: csr.getColumnNames()) {
rv.add("\t\tColumn: " + column + "\tvalue is: \t" + csr.getString(csr.getColumnIndex(column)));
}
}
csr.moveToPosition(original_position);
return rv;
}
I get the same error if I try "" instead of null e.g.
If I use only the rawQuery I get
04-22 07:07:33.914 6271-6271/s.q001 I/RESULTS: Table: RAWQUERY
04-22 07:07:33.914 6271-6271/s.q001 I/RESULTS: Row # 1
04-22 07:07:33.914 6271-6271/s.q001 I/RESULTS: Column: child_mismatches value is: 1
04-22 07:07:33.914 6271-6271/s.q001 I/RESULTS: Column: friend_mismatches value is: 1
This is from using
ArrayList<String> results = DBOpenHelper.checkLinkIntegrity();
for (String s : results) {
Log.i("RESULTS",s);
}
How can I run the query with the query method instead of the rawQuery method, to get the better marks?
Your issue is that the query method expects a table as it then generates SQL as per
SELECT your_columns FROM the_table;
As there is no table it issues the Invalid Table exception.
You have to provide something that will satisfy the FROM clause, it cannot be nothing.
You can get around this in a few ways, which I guess is what the homework is trying to get you to ascertain/explore.
You could supply one of the tables that exist e.g. use
csr = db.query(null,new String[]{sq_child_mismatches,sq_friend_mismatches},null,null,null,null,null,"1");
or as FROM can be a subquery (see diagram) you could use a subquery e.g. one that you have
So you could use :-
csr = db.query(
sq_child_mismatches, //<<<<<<<<<< the fake subquery
new String[]{
sq_child_mismatches,
sq_friend_mismatches
},
null ,null,null,null,null
);