Kbase 18278: Dynamic Query : sample code to view database tables and fields dynamically.
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
15/10/2008 |
|
Status: Verified
GOAL:
How to view database tables and fields dynamically.
FACT(s) (Environment):
Progress 9.x
FIX:
The following program uses dynamic query, buffer and buffer field objects to view and navigate dynamically any table in a connected database.
It presents one selection list with all the tables of the database, the fields of a selected table in a related selection list and provides a frame to display the data from the database for the selected table and fields.
If there are connections to multiple databases the program will use the working database.
DEFINE VARIABLE select-1 AS CHARACTER
VIEW-AS SELECTION-LIST INNER-CHARS 20
INNER-LINES 5 SCROLLBAR-VERTICAL LABEL "Table".
DEFINE VARIABLE select-2 AS CHARACTER
VIEW-AS SELECTION-LIST INNER-CHARS 20
INNER-LINES 5 SCROLLBAR-VERTICAL MULTIPLE LABEL "Fields".
DEFINE BUTTON b-first LABEL "First".
DEFINE BUTTON b-prev LABEL "Prev".
DEFINE BUTTON b-next LABEL "Next".
DEFINE BUTTON b-last LABEL "Last".
DEFINE BUTTON b-use-selected-fields LABEL "Use Selected fields".
DEFINE VARIABLE qh AS HANDLE NO-UNDO.
DEFINE VARIABLE bh AS HANDLE NO-UNDO.
DEFINE VARIABLE bf AS HANDLE NO-UNDO.
FORM
select-1 HELP "Choose one table in this selection list."
select-2 HELP "Select one field or use ctrl-click to select more than one."
SKIP
b-first b-prev b-next b-last b-use-selected-fields AT 40
WITH FRAME FRAME-A SIDE-LABELS WIDTH 80 THREE-D.
VIEW FRAME FRAME-A.
FORM WITH FRAME FRAME-B SIDE-LABELS WIDTH 120 17 DOWN THREE-D ROW 6.
VIEW FRAME FRAME-B.
ON CHOOSE OF b-first
DO:
IF NOT VALID-HANDLE(qh) THEN
LEAVE.
qh:GET-FIRST().
RUN display_fields IN THIS-PROCEDURE.
END.
ON CHOOSE OF b-prev
DO:
IF NOT VALID-HANDLE(qh) THEN
LEAVE.
qh:GET-PREV().
RUN display_fields IN THIS-PROCEDURE.
END.
ON CHOOSE OF b-next
DO:
IF NOT VALID-HANDLE(qh) THEN
LEAVE.
qh:GET-NEXT().
RUN display_fields IN THIS-PROCEDURE.
END.
ON CHOOSE OF b-last
DO:
IF NOT VALID-HANDLE(qh) THEN
LEAVE.
qh:GET-LAST().
RUN display_fields IN THIS-PROCEDURE.
END.
ON CHOOSE OF b-use-selected-fields
DO:
RUN create_selected_fields IN THIS-PROCEDURE.
RUN display_fields IN THIS-PROCEDURE.
END.
ON VALUE-CHANGED OF select-1
DO:
DEFINE VARIABLE I AS INTEGER NO-UNDO.
CREATE BUFFER bh FOR TABLE SELF:SCREEN-VALUE.
RUN create_fields IN THIS-PROCEDURE.
CREATE QUERY qh.
qh:SET-BUFFERS(bh).
qh:QUERY-PREPARE("FOR EACH " + SELF:SCREEN-VALUE).
qh:QUERY-OPEN().
qh:GET-FIRST().
RUN display_fields IN THIS-PROCEDURE.
ASSIGN select-2:LIST-ITEMS = "".
DO I = 1 TO bh:NUM-FIELDS:
ASSIGN bf = bh:BUFFER-FIELD(I).
select-2:ADD-LAST(bf:NAME).
END.
END.
DELETE WIDGET-POOL "fields" NO-ERROR.
CREATE WIDGET-POOL "fields" PERSISTENT.
FOR EACH _file WHERE NOT _FROZEN NO-LOCK:
select-1:ADD-LAST(_file-name).
END.
ENABLE ALL WITH FRAME FRAME-B.
ENABLE ALL WITH FRAME FRAME-A.
WAIT-FOR WINDOW-CLOSE OF CURRENT-WINDOW.
DELETE WIDGET-POOL "fields".
PROCEDURE create_fields:
DEFINE VARIABLE h AS HANDLE NO-UNDO.
DEFINE VARIABLE I AS INTEGER NO-UNDO.
DEFINE VARIABLE J AS INTEGER NO-UNDO.
DEFINE VARIABLE wf AS HANDLE NO-UNDO.
DELETE WIDGET-POOL "fields".
CREATE WIDGET-POOL "fields" PERSISTENT.
DO I = 1 TO bh:NUM-FIELDS:
ASSIGN bf = bh:BUFFER-FIELD(I).
IF bf:EXTENT = 0 THEN
RUN create_field(I, bf, 0).
ELSE
DO J = 1 TO bf:EXTENT:
RUN create_field(I + J - 1, bf, J).
END.
END.
END PROCEDURE.
PROCEDURE display_fields:
DEFINE VARIABLE h AS HANDLE NO-UNDO.
IF NOT bh:AVAILABLE THEN
DO:
MESSAGE "Record is not available.".
RETURN.
END.
.
ASSIGN h = FRAME FRAME-B:FIRST-CHILD
h = h:FIRST-CHILD.
DO WHILE VALID-HANDLE(h):
IF h:TYPE = "FILL-IN" AND h:PRIVATE-DATA ? THEN
DO:
IF NUM-ENTRIES(h:PRIVATE-DATA) = 1 THEN
ASSIGN bf = bh:BUFFER-FIELD(h:PRIVATE-DATA)
h:SCREEN-VALUE = bf:STRING-VALUE.
ELSE
ASSIGN bf = bh:BUFFER-FIELD(ENTRY(1,h:PRIVATE-DATA))
h:SCREEN-VALUE = bf:STRING-VALUE(INTEGER(ENTRY(2,h:PRIVATE-DATA))).
ASSIGN h:VISIBLE = YES NO-ERROR.
END.
ASSIGN h = h:NEXT-SIBLING.
END.
END PROCEDURE.
PROCEDURE create_selected_fields:
DEFINE VARIABLE I AS INTEGER NO-UNDO.
DEFINE VARIABLE J AS INTEGER NO-UNDO.
DELETE WIDGET-POOL "fields".
CREATE WIDGET-POOL "fields" PERSISTENT.
DO I = 1 TO NUM-ENTRIES(select-2:SCREEN-VALUE IN FRAME FRAME-A):
ASSIGN bf = bh:BUFFER-FIELD(ENTRY(I,select-2:SCREEN-VALUE)).
IF bf:EXTENT = 0 THEN
RUN create_field(I, bf, 0).
ELSE
DO J = 1 TO bf:EXTENT:
RUN create_field(I + J - 1, bf, J).
END.
END.
END PROCEDURE.
PROCEDURE create_field:
DEFINE INPUT PARAMETER position AS INTEGER NO-UNDO.
DEFINE INPUT PARAMETER buffer-field AS HANDLE NO-UNDO.
DEFINE INPUT PARAMETER extent AS INTEGER NO-UNDO.
DEFINE VARIABLE h AS HANDLE NO-UNDO.
DEFINE VARIABLE wf AS HANDLE NO-UNDO.
CREATE TEXT h IN WIDGET-POOL "fields"
ASSIGN FRAME = FRAME FRAME-B:HANDLE
ROW = position
COL = 1
FORMAT = "X(17):"
SCREEN-VALUE = buffer-field:LABEL + (IF extent > 0 THEN STRING(extent," [99]") ELSE "")
VISIBLE = TRUE.
CREATE FILL-IN wf IN WIDGET-POOL "fields"
ASSIGN FRAME = FRAME FRAME-B:HANDLE
ROW = position
COL = 20
SIDE-LABEL-HANDLE = h
DATA-TYPE = bf:DATA-TYPE
PRIVATE-DATA = bf:NAME + (IF extent > 0 THEN STRING(extent,",99") ELSE "")
SENSITIVE = TRUE.
ASSIGN wf:VISIBLE = FALSE
wf:FORMAT = bf:FORMAT
wf:HELP = bf:HELP NO-ERROR.
END PROCEDURE.
.