We have a browse which has multiple fields from multiple tables.
We want to make a new dynamic query having same fields from same tables as browse's, and we want to save all the records in the dynamic query to a file.
Is there a way to do that?
in which progress-version are you? OE10 has some interesting new features to achieve this.
We are using OE 10.1B.
If you're working on temp-tables, you could use a dataset and write-xml to export your data. If you just want a flat text file with the contents, you can use something like this :
DEFINE VARIABLE hQuery AS HANDLE NO-UNDO.
OUTPUT TO value(SESSION:TEMP-DIRECTORY + "query.txt").
CREATE QUERY hQuery .
hquery:SET-BUFFERS(BUFFER table1:HANDLE, BUFFER table2:HANDLE).
hquery:QUERY-PREPARE("FOR EACH table1 NO-LOCK WHERE table1.field1 = 'string', FIRST table2 NO-LOCK WHERE table2.field2 = table1.field2").
hQuery:QUERY-OPEN().
hQuery:GET-FIRST().
DO WHILE NOT hQuery:QUERY-OFF-END :
EXPORT table1.field3 table2.field3.
hQuery:GET-NEXT().
END.
hQuery:QUERY-CLOSE().
OUTPUT CLOSE.
DELETE OBJECT hQuery.
Thx for the answer. I'm using something similar to that. I'm trying to export only the fields showed in the browser. Below you can see part of my code. I only need to get the WHERE and BY clauses of the dynamic browse's query. If I can find the way, my code will be complete.
====================================
/I use max 5 buffers to be able to support up to five tables in browse/
IF VALID-HANDLE(hbuffer[1]) THEN
DO:
repeat i=1 to 5:
hbuffer:BUFFER-RELEASE() no-error.
DELETE OBJECT(hbuffer) no-error.
end.
DELETE OBJECT(hquery) no-error.
END.
CREATE QUERY hquery.
repeat i=1 to myTableCount:
CREATE BUFFER hbuffer FOR TABLE ENTRY(i,"}"," ") NO-ERROR. /Creates buffers for each table in the browse/
end.
if myTableCount=1 then hquery:SET-BUFFERS(hbuffer[1]) NO-ERROR.
if myTableCount=2 then hquery:SET-BUFFERS(hbuffer[1],hbuffer[2]) NO-ERROR.
if myTableCount=3 then hquery:SET-BUFFERS(hbuffer[1],hbuffer[2],hbuffer[3]) NO-ERROR.
if myTableCount=4 then hquery:SET-BUFFERS(hbuffer[1],hbuffer[2],hbuffer[3],hbuffer[4]) NO-ERROR.
if myTableCount=5 then hquery:SET-BUFFERS(hbuffer[1],hbuffer[2],hbuffer[3],hbuffer[4],hbuffer[5]) NO-ERROR.
if query-value="" then
do:
query-value="FOR ".
REPEAT i=1 TO myTableCount:
/*******************************************************************
THIS PART OF THE CODE IS WHAT IS MISSING.
I HAVE TO GENERATE THE QUERY-VALUE IN HERE.
*******************************************************************/
END.
end.
hquery:QUERY-PREPARE(query-value) .
hquery:QUERY-OPEN.
isContinue=hquery:GET-FIRST().
OUTPUT TO value(SESSION:PARAMETER + "temp/query.txt").
DEFINE VARIABLE xColumn AS HANDLE NO-UNDO.
DEFINE VARIABLE xColumnHandles AS CHARACTER NO-UNDO.
DEFINE VARIABLE iColNum AS INTEGER NO-UNDO.
DEFINE VARIABLE iCounter AS INTEGER NO-UNDO.
ASSIGN xColumn = BROWSE :FIRST-COLUMN.
DO WHILE VALID-HANDLE(xColumn):
ASSIGN xColumnHandles = IF xColumnHandles <> "":U THEN
xColumnHandles + ",":U + STRING(xColumn)
ELSE
STRING(xColumn).
xColumn = xColumn:NEXT-COLUMN.
END.
ASSIGN iColNum = NUM-ENTRIES(xColumnHandles).
DO iCounter = 1 TO iColNum:
ASSIGN xColumn = WIDGET-HANDLE(ENTRY(iCounter,xColumnHandles)).
if xColumn:LABEL<>? then put unformatted xColumn:LABEL.
PUT UNFORMATTED "ø".
END.
PUT SKIP.
DO WHILE isContinue:
iCalculatedField=0.
DO I = 1 TO iColNum:
ASSIGN xColumn = WIDGET-HANDLE(ENTRY(i,xColumnHandles)).
iCounter= LOOKUP(xColumn:table,"}"," ").
if iCounter>0 then
do:
ASSIGN bfield = hbuffer:BUFFER-FIELD(xColumn:NAME).
IF bfield:BUFFER-VALUE<>? THEN put unformatted bfield:BUFFER-VALUE.
PUT UNFORMATTED "ø".
end.
else
do:
iCalculatedField=iCalculatedField + 1.
run writeCalculatedField. /If there is calculated field, a procedure writes it's content/
PUT UNFORMATTED "ø".
end.
END.
PUT UNFORMATTED SKIP.
isContinue=hquery:GET-NEXT().
END.
OUTPUT CLOSE.
IF VALID-HANDLE(hbuffer[1]) THEN
DO:
repeat i=1 to 5:
hbuffer:BUFFER-RELEASE() no-error.
DELETE OBJECT(hbuffer) no-error.
end.
DELETE OBJECT(hquery) no-error.
END.
If your browse's query is defined dynamically, you can use hQuery:PREPARE-STRING. (The handle of the browse query can be found by browse:query).
If the query of the browse is static, you'll have to figure something out.