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@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@lists.runrev.com
> Please visit this url to subscribe, unsubscribe and manage your subscription
> preferences:
> http://lists.runrev.com/mailman/listinfo/use-livecode
_______________________________________________
use-livecode mailing list
use-livecode@lists.runrev.com
Please visit this url to subscribe, unsubscribe and manage your subscription
preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode
