Stuck on native database functions

[Bob Sneidar]

Okay, so here's an updated version of my ArrayToSQLITE function. It takes an array, and as the name suggests, converts it to an sqLite database and returns the Database ID. Of note is that it now works with EMBEDDED ARRAYS! If it detects that a key is an array in the supplied array, it will create a BLOB column, otherwise it will create a VARCHAR column. (This is largely for syntax compatibility as sqLITE does not have data type constraints, but anything larger than 255 characters will need to be a BLOB [I think]. 

Then when building the insert statement, it will arrayEncode the value. 

I have not tested this yet, as I have no need of it, but if anyone wants to try, feel free and let me know if you find any issues. 

FUNCTION arrayToSQLite pArrayDataA, pDBFile, pDBName
   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
      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 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 tDBID, 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 tDBID, 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 tDBID
END arrayToSQLite


> On Jul 9, 2021, at 08:09 , Bob Sneidar via use-livecode <use-livecode at lists.runrev.com> wrote:
> 
> Okay I found out what I did wrong. The array variable needs to be enclosed in quotes. I remember now some time in the past looking at that and saying, "Why are their quotes around the array variable? That won't work!" So I removed them and subsequently shot myself in the foot. 
> 
> For the record, having a parameter that can be either a string or an array, and then having to enclose the name of the array in quotes to keep the handler from confusing it as a string, strikes me as a really odd way to do things. But hey, it is what it is. 
> 
> Bob S