All geek questions in one place

SQLite - cross database query not working

I want to execute a cross-database query in SQLite on Android. I have two tables in two different databases.

attach database 'data/data/com.app/databases/db1' as db1; attach database 'data/data/com.app/databases/db2' as db2; SELECT db1.tbl1.* FROM db1.tbl1 JOIN db2.tbl2 ON db1.tbl1.primaryKey = db2.tbl2.primaryKey WHERE db1.tbl1.columnX = ? AND db2.tbl2.columnY = ?

Calling this query using the rawQuery Android method, for example:

 Cursor cursor = sqLiteDatabase.rawQuery(selectQuery, new String[]{"1","xyz"});

I get this error:

 SQLiteException: bind or column index out of range: handle 0xa6259ec8

I checked the query syntax and it is correct. Unable to execute cross database query in Android?

+5
source share
1 answer

I checked the query syntax and it is correct. Is it not possible to execute a cross-database query in Android?

Yes, perhaps, as the example demonstrates.

I think your problem is that you are using the wrong rawQuery / signature method.

instead of: -

  Cursor cursor = sqLiteDatabase.rawQuery(selectQuery, "1","xyz");

I believe that you should use: -

  Cursor cursor = sqLiteDatabase.rawQuery(selectQuery, new String[]{"1","xyz"});

The first only supplies 1 anchor object, unlike the two expected anchor elements (although what you use is not even a valid signature for rawQuery)

However, I suspect that from testing you have additional problems regarding the request. Here is a working example based on your original post (i.e., Attached databases are actually the same databases, in principle, it doesn't matter): -

 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 second request: -

 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=?

Please note that the above query is based on the original message, which included the same database twice.

Running the above (after several runs, including a code to enter a line with a missing code), gave the following results: -

From / for the first request: -

 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 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/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 second 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

Those. he selected only one row of 16 rows, i.e. one row with 12 in the _id column and Fred in the Name column of the second nested database (db3).

0
source

Source: https://habr.com/ru/post/1273098/

More articles:


All Articles