Using dynamic cursors

Implementing a dynamic dialog based on the database schema.

To write generic code accessing a database, implement the dynamic dialog with field names and types coming from a base.SqlHandle cursor.

The next code example builds a list of fields based on the database table passed as first parameter.

The function scans the result set column names and types of the base.SqlHandle cursor, to build the list of field definitions, that can then be used for the dynamic dialog creation:

FUNCTION build_field_list(dbtable, fields)
    DEFINE dbtable STRING,
           fields DYNAMIC ARRAY OF RECORD
               name STRING,
               type STRING
           END RECORD
    DEFINE h base.SqlHandle,
           i INT

    LET h = base.SqlHandle.create()
    CALL h.prepare("SELECT * FROM " || dbtable)
    CALL h.open()
    CALL h.fetch()
    CALL fields.clear()
    FOR i=1 TO h.getResultCount()
        LET fields[i].name = h.getResultName(i)
        LET fields[i].type = h.getResultType(i)
    END FOR

END FUNCTION