Array to DB functions

[Bob Sneidar]

Hi all. 

I modified the function arrayToSQLite() (used to be arrayToMemoryDB) so that you can provide a file name and a table name. If you leave the file name empty it will create a memory db. If you leave the table name empty it will use "arraydata" for a table name. 

Once you get the database id, query it like normal to retrieve a cursor. Once you have the cursor use the function cursorToArray(pCursorID) to return an array of the query results as a numbered array. 

If you find any bugs or want any features, let me know. 

Bob S


function arrayToSQLite aArrayData, pDBFile, pDBName
   put the keys of aArrayData 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
      put revOpenDatabase("sqlite", pDBFile) into tDBID
      
      if "Error" is in tDBID then 
         throw tDBID
         return empty
      end if
      
      put "drop table " & pDBName 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 & pDBName & 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 " & pDBName && 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 arrayToSQLite

function cursorToArray pCursorID
   put revDatabaseColumnCount(pCursorID) into tColumnCount
   put revDatabaseColumnNames(pCursorID) into tColumnNames
   
   repeat forever
      add 1 to tRecordCount
      repeat with i = 1 to tColumnCount
         put revDatabaseColumnNumbered(pCursorID, i) into aCursorArray [tRecordCount] [item i of tColumnNames]
      end repeat
      
      revMoveToNextRecord pCursorID
      if not the result then exit repeat
   end repeat
   
   return aCursorArray
end cursorToArray