Android Question [Solved] SQL combine (ATTACH?)

mw71 · Nov 15, 2018

Hi,

i store data in SQLite Database monthly, so i have 12 db Files (with identical structure) for one Year.
How can i combine these Files to one (virtual) Database and use this for a SELECT Statement? (SELECT * FROM log WHERE ...... or ....)

I think the right statement is ATTACH? But i am not sure and i dont now how i use it and the SELECT Statement with the createt db .

DonManfred · Nov 15, 2018

B4X:

sql1.ExecNonQuery("ATTACH DATABASE '" & File.Combine(File.DirRootExternal, "2.db") & "' AS db2")

B4X:

SELECT * FROM db2.log WHERE..."

mw71 · Nov 15, 2018

thanks for Anser,

i think a can attach more Files via

B4X:

sql1.ExecNonQuery("ATTACH DATABASE '" & File.Combine(File.DirRootExternal, "3.db") & "' AS db3")
sql1.ExecNonQuery("ATTACH DATABASE '" & File.Combine(File.DirRootExternal, "4.db") & "' AS db4")
....

rigth?

but, "SELECT * FROM db2.log WHERE..." search only in the "2.db" part? i would like serch all data

DonManfred · Nov 15, 2018

mw71 said:
rigth?

yes. Never tried.

mw71 said:
search only in the "2.db" part?

You can construct a sql-query which does search in all databases. But it needs a bit of knowledge about combined queries.
But it is possible.

DonManfred · Nov 15, 2018

Basically the structure is the name, yes?

jimmyF · Nov 15, 2018

Do this for each of your 12 databases:

B4X:

INSERT INTO dbNew.table_name
SELECT * FROM db1.table_name
ON DUPLICATE KEY IGNORE;

