Hi all.
I just wanted to alert anyone working with memory databases. Normally when you
call revOpenDatabase("sqLite", filename) it will open the sqLite database with
all the tables and data intact. HOWEVER... If you do this with ":memory:"
instead of a filename then EACH TIME you call revOpenDatabase() it will create
a NEW MEMORY DATABASE! I had no idea this was possible. I don't see anything in
the dictionary about it. In fact, :memory: does not even show up in the
dictionary! LOL!
I had to update my arrayToSQLite handler with an additional parameter, pDBID,
and I only call revOpenDatabase() if that parameter is empty.
Here is an updated version in case anyone is using it (however unlikely) :
/*
The following function will attempt to open a database passed in pDBFile, which
should be the full path to an sqLite
database file, or else a memory database. It will then *attempt* to drop the
table passed in pDBName. It will leave
other tables intact, so tables can be added and dropped as needed by calling
this function. pArrayDataA must be a
2 dimensional numbered array, such as the kind used by datagrids. Nested arrays
are not supported. provide pDBID
if there is already a memory database open.
*/
FUNCTION arrayToSQLite pArrayDataA, pDBFile, pDBName, pDBID
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
if pDBID is empty then \
put revOpenDatabase("sqlite", pDBFile) into pDBID
IF "Error" is in pDBID THEN
return empty
END IF
put "drop table " & pDBName into tDropSQL
revExecuteSQL pDBID, 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 pDBID, 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 pDBID, 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 pDBID
END arrayToSQLite
_______________________________________________
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
