SQLite Query to Array

These 3 handlers can be replaced in the script of stack “AppStarterCodeLibrary”.
They help turn the results of a query into a Livecode Array using a numerical key or one of the columns.

TypeScript

function SQLite_QueryToArray pThisConnectionID,pThisQuery,@pDestinationVariable,pWhatKey,pDisplayProgress

local pRecordSetID
local tTheResult
// generate recordset
put SQLite_QueryToRecordset(pThisConnectionID,pThisQuery,pDestinationVariable) into pRecordSetID
// convert recordset to an array
put SQLite_RecordsetToArray(pRecordSetID,pDestinationVariable,pWhatKey,pDisplayProgress) into tTheResult
return tTheResult
end SQLite_QueryToArray

function SQLite_QueryToRecordset ThisConnectionID,WhichQueryToRun,@pDestinationVariable
local tRecordSetID
// run the query and create a recordset
put revQueryDatabase(ThisConnectionID,WhichQueryToRun) into tRecordSetID
return tRecordSetID
end SQLite_QueryToRecordset

function SQLite_RecordsetToArray pThisRecordset,@pDestinationArray,WhatKey,DisplayProgress
local tIndex
local theColumns
local theError
local tNumberOfRecords
local tThisRecordID

// Get the names of all the columns in the database cursor
put revDatabaseColumnNames(pThisRecordset) into theColumns

if theColumns begins with "revdberr," then
put item 2 to -1 of theColumns into theError
end if

if theError is empty then
put 0 into tIndex

## Loop through all rows in cursor

put revNumberOfRecords(pThisRecordset) into tNumberOfRecords
revMoveToFirstRecord pThisRecordset — set cursor to first record

repeat until revQueryIsAtEnd(pThisRecordset)
// cycle through the recordset until the end is reached
add 1 to tIndex

if DisplayProgress is "" then
else
end if

// Move all fields in row into next dimension of the array

repeat for each item theColumn in theColumns
if TheColumn = WhatKey then
// if using a specific column to use as they array key, the column must be first in line
put revDatabaseColumnNamed(pThisRecordset,theColumn) into tThisRecordID
put tThisRecordID into pDestinationArray[tThisRecordID][theColumn]
else
if WhatKey = "numeric" then
put revDatabaseColumnNamed(pThisRecordset, theColumn) into pDestinationArray[tIndex][theColumn]
else
put revDatabaseColumnNamed(pThisRecordset, theColumn) into pDestinationArray[tThisRecordID][theColumn]
end if
end if

end repeat

revMoveToNextRecord pThisRecordset

if DisplayProgress is "" then
else
wait 0 with messages
end if
end repeat
else
return "error:" & theError
end if

// close the recordset

revCloseCursor pThisRecordSet
return "OK" & comma & tNumberOfRecords
end SQLite_RecordsetToArray

AppStarterStack

for Livecode

Leave a Comment Cancel Reply

Hello, would you like to stay up to date with AppStarterStack development?

For

By

©2021 by MakeShyft R.D.A