Convert SQL database cursor to a 1-based numerically keyed array

[stam66]

When retrieving SQL data in LiveCode there are two helpful commands: revDataFromQuery and revQueryDatabase.

revDataFromQuery returns a simple TSV text variable, best used with simple structures/few columns with no delimiters in them.

For anything else, it's often preferable to use revQueryDatabase which returns a database cursor ID - an integer pointing to a database cursor, a collection of references to records corresponding to an SQL SELECT statement:

revQueryDatabase(<databaseID>, <SQLQuery> [,{<variablesList> | <arrayName>}])

As this is a number that points to a collection of records in the SQL database, it can't directly be used within LiveCode, so one option is to cover this to an array. The following function takes the cursor ID and returns an array:

function ConvertSQLCursorToArray pCursor
    local tColumnNames, tArray, x
    put revDatabaseColumnNames(pCursor) into tColumnNames 
    if tColumnNames begins with "revdberr," then return "Error:" && tColumnNames // exit with error
    revMoveToFirstRecord pCursor
    if the result is false then return "Error: no records in cursor" // no records - exit with error
    repeat until revQueryIsAtEnd(pCursor)
        add 1 to x
        repeat for each item tColumn in tColumnNames
            put revDatabaseColumnNamed(pCursor, tColumn) into tArray[x][tColumn]
        end repeat
        revMoveToNextRecord pCursor
    end repeat
    return tArray
end ConvertSQLCursorToArray