I have been trying to implement an in memory database and having no luck. What I’m trying to do is copy some pick list detail information to a in memory DB to speed things up. Unfortunately I can’t get it to work. When I try to access the RAMDB.t_items via a record set it is nil. When I run the example code below the debug stops and the following errors are available Exception NilObjectException, rs Nil. If I click on SQLiteDatabase the interesting part is: ErrorMessage no such table: RAMDB.t_items.
I left the code as close to the examples as possible. What am I doing wrong?
[code]Dim rs as RecordSet
Dim db as SQLiteDatabase = New SQLiteDatabase
db.DatabaseFile = GetFolderItem(“filepath”)
If db.Connect = False Then
Dim re As New RuntimeException
re.Reason = “Error trying to connect to the database”
Raise re
End If
db.SQLExecute(“PRAGMA LOCKING_MODE = Exclusive”) // Improves the read access to the database
// We create a in-memory database, ‘attaching’ it with the disk based SQLite database
db.SQLExecute(“attach database ‘:memory:’ as ‘RAMDB’”)
// Copying all the contents from the source table
// to the newly created table on the RAM SQLite database
db.SQLExecute(“create table RAMDB.t_items as select * from db.t_items”)
'this works properly
'rs = db.SQLSelect(“SELECT * FROM t_items”)
'this returns a nil rs
rs = db.SQLSelect(“Select * from RAMDB.t_items”)
while not rs.eof
lbItems.addRow rs.idxField(2).StringValue
rs.moveNext
wend
Thanks for the link but it made my head hurt even more. All I’m trying to do is copy a disk based db to a memory based one to use for returning a few values instead of poking through an array like i have done for years. I have heard so many times this is trivial but so far it’s eluding me!
right but the syntax you’re using wont work (it might if you were using the sqlite cmd line tools but we’re not)
so you have to do it the way that thread says
create the in memory db FIRST
atatch the on disk on to the in memory one
copy the data to the in memory one
when the app quits (or some other key point) copy the data from the in memory back to the on disk one
but, if all you’re doing is looking for a handful of values I personally would not bother with an in memory
just write the correct sql and gfrab whatever values you need directly from the on disk db
its plenty fast unless you need billions of values ???
and the quickest (and safest) way to copy an entire SQLite database to another (be it from a Memory to Disk, Disk to Memory or even Disk to Disk) is to use the built-in SQLite BACKUP command. This works Very well.
I have a project where the User can manipulate a database, and then ultimately decide to trash all the changes… So what I do is copy from disk to Memory, let them do what they will to the memory copy… If they want to save it, then I use backup to put it back into the disk based file.
And the nice thing is, you don’t need to know what the tables are or their structure, Backup takes care of it all for you.
In this case I have taken two posted examples and tried to combine and use them. My first attempts failed so I copied the example code and Dave S’s answer at the top of this thread to hopefully get someone to point out the error that I must have made.
Trying to use the backup command example to create in in memory db with this code I get a “Unable to extract path from the FolderItem.” error on the .CreateDatabase line. I had thought leaving out a disk file reference would result in a in memory db.
Var backupDB As New SQLiteDatabase
try
backupDB.CreateDatabase
db.Backup(backupDB, Nil, -1)
Catch error As IOException
MsgBox("Failed to create backup database. Error: " + error.Message)
End Try
Var backupDB As New SQLiteDatabase
backupDB.DatabaseFile = GetFolderItem("path_to_file")
try
backupDB.CreateDatabase
db.Backup(backupDB, Nil, -1)
Catch error As IOException
MsgBox("Failed to create backup database. Error: " + error.Message)
End Try
What to do?
OK got it! Thanks Dave for the .Backup! Much better! Here is a example for those that might stumble in my footsteps:
[code]Var strSQL as String = “SELECT * FROM $$$your table name here$$$”
Var strDBpath as String = “$$$path to your disk db here$$$”
Var rs as RecordSet
Var db as New SQLiteDatabase
db.DatabaseFile = GetFolderItem(strDBpath)
try
db.Connect
db.SQLExecute(“PRAGMA page_size = 4096”)
rs = db.SQLSelect( strSQL)
while not rs.eof
lbItems.addRow rs.idxField(1).StringValue
rs.moveNext
wend
Catch error As IOException
MsgBox("Failed to connect to disk database. Error: " + error.Message)
End Try
Var backupDB As New SQLiteDatabase
try
backupDB.Connect
db.Backup(backupDB, Nil, -1)
rs = backupDB.SQLSelect( strSQL)
while not rs.eof
lbItems2.addRow rs.idxField(1).StringValue
rs.moveNext
wend
Catch error As IOException
MsgBox("Failed to create backup database. Error: " + error.Message)
End Try
db.Close
backupDB.Close[/code]
Put the correct values in the areas denoted by $$$blah blah$$$. Put two list boxes (lbItems and lbItems2) on a window and put the code above in the open event for the window. It should run without error and show the first column of the db in both list boxes. Worked for me…
$#!%! Not that it makes any difference on a small database but .Backup copies the entire database. Is there any way to restrict it to a single table in the db?
Sorry for all the noise but that is what is in my head right now…