Attach TWO in-memory SQLite Databases

in my app right now I have ONE in-memory database [DB] and one physical on disk database [DAT, at ‘mypath’]
the app currently uses them ‘attached to each other’ when I can do by issuing

ATTACH DATABASE "mypath" AS "TEMPLATE"

and this works very well…

Now what I would like is for DAT to also be an in Memory database… the creation of which is easy enough

But how to attach them, since DAT no longer has a path to use.
SQLite indicates “:memory:” but that bears no reference to an existing connection

I need the ability to attach and detach DB and DAT at will during the execution of the app

DB will be changing during the course of the execution, DAT for the most part will be populated at the startup with very little changes occurring

This is a single user local connection

pretty sure you cannot attach an in memory database to anything else

well darn… well I know I can attach a physcial TO an existing in memory one… the app would be a bit faster if they could both be in memory… And yes they need to be two entities…

Not sure how Xojo engine will react, but in theory you can name in-memory sqlite dbs and attach them.

You should pass the filename string as “file:my_named_mem_db1?mode=memory&cache=shared”, but as Xojo uses a “FolderItem” layer in front of it, don’t know how.

Have you looked at:

https://www.sqlite.org/inmemorydb.html

which says that you can do this:

ATTACH DATABASE ':memory:' AS aux1;

Wouldn’t you then use:

Database.ExecuteSQL ("ATTACH DATABASE ':memory:' AS aux1")
to do the attach, and you could attach another one with:

Database.ExecuteSQL ("ATTACH DATABASE ':memory:' AS aux2")

Re-reading the OP I should perhaps say, open the first in-memory database, then attach the second to it.

[quote=466260:@Tim Streater]

Re-reading the OP I should perhaps say, open the first in-memory database, then attach the second to it.[/quote]

ATTACH needs FILE NAME, different “filenames” not ALIAS. SQLite accepts URI names, some special, for for example, named memory files. But as Xojo uses “FolderItem”, so we can’t pass such URIs as DB paths to SQLite.

It needs a non-existent FolderItem trick, like:

[code]Var db1 As New SQLiteDatabase

Var db2 As New SQLiteDatabase

// URI should create and return a “special” FolderItem accepting ANY URI (Xojo does not inspect it)
// but without file IO (used for passing pure URI “filenames”)
// Shared method. Returns a new “URI FolderItem”
db1.DatabaseFile = FolderItem.URI(“file:my_mem_db1?mode=memory&cache=shared”)
db.CreateDatabase
db1.Connect
db1.ExecuteSQL(“CREATE TABLE Foo (f1, f2);”)

db2.DatabaseFile = FolderItem.URI(“file:my_mem_db2?mode=memory&cache=shared”)
db2.CreateDatabase
db2.Connect
db2.ExecuteSQL(“CREATE TABLE Foo (f3, f4);”)

// Attach DB2 to DB1

db1.ExecuteSQL(“ATTACH DATABASE ‘file:my_mem_db2?mode=memory&cache=shared’ AS db2;”)[/code]

Or… Xojo needs to create a special setter for SQLite just to accept URI names.

db1.DatabaseURI = “file:my_mem_db1?mode=memory&cache=shared”

1 Like

@Dave S put a FR in the Feedback asking for SQLiteDatabase.DatabaseURI and in the future you’ll could have multiple memory attachable DBs.

In my app, I already do this:

Var dbh As New SQLiteDatabase, dbFile As FolderItem
dbFile = new FolderItem ("/path/to/somedatabase", FolderItem.PathModes.Native)
dbh.DatabaseFile = dbFile
dbh.connect ()
dbh.ExecuteSQL ("ATTACH DATABASE ':memory:' AS mem1")
dbh.ExecuteSQL ("create table mem1.mytab (x integer, y text)")

and it works perfectly well.

So I see no reason why the following should not work either, although I haven’t tested it:

Var dbhmem As New SQLiteDatabase
dbhmem.connect ()
dbhmem.ExecuteSQL ("ATTACH DATABASE ':memory:' AS mem1")
dbhmem.ExecuteSQL ("create table mem1.mytab (x integer, y text)")
//etc

You need to read the OP request in detail. It’s not file+memory as everybody does. He wants memory+memory. https://forum.xojo.com/conversation/post/466142

i create one in memory db with multiple tables. Can you do that?? instead of having two in memory db???

Which is what my example does. Creates an in-memory DB, attaches another in-memory DB to it.

Nope. Your first is file+memory and the second is nonsensical, you create an in-memory and ask to attach the same in-memory DB file? Two separated in-memory SQLiteDatabase objects demands named in-memory “files” as already explained.

Which is what I need in my own app.

You’ve evidently not read the SQLite doc. I quote from the link to SQLite docs I posted above:

[quote]Every :memory: database is distinct from every other. So, opening two database connections each with the filename “:memory:” will create two independent in-memory databases.
[/quote]

which is what my second example does and is, AIUI, what the OP wanted.

Richard… no they MUST be two entities…

Tim… sorry… but I do not see where any of what you posted creates TWO in memory database connections and then attaches.

Again as I posted originally…

  • they MUST be two databases connections
  • they MUST be able to be attached and DETACHED at will during the app execution

This is currenlty easily done with a MEMORY + PHYSICAL but I need MEMORY + MEMORY

and I think the key phrase that is causing some confusion is

WILL CREATE… no… the database connections will already exist.

[quote=466293:@Dave S]Richard… no they MUST be two entities…

Tim… sorry… but I do not see where any of what you posted creates TWO in memory database connections and then attaches.

Again as I posted originally…

  • they MUST be two databases connections
  • they MUST be able to be attached and DETACHED at will during the app execution[/quote]

Agreed my example doesn’t give you two separate connections.

Dave,

I had the same need a few years back and concluded there is no way to attach two existing in memory databases in Xojo. I can’t find the thread though.