:memory: heads up

[Bob Sneidar]

Hi all. 

I just wanted to alert anyone working with memory databases. Normally when you call revOpenDatabase("sqLite", filename) it will open the sqLite database with all the tables and data intact. HOWEVER... If you do this with ":memory:" instead of a filename then EACH TIME you call revOpenDatabase() it will create a NEW MEMORY DATABASE! I had no idea this was possible. I don't see anything in the dictionary about it. In fact, :memory: does not even show up in the dictionary! LOL! 

I had to update my arrayToSQLite handler with an additional parameter, pDBID, and I only call revOpenDatabase() if that parameter is empty. 

Here is an updated version in case anyone is using it (however unlikely) : 

/*
The following function will attempt to open a database passed in pDBFile, which should be the full path to an sqLite 
database file, or else a memory database. It will then *attempt* to drop the table passed in pDBName. It will leave 
other tables intact, so tables can be added and dropped as needed by calling this function. pArrayDataA must be a
2 dimensional numbered array, such as the kind used by datagrids. Nested arrays are not supported. provide pDBID 
if there is already a memory database open. 
*/
FUNCTION arrayToSQLite pArrayDataA, pDBFile, pDBName, pDBID
   put the keys of pArrayDataA into tArrayKeys
   sort tArrayKeys numeric ascending
   IF pDBFile is empty THEN put ":memory:" into pDBFile
   IF pDBName is empty THEN put "arraydata" into pDBName
   
   TRY
      if pDBID is empty then \
            put revOpenDatabase("sqlite", pDBFile) into pDBID
      
      IF "Error" is in pDBID THEN
         return empty
      END IF
      
      put "drop table " & pDBName into tDropSQL
      revExecuteSQL pDBID, 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 & pDBName & quote \
         & cr into tCreateCommand
   put "(" & quote & "recordid" & quote && "NUMERIC PRIMARY KEY UNIQUE, " \
         & cr AFTER tCreateCommand
   
   put the keys of pArrayDataA [1] into tRecordKeyList
   
   REPEAT for each line tRecordKey in tRecordKeyList
      if pArrayDataA [1] [tRecordKey] is an array then 
         put "BLOB" into tColumnType
      else
         put VARCHAR into tColumnType
      end if
      
      put quote & tRecordKey & quote && tColumnType & "," && cr AFTER tCreateCommand
   END REPEAT
   
   delete char -3 to -1 of tCreateCommand
   put ")" AFTER tCreateCommand
   
   TRY
      revExecuteSQL pDBID, tCreateCommand
      put the result into tResult
      IF tResult is not 0 THEN breakpoint
   CATCH tError
      breakpoint
   END TRY
   
   put 1 into tRecordCounter
   put "recordid" & cr & tRecordKeyList into tColumns
   
   repeat with i = 1 to the number of lines of tColumns
      put ":" & i into item i of tColumnList
   end repeat
   
   put "(" & tColumnList & ")" into tColumnList
   
   -- insert data
   REPEAT for each line tKey in tArrayKeys
      put 1 into tColumnCounter
      put pArrayDataA [tKey] into tRecordDataA
      put tRecordCounter into tQueryDataA [1]
      
      REPEAT for each line tRecordKey in tRecordKeyList
         add 1 to tColumnCounter
         
         if tRecordDataA [tRecordKey] is an array then
            put arrayEncode(tRecordDataA [tRecordKey]) into tValue
         else
            put tRecordDataA [tRecordKey] into tValue
         end if
         
         put tValue into tQueryDataA [tColumnCounter]
      END REPEAT
      
      put "insert into" && pDBName &&  "VALUES" && tColumnList into tInsertSQL
      
      TRY
         revExecuteSQL pDBID, tInsertSQL, "tQueryDataA"
         put the result into tResult
         if the result is not a number then breakpoint
      CATCH tError
         breakpoint
      END TRY
      
      add 1 to tRecordCounter
   END REPEAT
   
   return pDBID
END arrayToSQLite