• Welcome to SQLitening Support Forum.
 

News:

Welcome to the SQLitening support forums!

Main Menu

Using :memory:

Started by Marc Giao, December 05, 2009, 01:21:18 PM

Previous topic - Next topic

Marc Giao

Hi,

I want to duplicate certain tables (not the copmlete database) from a disk database in a ":memory:" database, wah is the best way to accomplish this?

I do a query on the disk db, do I then ise slGetRow to process each row and then insert into :memory:?

Thanks for any info.

Marc

Fred Meier

That would work but this might be better:
   slExe "Insert into MemoryTable Select * from DiskTable"

Marc Giao

Hi Fred,

Thanks for the example, looks simple but I am having trouble understanding how to deal with both db's :)

For your example to work don't both db's have to be open? How do you open both db's for this example to work? Do you loose all data if you close the :memory: database?

I was planning on having a table with the same name in both db_disk and db_mem, then query certain data from db_disk to db_mem and close db_disk.

After processing data in db_mem I would like to reopen db_disk and either retrieve more data from db_disk to db_mem or from db_mem to db_disk.

Am I hoping for too much here? I am really confused and probably missing something very basic :)

Marc

Fred Meier

#3
You are right, I forgot about the two seperate databases.  Requires using Attach as follows:
  slOpen "DiskDataBase.Sld"
   slAttach ":memory:", "MemDB"
   slExe "Create table MemDB.TableName as select * from Main.TableName"
   slExe "Insert into MemDB.TableName select * from Main.TableName"


After the above code runs, you now can use both tables in both the
databases simply by preceding the tables names with MemDB (or whatever
name you choose) and Main. 

Marc Giao

Hi Fred,

Thanks for that example... Starting to make sense now :)

I am assuming this example only works if both db_disk and db_men are either local or remote. If one db is local and the other is remote then the following example (also your code) from another thread mus tbe used instead, am I correct?

'http://www.sqlitening.com/support/index.php?topic=2869.15
Sub CopyTable

   Local lsSaveVAlue as String
   Local lsRemoteDB, lsLocalDB as String

   ' Connect, open, and save remote database handle
   slConnect
   slOpen "Test.Sld"
   slPushDatabase lsRemoteDB

   ' Switch libs, open, and save local database handle
   slSetProcessMods "L0"         ' switch to local lib         
   slOpen "C:\SQLitening\Test\TestLocal.Sld
   slPushDatabase lsLocalDB

   ' Create table on local and begin tran
   slPopDatabase lsLocalDB
   slExe "Create Table if not exists T2 (F1)"
   slExe "Delete from T2"
   slExe "Begin"

   ' Switch to remote and select from input table
   slPopDatabase lsRemoteDB
   slSetProcessMods "L1"         ' switch to remote lib     
   slSel "Select F1 from T1"
   do while slGetRow
      lsSaveValue = slFN("F1")

      ' Switch to local and insert
      slPopDatabase lsLocalDB
      slSetProcessMods "L0"         ' switch to local lib     
      slExe "Insert into T2 values(" & lsSaveValue & ")"

      ' Switch to remote for next record
      slPopDatabase lsRemoteDB
      slSetProcessMods "L1"         ' switch to remote lib     
   loop

   ' Switch to local and end tran
   slPopDatabase lsLocalDB
   slSetProcessMods "L0"         ' switch to local lib     
   slExe "End"

End Sub


Marc

Fred Meier

My example will work in either local or remote mode. 

Memory and Temp databases will "reside" on the server if running remote or
on the client if running local.  So just add a slConnect to my example and
you will have a memory database on the server. 

Now, if your have a remote database and you wanted to have one of the
tables to be a local memory database then you would have to use your
example. 

Using Memory and Temp databases on the server(remote) is "OK", of course
local ones will always perform better.