Dynamic Query w/ linked tables

[redwards1966]

Hi all,
After having spent most of my career with MS products, I'm just starting along the Progress road getting my feet wet and I'm having a little trouble.

I have already written a procedure that will read query information from an ini file and export said query to a delimited text file based on command line filters etc. This all works great, but now I want the query to be able to support data from more than one table.

As an example (I'll use the trusty Sports2000 db) I'd like to be able to export all Orders that belong to a customer whose state = 'NH'.

Currently I'm doing something like this which would support a single table. ExportRecord is just my function that returns the string of fields based on the current record.

DEF VAR vQuery AS HANDLE NO-UNDO.
DEF VAR vBuffer AS HANDLE NO-UNDO.

DEF STREAM outfile.

OUTPUT STREAM outfile TO "C:\customer.txt".

/* ***************************  Main Block  *************************** */
CREATE QUERY vQuery.
CREATE BUFFER vBuffer FOR TABLE "Customer".
vQuery:ADD-BUFFER(vBuffer).
vQuery:QUERY-PREPARE("FOR EACH Customer NO-LOCK").
vQuery:QUERY-OPEN().
vQuery:GET-NEXT().
DEF VAR vOutStr AS CHARACTER NO-UNDO.
        
REPEAT WHILE NOT vQuery:QUERY-OFF-END:
    vOutStr = ExportRecord(INPUT vQuery:GET-BUFFER-HANDLE(1), ",") + ",".
    PUT STREAM outfile UNFORMATTED vOutStr SKIP.
    vQuery:GET-NEXT().
END.

How could I modify the above code to behave for the Order/Customer sample I mentioned? I'm imagining it has something to do with the create buffer and query-prepare statements, but if I try to change them to add order and the proper query it's giving me errors.

Anyone?

 

[parul]

I have changed the code, have a look.

-Parul

DEF VAR vQuery AS HANDLE NO-UNDO.
DEF VAR vBuffer AS HANDLE NO-UNDO.

DEF STREAM outfile.

OUTPUT STREAM outfile TO "C:\customer.txt".

/* *************************** Main Block *************************** */
CREATE QUERY vQuery.
CREATE BUFFER vBuffer FOR TABLE "Customer".
/*set-buffers instead of add-buffers*/
vQuery:set-BUFFERs(vBuffer, buffer order:handle).
vQuery:QUERY-PREPARE("FOR EACH Customer NO-LOCK, each order of customer").
vQuery:QUERY-OPEN().
vQuery:GET-NEXT().
DEF VAR vOutStr AS CHARACTER NO-UNDO.

REPEAT WHILE NOT vQuery:QUERY-OFF-END:
/* you can access the order table by GET-BUFFER-HANDLE(2) */
vOutStr = ExportRecord(INPUT vQuery:GET-BUFFER-HANDLE(1), ",") + ",".
PUT STREAM outfile UNFORMATTED vOutStr SKIP.
vQuery:GET-NEXT().
END.

 

[redwards1966]

Thanks for your help. This worked out great for me. I ended up doing it the following way so I could pass in the tables dynamically. vTableNames contains the comma delimited list of tables that vQuery references.

CREATE QUERY hQuery.
DEF VAR iTable AS INTEGER NO-UNDO.
DO  iTable = 1 TO NUM-ENTRIES(vTableNames):
    CREATE BUFFER hBuffer FOR TABLE ENTRY(iTable,vTableNames).
    hQuery:ADD-BUFFER(hBuffer).
END.
hQuery:QUERY-PREPARE(vQuery).

 

[D.Cook]

Hi, I'm working to do this exact thing, and glad to see I've ended up in the same place!

Just wondering one thing though, how did you write the function ExportRecord? I can't seem to find a simple solution.

... *time passes*

well here's my solution anyway, hope it helps someone!

function ExportRecord returns char (hp_buffer as handle):
   def var vr_value as char no-undo init "".
   def var vr_loop as int no-undo.
   do vr_loop = 1 to hp_buffer:num-fields:
      if hp_buffer:buffer-field(vl_loop):buffer-value eq ? then 
         vr_value = vr_value + '? '.
      else if hp_buffer:buffer-field(vl_loop):data-type eq "character" then
         vr_value = vr_value + '"' + hp_buffer:buffer-field(vl_loop):buffer-value + '" '.
      else
         vr_value = vr_value + string(hp_buffer:buffer-field(vl_loop):buffer-value) + ' '.
   end.
   return vr_value.
end function.