Solution ID: P330
GOAL:
How to create a WebSpeed Program to Access Data Dynamically?
FACT(s) (Environment):
WebSpeed 3.x
FIX:
Example use of the program, tblreport.html:
'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.
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} " Specified table is unavailable Please use
tblreport.html?t=state&f=* ".
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>