Array to DB functions

[Bob Sneidar]

Also there is this for those who do not like to use arrays: 

function textToSQLite pList, pDelimiter, pHasHeader, pFileName, pTableName
   set the itemDelimiter to pDelimiter
   
   if pHasHeader then
      put line 1 of pList into tHeader
      delete line 1 of tHeader
   else
      repeat with i = 1 to the number of items of line 1 of pList
         put "Col" & i into item i of tHeader
      end repeat
   end if
   
   if pFileName is empty then put ":memory:" into pFileName
   if pTableName is empty then put "arraydata" into 
   
   try
      put revOpenDatabase("sqlite", pFileName) into tDBID
      
      if "Error" is in tDBID then 
         throw tDBID
         return empty
      end if
      
      put "drop table " & pTableName 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 & pTableName & quote \
         & cr into tCreateCommand
   put "(" & quote & "recordid" & quote && "INTEGER PRIMARY KEY, " \
         & cr after tCreateCommand
   
   repeat for each item tColumn in tHeader
      put quote & tColumn & 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 tRow in pList
      put "INSERT INTO " & pTableName & " (" & tHeader & ")" && "VALUES (" & tRow & ")" into tInsertSQL
      replace quote with "\" & quote in tInsertSQL
      replace "'" with quote in tInsertSQL
      replace pDelimiter with comma in tInsertSQL
      
      try
         revExecuteSQL tDBID, tInsertSQL
         put the result into tResult
         if tResult is not 1 then breakpoint
      catch tError
         breakpoint
      end try
   end repeat
   
   return tDBID
end textToSQLite

> On Jun 27, 2019, at 08:28 , Bob Sneidar via use-livecode <use-livecode at lists.runrev.com> wrote:
> 
> 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
> _______________________________________________
> use-livecode mailing list
> use-livecode at lists.runrev.com
> Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
> http://lists.runrev.com/mailman/listinfo/use-livecode