memory databases fail in Windows

[Bob Sneidar]

Enjoy! If you make it better post updates. Eventually we can put it into the library we all share. I use it for querying for the min top and left, and the max bottom and right of all visible objects on a card. But it can be used In a lot of ways. You could store data in an array in a stack property, convert it to a memory db at runtime, then convert it back whenever you want to "commit" the changes. None of the data ever touches the disk in a way that something outside Livecode could access. I suppose now I will have to write memoryDBToArray()

function arrayToMemoryDB aArrayData
   put the keys of aArrayData into tArrayKeys
   sort tArrayKeys numeric ascending
   put ":memory:" into tDBFile
   
   try
      put revOpenDatabase("sqlite", tDBFile) into tDBID
      
      if "Error" is in tDBID then 
         throw tDBID
         return empty
      end if
      
      put "drop table arraydata" into tDropSQL
      revExecuteSQL tDBID, tDropSQL
      put  the result into tResult
   catch tError
      answer tError
      if the environment is "development" then exit to top else quit
   end try
   
   -- create the table
   put "create table" && quote & "arraydata" & quote \
         & cr into tCreateCommand
   put "(" & quote & "recordid" & quote && "NUMERIC PRIMARY KEY NOT NULL UNIQUE, " \
         & cr after tCreateCommand
   
   put the keys of aArrayData [1] into tRecordKeyList
   
   repeat for each line tRecordKey in tRecordKeyList
      put quote & tRecordKey & quote && "VARCHAR, " & cr after tCreateCommand
   end repeat
   
   delete char -3 to -1 of tCreateCommand
   put ")" after tCreateCommand
   
   try
      revExecuteSQL tDBID, tCreateCommand 
      put the result into tResult
      if tResult is not 0 then breakpoint
   catch tError
      breakpoint
   end try
   
   -- insert data
   repeat for each line tKey in tArrayKeys
      put aArrayData [tKey] into aRecordData
      put 1 into tCounter
      put "recordid" into item tCounter of tColumns
      put ":" & tCounter into item tCounter of tColumnData
      put tKey into aColumnData [tCounter]
      
      repeat for each line tRecordKey in tRecordKeyList
         add 1 to tCounter
         put tRecordKey into item tCounter of tColumns
         put ":" & tCounter into item tCounter of tColumnData
         put aRecordData [tRecordKey] into aColumnData [tCounter]
      end repeat
      
      put "(" & tColumns & ")" into tColumns
      put "(" & tColumnData & ")" into tColumnData
      put "insert into arraydata" && tColumns && "VALUES" && tColumnData into tInsertSQL
      replace quote with "\" & quote in tInsertSQL
      replace "'" with quote in tInsertSQL
      
      try
         revExecuteSQL tDBID, tInsertSQL, "aColumnData"
         put the result into tResult
         if tResult is not 1 then breakpoint
      catch tError
         breakpoint
      end try
   end repeat
   
   return tDBID
end arrayToMemoryDB