Database in Ram

Databases
1

Hi I have a sqlit database called NutrientiDB whose Nutrienti.rsd file resides on disk, I want to use this database in memory, I tried with this code
NutrientiDB.ExecuteSQL (“Attach NutrientiDB ‘: memory:’ as ‘RAMDB’”)
but it returns a databaseException near “’; memory:’”: sintax error
where am i wrong? can someone help me?
Mario

2

Make sure you are using the ordinary quote characters, not curly-quotes. Also you don’t need quotes arounf RAMDB.

1 Like
3

I tried this way but still the same error
NutrientiDB.ExecuteSQL(“Attach NutrientiDB ‘:memory:’ as RAMDB”)
I also tried this other way
NutrientiDB.ExecuteSQL(“Attach NutrientiDB “:memory:” as RAMDB”)
error = item memory does not exist
the quote in ‘: memory:’ I use them in a query and they work, I don’t understand

Mario

4

Out of curiosity does this approach work with API 1.0 database code? API 2.0 puts everything through a prepared statement and that caused issues for other folks.

5

I’ve been doing this:

Var regs as RowSet, dbh as SQLiteDatabase

regs = dbh.ExecuteSQL ("attach database ':memory:' as mem")

since forever. The above is API2 but it worked just the same in API1.

Oh, wait, Mario. You have an unecessary NutrientDB in there. Try this:

NutrientiDB.ExecuteSQL("Attach ':memory:' as RAMDB")

1 Like
6

Thanks Tim, I tried ok, now I will try to continue to use it, I hope to succeed
Mario

7

@Mario_Graziani Watch your single and double quotes.

8

to read the database I used this code
RamDB = New SQLiteDatabase
NutrientiDB.ExecuteSQL(“Attach ‘:memory:’ as RamDB”)
RamDB.SQLExecute(“create table RamDB.Nutri * from NutrientiDB.Nutri”)
ramDB.EncryptionKey = “pasword”
if ramDB.Connect() = false then
DisplayDatabaseError1( false )
Quit
return
end if

sql = “SELECT * From nutri”
if filter <> “” then
sql = sql +" WHERE "+ filter
end
rs = app.RamDB.sqlSelect( sql )
PopulateListBox List1,rs

but cannot read the database
the reported error is
databasefile nil
error true
errorCode 1
ErrorMessage Operation cannot be completed because the database is closed
how to open the database?
where am i wrong?

9

If you create a new sqlite datbase instance wothout setting the dbfile it will be in memory if you connnect it, then attach that on to you other sqlitedb

Var myRamDB As New Sqlitedatabase 
Try
MyRamDB.connect // should connect in mem db
Catch e as databaseexception
// failed 
End try

Now attach it to the other db

 MyOtherDb.Attach(myRamDb)
10

Derkj if I connect the database just declared it still doesn’t work, change the error
no such table: Nutri
so the error is somewhere else
RamDB = New SQLiteDatabase
Try
RamDB.connect
Catch e as databaseexception
msgbox " No"
quit
End try
NutrientiDB.ExecuteSQL(“Attach ‘:memory:’ as RamDB”)
RamDB.SQLExecute(“create table RamDB.nutri * from NutrientiDB.Nutri”)
ramDB.EncryptionKey = “MaRosDieBa”

Mario

11

Don’t try to use .ExecuteSQL(“attach”) etc.

Use the xojo provided functions for that.
https://documentation.xojo.com/api/databases/sqlitedatabase.html#sqlitedatabase-adddatabase

Remember the given database name (ramdb in this example) In queries from the main db:

Var query As String = "SELECT * FROM ramdb.atable"
Try
Var rs As Rowset = mainDB.ExecuteSQL(query)
// Do something with the rowset:

Catch e As DatabaseException
// Handle error
End Try

maindb would be NutrientiDB just replace it.

12

I solved it thanks to our friend Pietro Beccegato
in this way
dim nutrientiDbPath as String = path Nutrienti.rsd

RamDB = New SQLiteDatabase
RamDB.Connect
RamDB.ExecuteSQL(“ATTACH '”+nutrientiDbPath+"’ AS nutrientiDb KEY ‘password’")
RamDB.ExecuteSQL(“CREATE TABLE Main.Nutri AS SELECT * FROM NutrientiDB.Nutri”)
RamDB.ExecuteSQL(“DETACH DATABASE ‘nutrientiDb’”)

However, thanks to all
Mario

13

This topic was automatically closed 182 days after the last reply. New replies are no longer allowed.