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@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
_______________________________________________
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
