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
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…
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.
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);”)
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
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.
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.