In Memory database question

I tried to create an inMemory sqlite database.

  • after creating a table only, no data inserted, the database was blocked, is that normal ?
  • the created table showed up in my sqlite database, is that normal too ?

have never done that before, so I was wondering if it works that way.
thanks

No, no and no.

What do you mean with “database was blocked”? How do you create the in memory db?

I used the example from the xojo blog, Sqlite in RAM …

sorry, it meant database was locked, because the next call to the database created an error and told me it was locked.

I think that the following line is the problem:

// We create a in-memory database, 'attaching' it with the disk based SQLite database
db.ExecuteSQL("attach database ':memory:' as 'RAMDB'")

The “example” copies an existing database into memory.

thanks Beatrix,
will try again … and see if I can get it working

This code is working fine (Xojo 2019r1.1, OS Windows 10)

// Data base SQLite in memory
inMemoryDB = New SQLiteDatabase

If Not inMemoryDB.Connect Then
MsgBox inMemoryDB.ErrorMessage
Return
End If

// create table “Etichette”
Dim sql As String
sql = "CREATE TABLE Etichette "
sql = sql + "(Ubicazione TEXT, Descrizione TEXT) "

inMemoryDB.SQLExecute(sql)

// Writing record into the inMemory Database
Dim sql As String = "DELETE FROM Etichette "
inMemoryDB.SQLExecute(sql)

sql = “INSERT INTO Etichette VALUES(’%” + txtUbicazione.Text + “’, ‘’)”
inMemoryDB.SQLExecute(sql)

// Printing
RS = inMemoryDB.SQLSelect(“SELECT * FROM Etichette”)
etc. etc

1 Like

thanks Nedi,
your code looks more understandable.
Is there any way to see what’s in the inMemory database ?
I mean can you see tables and rows ?

Not easily, but you could temporarily put it on disk when testing by setting the DatabaseFile property and then looking at that with a sqlite database browser app.

Instead of an in-memory database, it may be good to use normal SQLite database file with huge cache, so the whole database fits in memory for great performance.

e.g.
db.SQLExecute "PRAGMA cache_size = 2000000" // 2 GB

then when app crashes, the data is still there.

1 Like

i can say that this has average higher performance than a in-memory db. In-memory sqlite is not that much better than a huge cache.

1 Like

what’s the advantage then ? using a bigger cache should solve the problem …
thanks to all of you for your input

Nedi, why do you use an inMemory database if there is almost no advantage of speed ?

One addition to the speed of in memory, read the full discussion.

It can be little faster but in my testing it was even slower than using a much bigger cache.
Tested on Windows, and MacOS (x64 and ARM64) with almost the same results.

1 Like

Only advantage would be if you have very much consuming queries like:
Multiple Select(s) with Multiple Update(s) in one transaction. This is a very specific purpose, and 99,9% of the times there is not much difference. You’d better optimise your queries and code, that’s much more speed improvement.

thank’s Derkj
I’ll keep that in mind and probably forget using inMemory databases

thanks very much

1 Like

It has been only a matter of study, not a real need (the table has only a single record, so it’s not a need of speed)

that’s ok, now you know how it works, I might do the same, just to see how it works !!