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
