SUMMARY:
This Knowledge Base solution provides a sample WebSpeed program that uses 4GL dynamic objects; query, buffer, and buffer-field. These objects were introduced in Progress Version 9.x and allow dynamic access to the data in a Progress database.
The use of dynamic objects allows you to write generic code, programs that can be used with any table in the database.
SOLUTION:
In this KBase, the program creates an HTML page with data presented in a table format and where the labels of the columns perform a request to sort the data.
Generic programs are useful because they can be reused, provide a common look and feel, and are easy to maintain. Instead of having as many programs as reports of a type as needed, only one program is used.
The program receives parameters that are passed in the query string, the URL of a GET request. The program implements a validation using the CAN-DO function to check whether the table passed as a parameter matches a CAN-DO list.
The program should be saved with .htm or .html extension so it can be compiled with WebSpeed as an HTML with embedded SpeedScript. For example, the program can be saved as tblreport.html.
Example use of the program:
- http://host/cgi-bin/cgiip.exe/tblreport.html?t=state&f=*
Generates a report on table state using all its the fields.
- http://host/
cgi-bin/cgiip.exe/tblreport.html?t=state&f=*&s=Region
Generates a report on table state using all its the fields
and sorting by Region.
- http://host/cgi-bin/cgiip.exe/
tblreport.html?t=customer&f=custnum,name,creditlimit
Generates a report on table customer using fields custnum,
name, and creditlimit.
<html>
<?WS>
DEFINE VARIABLE tablename AS CHARACTER NO-UNDO.
DEFINE VARIABLE qh AS HANDLE NO-UNDO.
DEFINE VARIABLE bh AS HANDLE NO-UNDO.
DEFINE VARIABLE fh AS HANDLE NO-UNDO.
DEFINE VARIABLE i AS INTEGER NO-UNDO.
DEFINE VARIABLE nfields AS INTEGER NO-UNDO.
DEFINE VARIABLE fieldlist AS CHARACTER NO-UNDO.
DEFINE VARIABLE fieldname AS CHARACTER NO-UNDO.
DEFINE VARIABLE nrecords AS CHARACTER NO-UNDO.
DEFINE VARIABLE theader AS CHARACTER NO-UNDO.
DEFINE VARIABLE nfieldlist AS INTEGER NO-UNDO.
DEFINE VARIABLE sortby AS CHARACTER NO-UNDO.
tablename = get-value("t").
fieldlist = get-value("f").
sortby = get-value("s").
IF sortby <> "" THEN sortby = "by " + sortby.
/* Simple security check so only specific tables can be accessed */
IF NOT CAN-DO("customer,salesrep,state", tablename) THEN DO:
{&OUT} "<body>Specified table is unavailable<br>Please use tblreport.html?t=state&f=*</body></html>".
RETURN.
END.
CREATE BUFFER bh FOR TABLE tablename NO-ERROR.
theader = "".
nfields = 0.
/* Expands * to field list */
IF fieldlist = "*" THEN DO:
fh = bh:BUFFER-FIELD(1).
fieldlist = fh:NAME.
REPEAT I = 2 TO bh:NUM-FIELDS:
fh = bh:BUFFER-FIELD(I).
fieldlist = fieldlist + "," + fh:NAME.
END.
END.
nfieldlist = NUM-ENTRIES(fieldlist).
REPEAT i = 1 TO nfieldlist:
fieldname = ENTRY(i, fieldlist).
fh = bh:BUFFER-FIELD(fieldname).
nfields = nfields + (IF fh:EXTENT = 0 THEN 1 ELSE fh:EXTENT).
IF fh:EXTENT = 0 THEN
theader = theader + '<td><a href="tblreport.html?t='
+ url-encode(tablename,"QUERY")
+ '&f='
+ url-encode(get-value("f"),"QUERY")
+ '&s='
+ fh:NAME
+ '"><b>' + fh:LABEL + '</b></a></td>'.
ELSE DO i = 1 TO fh:EXTENT:
theader = theader + '<td><b>' + fh:LABEL + " "
+ STRING(i) + '</b></td>'.
END.
END.
</?WS>
<head><title>Report on `bh:TABLE` table</title></head>
<body>
<center>
<table border="1">
<tr><td align="center" colspan="`nfields`"><h2>Report on `bh:TABLE` table<br>(sort `sortby`)</h2></td></tr>
<tr>`theader`</tr>
<?WS>
CREATE QUERY qh.
qh:SET-BUFFERS(bh).
qh:QUERY-PREPARE("FOR EACH " + tablename + " " + sortby).
IF qh:QUERY-OPEN THEN
REPEAT:
qh:GET-NEXT().
IF qh:QUERY-OFF-END THEN LEAVE.
{&OUT} '<tr>'.
REPEAT i = 1 TO nfields:
fieldname = ENTRY(i, fieldlist).
fh = bh:BUFFER-FIELD(fieldname).
IF fh:EXTENT = 0 THEN
{&OUT} '<td>' fh:STRING-VALUE '</td>'.
ELSE DO i = 1 TO fh:EXTENT:
{&OUT} '<td>' fh:STRING-VALUE(i) '</td>'.
END.
END.
{&OUT} '</tr>~n'.
END.
qh:QUERY-CLOSE().
DELETE OBJECT qh.
DELETE OBJECT bh.
</?WS>
</table>
</center>
</body>
</html>
Reference to Written Documentation:
Progress Programming Handbook.
Progress Language Reference.