Connect too (2)- yes two SQLiteDatabases

Is it possible to connect to 2 SQLiteDatebases at the same time? And if so, how? I am curious for curiosity sake. No plans but I can see where this might come in handy, especially if one wanted to use a single point of access but keep the databases separated.

https://documentation.xojo.com/api/deprecated/deprecated_class_members/sqlitedatabase.attachdatabase.html
or
https://documentation.xojo.com/api/databases/sqlitedatabase.html#sqlitedatabase-adddatabase

1 Like

If you mean, using the same connection, you’d need to connect to the first and then attach the second. I do this when I want to move a row from one database to another (same schema, obvs). I have the issue that each row is defined by its absid column, which is a primary key, so the row that I move has to get a new primary key in the other database (and I need to know what it is). So I do this (ignoring error checks):

Var dbh as new SQLiteDatabase, db1, db2 as string, dbfile as FolderItem, absid, newabsid as Integer

db1 = "/path/to/first/database/somedb"
db2 = "/path/to/second/database/otherdb"
absid = 23  // Or whatever the row id is

dbfile = new FolderItem (db1, FolderItem.PathModes.Native)
dbh.DatabaseFile = dbFile
dbh.Connect ()
dbh.ExecuteSQL ("attach database ':memory:' as mem")
dbh.ExecuteSQL ("create table mem.messages as select * from main.messages where absid=?", absid)
dbh.ExecuteSQL ("update mem.messages set absid=null")
dbh.ExecuteSQL ("attach database '" + db2 + "' as dst")
dbh.ExecuteSQL ("insert into dst.messages select * from mem.messages")

newabsid = dbh.LastRowID

( I am actually connecting to three databases on the same connection, in this example).

1 Like