Android Question How to attach 2 databases and...

[Harris]

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

 

[Erel]

You can use the ATTACH keyword.

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

 

[Harris]

You can use the ATTACH keyword.

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

 

[Kintara]

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

 

[LucaMs]

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

    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

 

[Kintara]

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

 

[Johan Hormaza]

And how would it be if there are more than two databases