Sqlite Query From Mutliple Databases Which Are Already Existing
I am having difficulty in converting below sqlite query into Android source code. I have two databases: - db1 : program table - db2 : event table Here is the query: sqlite3 d
Solution 1:
This is a fully working example (albeit with 3 attached for demonstration purposes and perhaps overly complicated due to column name duplication) :-
publicclassMainActivityextendsAppCompatActivity {
publicstaticfinalStringDBNAME1="db1";
publicstaticfinalStringDBNAME2="db2";
publicstaticfinalStringDBNAME3="db3";
publicstaticfinalStringID_COLUMN="_id";
publicstaticfinalStringTABLENAME_BASE="_table_main";
publicstaticfinalStringNAMECOLUMN_BASE="_name";
publicstaticfinalStringDB2_COL_NAME= DBNAME2 + NAMECOLUMN_BASE;
publicstaticfinalStringEXTENDED_DB2TABLENAME= DBNAME2 + "." + DBNAME2 + TABLENAME_BASE;
publicstaticfinalStringEXTENDED_DB3TABLENAME= DBNAME3 + "." + DBNAME2 + TABLENAME_BASE;
publicstaticfinalStringDB2_FULL_IDCOL= EXTENDED_DB2TABLENAME + "." + ID_COLUMN;
publicstaticfinalStringDB2_FULL_NAMECOL= EXTENDED_DB2TABLENAME + "." + DBNAME2 + NAMECOLUMN_BASE;
publicstaticfinalStringDB3_FULL_IDCOL= EXTENDED_DB3TABLENAME + "." + ID_COLUMN;
publicstaticfinalStringDB3_FULL_NAMECOL= EXTENDED_DB3TABLENAME + "." + DBNAME2 + NAMECOLUMN_BASE;
privatestaticfinal String[] DB1NAMES = newString[] {"Fred","Bert","Harry","Tom","Dick"};
privatestaticfinal String[] DB2NAMES = newString[] {"Alan","George","Robert","Colin","Ian","John"};
String mDB1Path, mDB2path;
SQLiteDatabase mMaster;
@OverrideprotectedvoidonCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
mDB1Path = createDatbase(DBNAME1);
mDB2path = createDatbase(DBNAME2);
// Check that can be added for those who are too arrogant to// even consider they may have the incorrect path name coded// when hard coding the path rather than obtaining it via code.
Log.d("DBPATHS","Path for " + DBNAME1 + " is " + mDB1Path);
Log.d("DBPATHS","Path for " + DBNAME2 + " is " + mDBPath2);
/* Only uncomment for first run to populate database tables
for (String s: DB2NAMES) {
insertRow(DBNAME1,s);
}
for (String s: DB1NAMES) {
insertRow(DBNAME2,s);
}
*/
mMaster = this.openOrCreateDatabase(mDB1Path, Context.MODE_PRIVATE,null);
Stringattachsql="ATTACH DATABASE '" + mDB2path + "' AS " + DBNAME2;
Stringattachsql2="ATTACH DATABASE '" + mDB2path + "' AS " + DBNAME3;
mMaster.execSQL(attachsql);
mMaster.execSQL(attachsql2);
Stringsqlstr="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;
Log.d("SELECTSQL_1",sqlstr);
Cursorcsr= mMaster.rawQuery(sqlstr,null);
while (csr.moveToNext()) {
Stringlogdata="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);
}
Stringarg1="12";
Stringarg2="Fred";
Stringsqlstr2=" 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 " +
DB2_FULL_IDCOL + "=? AND " +
DB3_FULL_NAMECOL + "=?";
Log.d("SELECTSQL_2",sqlstr2);
Cursorcsr2= mMaster.rawQuery(sqlstr2,newString[]{arg1,arg2});
while (csr2.moveToNext()) {
Stringlogdata="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);
}
}
private String createDatbase(String dbqualifier) {
SQLiteDatabasedb=this.openOrCreateDatabase(dbqualifier,Context.MODE_PRIVATE,null);
Stringtblcrtstr="CREATE TABLE IF NOT EXISTS " +
dbqualifier + TABLENAME_BASE +
"(" +
ID_COLUMN + " INTEGER PRIMARY KEY, " +
dbqualifier + NAMECOLUMN_BASE + " TEXT" +
")";
db.execSQL(tblcrtstr);
Stringrv= db.getPath();
db.close();
return rv;
}
privatevoidinsertRow(String dbqualifier, String name) {
SQLiteDatabasedb=this.openOrCreateDatabase(dbqualifier,MODE_PRIVATE,null);
ContentValuescv=newContentValues();
cv.put(dbqualifier + NAMECOLUMN_BASE, name);
db.insert(dbqualifier + TABLENAME_BASE,null,cv);
db.close();
}
}
You problem is that it cannot open the database db2, perhaps because of the path being incorrect. The above gets the path according to the database name.
Example output from the above (note 2nd query purposefully results in empty cursor, it is an example of coping with multiple columns named _id) :-
12-0719:32:26.7642913-2913/mjt.attachdatabases 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
12-0719:32:26.7642913-2913/mjt.attachdatabases D/CSRINFO: Row = 0 Column = _idValue=1 Column = db1_nameValue=AlanColumn=db2_nameValue=FredColumn=db2_nameValue= Fred
12-0719:32:26.7642913-2913/mjt.attachdatabases D/CSRINFO: Row = 1 Column = _idValue=2 Column = db1_nameValue=GeorgeColumn=db2_nameValue=BertColumn=db2_nameValue= Bert
12-0719:32:26.7642913-2913/mjt.attachdatabases D/CSRINFO: Row = 2 Column = _idValue=3 Column = db1_nameValue=RobertColumn=db2_nameValue=HarryColumn=db2_nameValue= Harry
12-0719:32:26.7642913-2913/mjt.attachdatabases D/CSRINFO: Row = 3 Column = _idValue=4 Column = db1_nameValue=ColinColumn=db2_nameValue=TomColumn=db2_nameValue= Tom
12-0719:32:26.7642913-2913/mjt.attachdatabases D/CSRINFO: Row = 4 Column = _idValue=5 Column = db1_nameValue=IanColumn=db2_nameValue=DickColumn=db2_nameValue= Dick
12-0719:32:26.7642913-2913/mjt.attachdatabases 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 db2.db2_table_main._id=? AND db3.db2_table_main.db2_name=?
Amended Code
tested on 3 devices API's 16, 23 and 24 with default permissions. Now uses databases from different directories.
publicclassMainActivityextendsAppCompatActivity {
publicstaticfinalStringDBNAME1="db1";
publicstaticfinalStringDBNAME2="db2";
publicstaticfinalStringDBNAME3="db3";
publicstaticfinalStringID_COLUMN="_id";
publicstaticfinalStringTABLENAME_BASE="_table_main";
publicstaticfinalStringNAMECOLUMN_BASE="_name";
publicstaticfinalStringDB2_COL_NAME= DBNAME2 + NAMECOLUMN_BASE;
publicstaticfinalStringEXTENDED_DB2TABLENAME= DBNAME2 + "." + DBNAME2 + TABLENAME_BASE;
publicstaticfinalStringEXTENDED_DB3TABLENAME= DBNAME3 + "." + DBNAME2 + TABLENAME_BASE;
publicstaticfinalStringDB2_FULL_IDCOL= EXTENDED_DB2TABLENAME + "." + ID_COLUMN;
publicstaticfinalStringDB2_FULL_NAMECOL= EXTENDED_DB2TABLENAME + "." + DBNAME2 + NAMECOLUMN_BASE;
publicstaticfinalStringDB3_FULL_IDCOL= EXTENDED_DB3TABLENAME + "." + ID_COLUMN;
publicstaticfinalStringDB3_FULL_NAMECOL= EXTENDED_DB3TABLENAME + "." + DBNAME2 + NAMECOLUMN_BASE;
privatestaticfinal String[] DB1NAMES = newString[] {"Fred","Bert","Harry","Tom","Dick"};
privatestaticfinal String[] DB2NAMES = newString[] {"Alan","George","Robert","Colin","Ian","John"};
privatestaticfinalStringaltbasepath="/data/data/mjt.attachdatabases/";
String mDB1Path, mDB2path, mDBA1Path, mDBA2Path;
SQLiteDatabase mMaster, mAltMaster;
@OverrideprotectedvoidonCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
mDB1Path = createDatbase(DBNAME1);
mDB2path = createDatbase(DBNAME2);
mDBA1Path = createDatabase(DBNAME1,altbasepath);
mDBA2Path = createDatabase(DBNAME2,altbasepath);
// Only uncomment for first run to populate database tablesfor (String s: DB2NAMES) {
insertRow(DBNAME1,s);
insertAltRow(DBNAME2,s);
}
for (String s: DB1NAMES) {
insertRow(DBNAME2,s);
insertAltRow(DBNAME1,s);
}
Log.d("DBPATHS", "DB1 Path=" + mDB1Path + " - DSB2 path=" + mDB2path);
Log.d("DBPATHS", "ADB1 Path=" + mDBA1Path + " - DADB2 path=" + mDBA2Path);
mMaster = this.openOrCreateDatabase(mDB1Path, Context.MODE_PRIVATE,null);
mAltMaster = this.openOrCreateDatabase(mDBA1Path,Context.MODE_PRIVATE,null);
/*
String altattachsql = "ATTACH DATABASE '" + mDBA2Path + "' AS " + DBNAME2;
String attachsql = "ATTACH DATABASE '" + mDB2path + "' AS " + DBNAME2;
String aaltttachsql2 = "ATTACH DATABASE '" + mDB2path + "' AS " + DBNAME3;
String attachsql2 = "ATTACH DATABASE '" + mDB2path + "' AS " + DBNAME3;
mMaster.execSQL(attachsql);
mMaster.execSQL(attachsql2);
mAltMaster.execSQL(altattachsql);
mAltMaster.execSQL(aaltttachsql2);
*/Stringmdattachsql="ATTACH DATABASE '" + mDBA2Path + "' AS " + DBNAME2;
Stringmdattachsql2="ATTACH DATABASE '" + mDBA2Path + "' AS " + DBNAME3;
mMaster.execSQL(mdattachsql);
mMaster.execSQL(mdattachsql2);
Stringsqlstr="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;
Log.d("SELECTSQL_1",sqlstr);
Cursorcsr= mMaster.rawQuery(sqlstr,null);
while (csr.moveToNext()) {
Stringlogdata="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);
}
/*
Log.d("SELECTSQL_ALT",sqlstr);
Cursor acsr = mAltMaster.rawQuery(sqlstr,null);
while (acsr.moveToNext()) {
String logdata = "Row = " + acsr.getPosition();
for (int i=0; i < acsr.getColumnCount(); i++) {
logdata = logdata + " Column = " + acsr.getColumnName(i) +
" Value = " + acsr.getString(i);
}
Log.d("CSRINFO",logdata);
}
*/Stringarg1="12";
Stringarg2="Fred";
Stringsqlstr2=" 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 " +
DB2_FULL_IDCOL + "=? AND " +
DB3_FULL_NAMECOL + "=?";
Log.d("SELECTSQL_2",sqlstr2);
Cursorcsr2= mMaster.rawQuery(sqlstr2,newString[]{arg1,arg2});
while (csr2.moveToNext()) {
Stringlogdata="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);
}
}
private String createDatbase(String dbqualifier) {
SQLiteDatabasedb=this.openOrCreateDatabase(dbqualifier,Context.MODE_PRIVATE,null);
Stringtblcrtstr="CREATE TABLE IF NOT EXISTS " +
dbqualifier + TABLENAME_BASE +
"(" +
ID_COLUMN + " INTEGER PRIMARY KEY, " +
dbqualifier + NAMECOLUMN_BASE + " TEXT" +
")";
db.execSQL(tblcrtstr);
Stringrv= db.getPath();
db.close();
return rv;
}
private String createDatabase(String dbqualifier, String altpath) {
SQLiteDatabasedb=this.openOrCreateDatabase(altpath + "/" + dbqualifier,Context.MODE_PRIVATE, null);
Stringtblcrtstr="CREATE TABLE IF NOT EXISTS " +
dbqualifier + TABLENAME_BASE +
"(" +
ID_COLUMN + " INTEGER PRIMARY KEY, " +
dbqualifier + NAMECOLUMN_BASE + " TEXT" +
")";
db.execSQL(tblcrtstr);
Stringrv= db.getPath();
db.close();
return rv;
}
privatevoidinsertRow(String dbqualifier, String name) {
SQLiteDatabasedb=this.openOrCreateDatabase(dbqualifier,MODE_PRIVATE,null);
ContentValuescv=newContentValues();
cv.put(dbqualifier + NAMECOLUMN_BASE, name);
db.insert(dbqualifier + TABLENAME_BASE,null,cv);
db.close();
}
privatevoidinsertAltRow(String dbqualifier, String name) {
SQLiteDatabasedb=this.openOrCreateDatabase(altbasepath + "/" + dbqualifier, MODE_PRIVATE,null);
ContentValuescv=newContentValues();
cv.put(dbqualifier + NAMECOLUMN_BASE, name);
db.insert(dbqualifier + TABLENAME_BASE, null, cv);
db.close();
}
}
Attempts to recreate the same error :-
1 - Try to open with a path that has a non-existing file :-
Changed code to :-
mAltMaster = this.openOrCreateDatabase(mDBA1Path+"x",Context.MODE_PRIVATE,null);Fails trying to open :-
12-0806:03:31.2941482-1482/?E/SQLiteLog:(14)cannotopenfileatline30174of [00bb9c9ce4]
12-0806:03:31.2941482-1482/?E/SQLiteLog:(14)os_unix.c:30174:(21)open(/data/data/mjt.attachdatabases/db1x)-12-0806:03:31.2941482-1482/?E/SQLiteDatabase:Failedtoopendatabase'/data/data/mjt.attachdatabases/db1x'.android.database.sqlite.SQLiteCantOpenDatabaseException:unknownerror(code14):Couldnotopendatabase2 - Skip the open and try to attach with non-existing file :-
e.g. ATTACH DATABASE '/data/data/mjt.attachdatabases/db2blah' AS db2
The ATTACH works but fails later due to table not found (i.e. the database is created, as attach appears to use openOrCreate, but has no user defined tables).
3 - try to attach a database that is not a database file, in this case a pdf file renamed to db2blah.
The ATTACH fails with :-
E/SQLiteLog: (26) statement aborts at 5: [ATTACH DATABASE '/data/data/mjt.attachdatabases/db2blah' AS db2] file is encrypted orisnot a database
All the above appear to not meet the error. So perhaps the issue is that the database file doesn't have the appropriate permissions, another possibility is that the file may be in use.
Post a Comment for "Sqlite Query From Mutliple Databases Which Are Already Existing"