Using dynamic cursors

Implementing a dynamic dialog based on the database schema.

Using column information from base.SqlHandle

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

The following 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:

TYPE t_dd_fields DYNAMIC ARRAY OF RECORD
         name STRING,
         type STRING
     END RECORD

FUNCTION build_field_list(dbtable STRING, fields t_dd_fields) RETURNS ()
    DEFINE h base.SqlHandle
    DEFINE x INTEGER
    LET h = base.SqlHandle.create()
    CALL h.prepare("SELECT * FROM " || dbtable)
    CALL h.open()
    CALL h.fetch()
    CALL fields.clear()
    FOR x=1 TO h.getResultCount()
        LET fields[x].name = h.getResultName(x)
        LET fields[x].type = h.getResultType(x)
    END FOR
END FUNCTION

Code example

The following code example implements a DISPLAY ARRAY dynamic dialog by using a base.SqlHandle object:

The form.per file:

LAYOUT
TABLE table1
{
[c11      |c12            ]
[c11      |c12            ]
[c11      |c12            ]
[c11      |c12            ]
}
END
END

ATTRIBUTES
EDIT c11 = FORMONLY.pkey, TITLE="Num";
EDIT c12 = FORMONLY.name, TITLE="Name";
END

INSTRUCTIONS
SCREEN RECORD sr(FORMONLY.*);
END

The program code:

TYPE t_dd_fields DYNAMIC ARRAY OF RECORD
         name STRING,
         type STRING
     END RECORD

MAIN

    DEFINE fields t_dd_fields
    DEFINE d ui.Dialog
    DEFINE t STRING

    CONNECT TO "test1"

    OPEN FORM f1 FROM "form"
    DISPLAY FORM f1

    CALL create_database_table()

    CALL build_field_list("tab1",fields)

    LET d = ui.Dialog.createDisplayArrayTo(fields, "sr")
    CALL d.addTrigger("ON ACTION accept")
    CALL d.addTrigger("ON ACTION cancel")

    CALL fill_array("SELECT * FROM tab1 ORDER BY name",fields,d)

    WHILE (t := d.nextEvent()) IS NOT NULL
        CASE t
            WHEN "BEFORE DISPLAY"
                CALL d.setSelectionMode("sr", 2)
                CALL d.setCurrentRow("sr",1)
            WHEN "ON ACTION cancel"
                LET int_flag = TRUE
                EXIT WHILE
            WHEN "ON ACTION accept"
                CALL d.accept()
        END CASE
    END WHILE

END MAIN

FUNCTION create_database_table() RETURNS ()
    WHENEVER ERROR CONTINUE
    DROP TABLE tab1
    WHENEVER ERROR STOP
    CREATE TABLE tab1 ( pkey INTEGER, name VARCHAR(30) )
    INSERT INTO tab1 VALUES ( 101, 'aaaa' )
    INSERT INTO tab1 VALUES ( 102, 'bbbbbbb' )
    INSERT INTO tab1 VALUES ( 103, 'ccccccccc' )
END FUNCTION

FUNCTION build_field_list(dbtable STRING, fields t_dd_fields) RETURNS ()
    DEFINE h base.SqlHandle
    DEFINE x INTEGER
    LET h = base.SqlHandle.create()
    CALL h.prepare("SELECT * FROM " || dbtable)
    CALL h.open()
    CALL h.fetch()
    CALL fields.clear()
    FOR x=1 TO h.getResultCount()
        LET fields[x].name = h.getResultName(x)
        LET fields[x].type = h.getResultType(x)
    END FOR
END FUNCTION

FUNCTION fill_array(sqlcmd STRING, fields t_dd_fields, d ui.Dialog) RETURNS ()
    DEFINE h base.SqlHandle
    DEFINE r, x INTEGER
    LET h = base.SqlHandle.create()
    CALL h.prepare(sqlcmd)
    CALL h.open()
    LET r = 0
    WHILE TRUE
        CALL h.fetch()
        IF sqlca.sqlcode == NOTFOUND THEN
            EXIT WHILE
        END IF
        CALL d.appendRow("sr")
        CALL d.setCurrentRow("sr",r:=r+1)
        FOR x=1 TO fields.getLength()
            CALL d.setFieldValue(fields[x].name, h.getResultValue(x))
        END FOR
    END WHILE
END FUNCTION