Then do your Select statement ((SELECT * FROM dbNew.table_name WHERE ...... or ....)

mw71 · Nov 15, 2018

DonManfred said:
Basically the structure is the name, yes?

you mean the same? Yes, exactly the same.

The Primary ID (unique) is generate from date/time, unique in all db.

DonManfred · Nov 15, 2018

What do you want to archieve? Using a select which will return results from all tables of all the Attached databases? But in one Query/Result?
Can you post a simple query which would return results in all databases? Just post the query as is.

DonManfred · Nov 15, 2018

You can use UNION for ex.

Something like

B4X:

SELECT * FROM db1.log WHERE db1.log_name="Test
UNION
SELECT * FROM db2.log WHERE db2.log_name="Test
UNION
SELECT * FROM db3.log WHERE db3.log_name="Test

mw71 · Nov 15, 2018

What do you want to archieve? Using a select which will return results from all tables of all the Attached databases? But in one Query/Result?

Yes

Create Table:

B4X:

CREATE TABLE log (id INTEGER PRIMARY KEY AUTOINCREMENT, Datum TEXT, Zeit TEXT, Call TEXT, Send TEXT, Recive TEXT, Band TEXT, Mode TEXT, vomBerg TEXT, zumBerg TEXT, Kommentar TEXT)"

ID: Dim ID As Long = DateTime.Add(DateTime.Now, -30,0,0)

Query:

B4X:

Select * FROM log WHERE (vomBerg LIKE '%SX-019%'  OR vomBerg LIKE '%SX-228%') AND (band like '2m' or (band >=144000 and band <=146000)) ORDER BY Datum AND Zeit COLLATE NOCASE

DonManfred · Nov 15, 2018

mw71 said:
Query

See my answer in #9
Build a query which queries all attached Databases (you know how much they are and know the aliasnames you used..).

DonManfred · Nov 15, 2018

try a first one

B4X:

Select * FROM db1.log WHERE (db1.vomBerg LIKE '%SX-019%'  OR db1.vomBerg LIKE '%SX-228%') AND (db1.band like '2m' or (db1.band >=144000 and db1.band <=146000)) ORDER BY db1.Datum AND db1.Zeit COLLATE NOCASE
UNION
Select * FROM db2.log WHERE (db2.vomBerg LIKE '%SX-019%'  OR db2.vomBerg LIKE '%SX-228%') AND (db2.band like '2m' or (db2.band >=144000 and db2.band <=146000)) ORDER BY db2.Datum AND db2.Zeit COLLATE NOCASE

but i´m not sure if the where does work like this (never tried). I would have used ORDER BY Datum ASC, Zeit ASC

JohnK · Nov 15, 2018

mw71 said:
thanks for Anser,

i think a can attach more Files via

B4X:

sql1.ExecNonQuery("ATTACH DATABASE '" & File.Combine(File.DirRootExternal, "3.db") & "' AS db3") sql1.ExecNonQuery("ATTACH DATABASE '" & File.Combine(File.DirRootExternal, "4.db") & "' AS db4") ....

rigth?

but, "SELECT * FROM db2.log WHERE..." search only in the "2.db" part? i would like serch all data

DonManfred said:

try a first one

B4X:

Select * FROM db1.log WHERE (db1.vomBerg LIKE '%SX-019%'  OR db1.vomBerg LIKE '%SX-228%') AND (db1.band like '2m' or (db1.band >=144000 and db1.band <=146000)) ORDER BY db1.Datum AND db1.Zeit COLLATE NOCASE
UNION
Select * FROM db2.log WHERE (db2.vomBerg LIKE '%SX-019%'  OR db2.vomBerg LIKE '%SX-228%') AND (db2.band like '2m' or (db2.band >=144000 and db2.band <=146000)) ORDER BY db2.Datum AND db2.Zeit COLLATE NOCASE

but i´m not sure if the where does work like this (never tried). I would have used ORDER BY Datum ASC, Zeit ASC

Create a view based on the query above, that way in the end the union is hidden from the final query

mw71 · Nov 18, 2018

DonManfred said:
See my answer in #9

right, but you ask in #8 for a query and i would like anser "complete"

first Step, made a test:

B4X:

Dim sql_T As SQL
sql_T.Initialize(File.DirDefaultExternal,"Data_1",True)
sql_T.ExecNonQuery("CREATE TABLE IF NOT EXISTS log (id INTEGER PRIMARY KEY AUTOINCREMENT , col1 TEXT , col2 text, col3 text)")
sql_T.ExecNonQuery2("INSERT INTO log VALUES (null, ?, ?,?)",Array As String("DB1",1,2))
sql_T.ExecNonQuery2("INSERT INTO log VALUES (null, ?, ?,?)",Array As String("DB1","a","b"))
sql_T.close

sql_T.Initialize(File.DirDefaultExternal,"Data_2",True)
sql_T.ExecNonQuery("CREATE TABLE IF NOT EXISTS log (id INTEGER PRIMARY KEY AUTOINCREMENT , col1 TEXT , col2 text, col3 text)")
sql_T.ExecNonQuery2("INSERT INTO log VALUES (null, ?, ?, ?)",Array As String("DB2","3","4"))
sql_T.ExecNonQuery2("INSERT INTO log VALUES (null, ?, ?, ?)",Array As String("DB2","c","d"))
sql_T.close

Dim sql_x As SQL
sql_x.Initialize(File.DirDefaultExternal,"Data_1",False)
sql_x.ExecNonQuery("ATTACH DATABASE '" & File.Combine(File.DirDefaultExternal, "Data_2") & "' AS db2")

works.
with

B4X:

Select * from log

only the first db will returne.

with

B4X:

Select * from db2.log UNION Select * from log

all will return.

after integration in my Code i have only a problem with the ORDER BY...
- only one ORDER BY is required, at the end
- ORDER BY Datum ASC, Zeit ASC works

jimmyF said:
Do this for each of your 12 databases:

B4X:

INSERT INTO dbNew.table_name SELECT * FROM db1.table_name ON DUPLICATE KEY IGNORE;

Then do your Select statement ((SELECT * FROM dbNew.table_name WHERE ...... or ....)

thanks, this is interesting for an other theme (Merge) in my app

Thanks for All!

DonManfred · Nov 18, 2018

What about

B4X:

Select res2.*, res1.* from db2.log as res2, log as res1 WHERE ... order by ....

?

mw71 · Nov 18, 2018

request with this Code:

B4X:

Dim cur As Cursor = sql_x.ExecQuery("Select res2.*, res1.* from db2.log As res2, Log As res1")
Dim sb As StringBuilder

For row = 0 To cur.RowCount - 1
    sb.Initialize
    cur.Position = row
    Dim values(cur.ColumnCount) As String
    For col = 0 To cur.ColumnCount - 1
        values(col) = cur.GetString2(col)
        sb.Append(values(col)).Append(", ")
    Next
Log(sb.ToString)
Next
cur.Close

1, DB2, 3, 4, 1, DB1, 1, 2,
1, DB2, 3, 4, 2, DB1, a, b,
1, DB2, 3, 4, 3, DB1, 1, 2,
1, DB2, 3, 4, 4, DB1, a, b,
1, DB2, 3, 4, 5, DB1, 1, 2,
1, DB2, 3, 4, 6, DB1, a, b,
1, DB2, 3, 4, 7, DB1, 1, 2,
1, DB2, 3, 4, 8, DB1, a, b,
2, DB2, c, d, 1, DB1, 1, 2,
2, DB2, c, d, 2, DB1, a, b,
2, DB2, c, d, 3, DB1, 1, 2,
2, DB2, c, d, 4, DB1, a, b,
2, DB2, c, d, 5, DB1, 1, 2,
2, DB2, c, d, 6, DB1, a, b,
2, DB2, c, d, 7, DB1, 1, 2,
2, DB2, c, d, 8, DB1, a, b,
3, DB2, 3, 4, 1, DB1, 1, 2,
3, DB2, 3, 4, 2, DB1, a, b,
3, DB2, 3, 4, 3, DB1, 1, 2,
3, DB2, 3, 4, 4, DB1, a, b,
3, DB2, 3, 4, 5, DB1, 1, 2,
3, DB2, 3, 4, 6, DB1, a, b,
3, DB2, 3, 4, 7, DB1, 1, 2,
3, DB2, 3, 4, 8, DB1, a, b,
4, DB2, c, d, 1, DB1, 1, 2,
4, DB2, c, d, 2, DB1, a, b,
4, DB2, c, d, 3, DB1, 1, 2,
4, DB2, c, d, 4, DB1, a, b,
4, DB2, c, d, 5, DB1, 1, 2,
4, DB2, c, d, 6, DB1, a, b,
4, DB2, c, d, 7, DB1, 1, 2,
4, DB2, c, d, 8, DB1, a, b,
5, DB2, 3, 4, 1, DB1, 1, 2,
5, DB2, 3, 4, 2, DB1, a, b,
5, DB2, 3, 4, 3, DB1, 1, 2,
5, DB2, 3, 4, 4, DB1, a, b,
5, DB2, 3, 4, 5, DB1, 1, 2,
5, DB2, 3, 4, 6, DB1, a, b,
5, DB2, 3, 4, 7, DB1, 1, 2,
5, DB2, 3, 4, 8, DB1, a, b,
6, DB2, c, d, 1, DB1, 1, 2,
6, DB2, c, d, 2, DB1, a, b,
6, DB2, c, d, 3, DB1, 1, 2,
6, DB2, c, d, 4, DB1, a, b,
6, DB2, c, d, 5, DB1, 1, 2,
6, DB2, c, d, 6, DB1, a, b,
6, DB2, c, d, 7, DB1, 1, 2,
6, DB2, c, d, 8, DB1, a, b,
7, DB2, 3, 4, 1, DB1, 1, 2,
7, DB2, 3, 4, 2, DB1, a, b,
7, DB2, 3, 4, 3, DB1, 1, 2,
7, DB2, 3, 4, 4, DB1, a, b,
7, DB2, 3, 4, 5, DB1, 1, 2,
7, DB2, 3, 4, 6, DB1, a, b,
7, DB2, 3, 4, 7, DB1, 1, 2,
7, DB2, 3, 4, 8, DB1, a, b,
8, DB2, c, d, 1, DB1, 1, 2,
8, DB2, c, d, 2, DB1, a, b,
8, DB2, c, d, 3, DB1, 1, 2,
8, DB2, c, d, 4, DB1, a, b,
8, DB2, c, d, 5, DB1, 1, 2,
8, DB2, c, d, 6, DB1, a, b,
8, DB2, c, d, 7, DB1, 1, 2,
8, DB2, c, d, 8, DB1, a, b,

1, DB1, 1, 2,
2, DB1, a, b,
3, DB1, 1, 2,
4, DB1, a, b,
5, DB1, 1, 2,
6, DB1, a, b,
7, DB1, 1, 2,
8, DB1, a, b,

1, DB1, 3, 4,
2, DB1, c, d,
3, DB1, 3, 4,
4, DB1, c, d,
5, DB1, 3, 4,
6, DB1, c, d,
7, DB1, 3, 4,
8, DB1, c, d,

Android Question [Solved] SQL combine (ATTACH?)

mw71

Active Member

DonManfred

Expert

mw71

Active Member

DonManfred

Expert

DonManfred

Expert

jimmyF

Active Member

mw71

Active Member

DonManfred

Expert

DonManfred

Expert

mw71

Active Member

DonManfred

Expert

DonManfred

Expert

JohnK

Active Member

mw71

Active Member

DonManfred

Expert

mw71

Active Member

Similar Threads