Android Question How to attach 2 databases and...

Harris

Expert
Licensed User
Longtime User
How would I write a query to attach (or join) 2 databases (main1 and temp1) and then insert the records from temp1.table1 into main1.table1.

It is easy when in the same database - "INSERT INTO table1 SELECT * FROM table2" - (structure same of course). I hope it is as easy as example above (after attaching database) with
"INSERT INTO main1.[table1] SELECT * FROM temp1.[table2]"

I need to insert new records into tables in my distributed app. I don't want to overwrite the database to get my new records in since it already contains data the user has entered.

So I thought I would supply a mirror database (MyTemp), containing only new records to insert into various tables. When insertion is complete, I will empty each table in MyTemp. On next app start, I will run the function to insert new, but all tables are empty in MyTemp so nothing will get inserted...

Thanks
 

Harris

Expert
Licensed User
Longtime User
You can use the ATTACH keyword.

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

Ok, so I have already opened and inited my main database - this will attach my temp (db2)...
Then I can use my example above to insert new records?


Edit: I think I found the answer in another post I found in search... I shall try it out - Thanks

Thanks
 
Last edited:
Upvote 0

Kintara

Member
Licensed User
Longtime User
According to a SQLite website:
"Basic syntax of SQLite ATTACH DATABASE statement is as follows:

ATTACH DATABASE 'DatabaseName' As 'Alias-Name'
Above command will also create a database in case database is already not created, otherwise it will just attach database file name with logical database 'Alias-Name'."


However, I find that it does not seem to create a database file if it does not already exist.
Is this just my bad usage of the command or is it not supported in the Android version of SQLite?

Kintara :cool:
 
Upvote 0

LucaMs

Expert
Licensed User
Longtime User
According to a SQLite website:
"Basic syntax of SQLite ATTACH DATABASE statement is as follows:

ATTACH DATABASE 'DatabaseName' As 'Alias-Name'
Above command will also create a database in case database is already not created, otherwise it will just attach database file name with logical database 'Alias-Name'."


However, I find that it does not seem to create a database file if it does not already exist.
Is this just my bad usage of the command or is it not supported in the Android version of SQLite?

Kintara :cool:


B4X:
    Dim DB As SQL
    Dim Dir As String = File.DirRootExternal
  
    DB.Initialize(File.DirRootExternal, "db1.db", True)
  
    ' Only to be sure that db2.db will be created by the Attach command
    If File.Exists(Dir, "db2.db") Then
        File.Delete(Dir, "db2.db")
    End If

    DB.ExecNonQuery("ATTACH DATABASE '" & File.Combine(Dir, "db2.db") & "' AS db2")
  
    Dim Cur As Cursor
    Cur = DB.ExecQuery("PRAGMA database_list")
    If Cur.IsInitialized Then
        For p = 0 To Cur.RowCount -1
            Cur.Position = p
            For c = 0 To Cur.ColumnCount-1
                Log(c & TAB & Cur.GetString2(c))
            Next
        Next
    Else
        Log("not initialized")
    End If
 
Upvote 0

Kintara

Member
Licensed User
Longtime User
Thanks LucaMs, I had been trying something similar and it worked on some Android phones but not all of them. I have now avoided the problem by adding an empty database file to the files directory which is loaded in the .apk file.

Kintara :cool:
 
Upvote 0
Top