SQL Insert Syntax

[Bob Sneidar]

Hi all. 

Here's a new toy for everyone to play with. It takes a numbered array of key/value pairs and converts it to a MEMORY database. Why you ask? So you can query against it of course! 

In my particular case I am getting an array of every object on a card EXCLUDING objects that are elements of a datagrid (that would be way too much data for me to care about) so I can query against it. For instance, given one of the columns is the name of the object and I want all the images, I can query for all image records:

select * from arraydata where name LIKE 'image %'

You can see how handy this would be, rather than iterating through every element of an array to find ONLY the array records you want. 

Here's the code:

function arrayToMemoryDB aArrayData
   put the keys of aArrayData into tArrayKeys
   sort tArrayKeys numeric ascending
   put ":MEMORY:" into tDBFile
   put revOpenDatabase("sqlite", tDBFile) into tDBID
   put "drop table arraydata" into tDropSQL
   
   try
      revExecuteSQL tDBID, tDropSQL
      put  the result into tResult
   catch tError
   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