I checked the syntax of query and it is correct. Is it not possible to
execute cross-database query in Android?
Yes it is possible as the example included demonstrates.
I think that your issue is that you appear to be using the incorrect rawQuery method/signature.
instead of :-
Cursor cursor = sqLiteDatabase.rawQuery(selectQuery, "1","xyz");
I believe that you should be using :-
Cursor cursor = sqLiteDatabase.rawQuery(selectQuery, new String[]{"1","xyz"});
The former only supplying the 1 bind item as opposed to the two expected bind items (although what you are using doesn't even appear to be a valid signature for rawQuery)
However, I do suspect, from testing that you have additional issues with regard to the query. Here is a working example based upon your original post (i.e the attached databases are in fact the same databases, this shouldn't matter in principle though) :-
public class MainActivity extends AppCompatActivity {
public static final String DBNAME1 = "db1";
public static final String DBNAME2 = "db2";
public static final String DBNAME3 = "db3";
public static final String ID_COLUMN = "_id";
public static final String TABLENAME_BASE = "_table_main";
public static final String NAMECOLUMN_BASE = "_name";
public static final String DB2_COL_NAME = DBNAME2 + NAMECOLUMN_BASE;
public static final String EXTENDED_DB2TABLENAME = DBNAME2 + "." + DBNAME2 + TABLENAME_BASE;
public static final String EXTENDED_DB3TABLENAME = DBNAME3 + "." + DBNAME2 + TABLENAME_BASE;
public static final String DB2_FULL_IDCOL = EXTENDED_DB2TABLENAME + "." + ID_COLUMN;
public static final String DB2_FULL_NAMECOL = EXTENDED_DB2TABLENAME + "." + DBNAME2 + NAMECOLUMN_BASE;
public static final String DB3_FULL_IDCOL = EXTENDED_DB3TABLENAME + "." + ID_COLUMN;
public static final String DB3_FULL_NAMECOL = EXTENDED_DB3TABLENAME + "." + DBNAME2 + NAMECOLUMN_BASE;
private static final String[] DB1NAMES = new String[] {"Fred","Bert","Harry","Tom","Dick"};
private static final String[] DB2NAMES = new String[] {"Alan","George","Robert","Colin","Ian","John"};
String mDB1Path, mDB2path;
SQLiteDatabase mMaster;
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
mDB1Path = createDatbase(DBNAME1);
mDB2path = createDatbase(DBNAME2);
/* Just to add some data for 1st run
for (String s: DB2NAMES) {
insertRow(DBNAME1,s);
}
for (String s: DB1NAMES) {
insertRow(DBNAME2,s);
}
*/
// Open the main database and attach databases
mMaster = this.openOrCreateDatabase(mDB1Path, Context.MODE_PRIVATE,null);
String attachsql = "ATTACH DATABASE '" + mDB2path + "' AS " + DBNAME2;
String attachsql2 = "ATTACH DATABASE '" + mDB2path + "' AS " + DBNAME3;
mMaster.execSQL(attachsql);
mMaster.execSQL(attachsql2);
// Prepare first query between all 3 DB's
String sqlstr = "SELECT " +
DBNAME1 + TABLENAME_BASE + ".*, " +
DBNAME2 + "." + DBNAME2 + TABLENAME_BASE + "." + DB2_COL_NAME +
", " + DBNAME3 + "." + DBNAME2 + TABLENAME_BASE + "." + DB2_COL_NAME +
" FROM " + DBNAME1 + TABLENAME_BASE +
" JOIN " + DBNAME2 + "." + DBNAME2 + TABLENAME_BASE +
" ON " + DBNAME1 + TABLENAME_BASE + "." + ID_COLUMN +
" = " + DBNAME2 + "." + DBNAME2 + TABLENAME_BASE + "." + ID_COLUMN +
" JOIN " + DBNAME3 + "." + DBNAME2 + TABLENAME_BASE +
" ON " + DBNAME1 + TABLENAME_BASE + "." + ID_COLUMN +
" = " + DBNAME3 + "." + DBNAME2 + TABLENAME_BASE + "." + ID_COLUMN;
// Write query string to log
Log.d("SELECTSQL_1",sqlstr);
// Perform the query an write resultant data to the log
Cursor csr = mMaster.rawQuery(sqlstr,null);
while (csr.moveToNext()) {
String logdata = "Row = " + csr.getPosition();
for (int i=0; i < csr.getColumnCount(); i++) {
logdata = logdata + " Column = " + csr.getColumnName(i) +
" Value = " + csr.getString(i);
}
Log.d("CSRINFO",logdata);
}
// Arguments for 2nd query
String arg1 = "12";
String arg2 = "Fred";
// SQL for 2nd query including WHERE clause
String sqlstr2 = " SELECT " +
DB2_FULL_IDCOL + ", " +
DB2_FULL_NAMECOL + ", " +
DB3_FULL_IDCOL + ", " +
DB3_FULL_NAMECOL +
" FROM " + DBNAME2 + TABLENAME_BASE +
" JOIN " + DBNAME3 + "." + DBNAME2 + TABLENAME_BASE +
" ON " + DBNAME2 + "." + DBNAME2 + TABLENAME_BASE + "." + ID_COLUMN +
" = " + DB3_FULL_IDCOL +
" WHERE " +
DB3_FULL_IDCOL + "=? AND " +
DB3_FULL_NAMECOL + "=?";
// Log the query string
Log.d("SELECTSQL_2",sqlstr2);
// Perform 2nd query and log cursor result
Cursor csr2 = mMaster.rawQuery(sqlstr2,new String[]{arg1,arg2});
while (csr2.moveToNext()) {
String logdata = "Row = " + csr2.getPosition();
for (int i=0; i < csr2.getColumnCount(); i++) {
logdata = logdata + " Column = " + csr2.getColumnName(i) +
" Value = " + csr2.getString(i);
}
Log.d("CSRINFO",logdata);
}
}
// Used to create the two actual databases
private String createDatbase(String dbqualifier) {
SQLiteDatabase db = this.openOrCreateDatabase(dbqualifier,Context.MODE_PRIVATE,null);
String tblcrtstr = "CREATE TABLE IF NOT EXISTS " +
dbqualifier + TABLENAME_BASE +
"(" +
ID_COLUMN + " INTEGER PRIMARY KEY, " +
dbqualifier + NAMECOLUMN_BASE + " TEXT" +
")";
db.execSQL(tblcrtstr);
String rv = db.getPath();
db.close();
return rv;
}
// Insert a row to the respective database
private void insertRow(String dbqualifier, String name) {
SQLiteDatabase db = this.openOrCreateDatabase(dbqualifier,MODE_PRIVATE,null);
ContentValues cv = new ContentValues();
cv.put(dbqualifier + NAMECOLUMN_BASE, name);
db.insert(dbqualifier + TABLENAME_BASE,null,cv);
db.close();
}
}
I believe that you are mainly interested in the 2nd query which is :-
SELECT db2.db2_table_main._id, db2.db2_table_main.db2_name, db3.db2_table_main._id, db3.db2_table_main.db2_name FROM db2_table_main JOIN db3.db2_table_main ON db2.db2_table_main._id = db3.db2_table_main._id WHERE db3.db2_table_main._id=? AND db3.db2_table_main.db2_name=?
Note the above query is based upon the original post which had the same database attached twice.
Running the above (after a number of runs that included the commented out row insertion code) produced the following results:-
From/for the first query :-
11-05 07:37:52.764 2726-2726/? D/SELECTSQL_1: SELECT db1_table_main.*, db2.db2_table_main.db2_name, db3.db2_table_main.db2_name FROM db1_table_main JOIN db2.db2_table_main ON db1_table_main._id = db2.db2_table_main._id JOIN db3.db2_table_main ON db1_table_main._id = db3.db2_table_main._id
11-05 07:37:52.764 2726-2726/? D/CSRINFO: Row = 0 Column = _id Value = 1 Column = db1_name Value = Fred Column = db2_name Value = Alan Column = db2_name Value = Alan
11-05 07:37:52.764 2726-2726/? D/CSRINFO: Row = 1 Column = _id Value = 2 Column = db1_name Value = Bert Column = db2_name Value = George Column = db2_name Value = George
11-05 07:37:52.764 2726-2726/? D/CSRINFO: Row = 2 Column = _id Value = 3 Column = db1_name Value = Harry Column = db2_name Value = Robert Column = db2_name Value = Robert
11-05 07:37:52.764 2726-2726/? D/CSRINFO: Row = 3 Column = _id Value = 4 Column = db1_name Value = Tom Column = db2_name Value = Colin Column = db2_name Value = Colin
11-05 07:37:52.764 2726-2726/? D/CSRINFO: Row = 4 Column = _id Value = 5 Column = db1_name Value = Dick Column = db2_name Value = Ian Column = db2_name Value = Ian
11-05 07:37:52.764 2726-2726/? D/CSRINFO: Row = 5 Column = _id Value = 6 Column = db1_name Value = Alan Column = db2_name Value = John Column = db2_name Value = John
11-05 07:37:52.764 2726-2726/? D/CSRINFO: Row = 6 Column = _id Value = 7 Column = db1_name Value = George Column = db2_name Value = Fred Column = db2_name Value = Fred
11-05 07:37:52.764 2726-2726/? D/CSRINFO: Row = 7 Column = _id Value = 8 Column = db1_name Value = Robert Column = db2_name Value = Bert Column = db2_name Value = Bert
11-05 07:37:52.764 2726-2726/? D/CSRINFO: Row = 8 Column = _id Value = 9 Column = db1_name Value = Colin Column = db2_name Value = Harry Column = db2_name Value = Harry
11-05 07:37:52.764 2726-2726/? D/CSRINFO: Row = 9 Column = _id Value = 10 Column = db1_name Value = Ian Column = db2_name Value = Tom Column = db2_name Value = Tom
11-05 07:37:52.764 2726-2726/? D/CSRINFO: Row = 10 Column = _id Value = 11 Column = db1_name Value = John Column = db2_name Value = Dick Column = db2_name Value = Dick
11-05 07:37:52.764 2726-2726/? D/CSRINFO: Row = 11 Column = _id Value = 12 Column = db1_name Value = Alan Column = db2_name Value = Fred Column = db2_name Value = Fred
11-05 07:37:52.765 2726-2726/? D/CSRINFO: Row = 12 Column = _id Value = 13 Column = db1_name Value = George Column = db2_name Value = Bert Column = db2_name Value = Bert
11-05 07:37:52.765 2726-2726/? D/CSRINFO: Row = 13 Column = _id Value = 14 Column = db1_name Value = Robert Column = db2_name Value = Harry Column = db2_name Value = Harry
11-05 07:37:52.765 2726-2726/? D/CSRINFO: Row = 14 Column = _id Value = 15 Column = db1_name Value = Colin Column = db2_name Value = Tom Column = db2_name Value = Tom
11-05 07:37:52.765 2726-2726/? D/CSRINFO: Row = 15 Column = _id Value = 16 Column = db1_name Value = Ian Column = db2_name Value = Dick Column = db2_name Value = Dick
From the 2nd query with the WHERE clause:-
11-05 07:37:52.765 2726-2726/? D/SELECTSQL_2: SELECT db2.db2_table_main._id, db2.db2_table_main.db2_name, db3.db2_table_main._id, db3.db2_table_main.db2_name FROM db2_table_main JOIN db3.db2_table_main ON db2.db2_table_main._id = db3.db2_table_main._id WHERE db3.db2_table_main._id=? AND db3.db2_table_main.db2_name=?
11-05 07:37:52.765 2726-2726/? D/CSRINFO: Row = 0 Column = _id Value = 12 Column = db2_name Value = Fred Column = _id Value = 12 Column = db2_name Value = Fred
I.e it has selected just the one row from the 16 rows, i.e. the one row that has a 12 in the _id column and Fred in the Name column of the 2nd attached (db3) database.
