How to do for each loop on list of tables

[mewhittaker]

This is my first post here, so I hope I haven't skipped any formality steps. I have searched on this subject but with no luck.

I want to be able to perform a For Each loop on every table in the database that has the field "company". Here is what I have so far:

DEF VAR tablelist AS CHAR.

FOR EACH _field
WHERE _field._field-name = "company",
EACH _file OF _field:
tablelist = tablelist + _file._file-name + ",".
END.

That gives me a list of comma-separated table names. How can I perform a FOR EACH loop on each table in that list? What I want to be able to do is change the value of "company" on all records in all tables that have that field. Ex:

FOR EACH tablename
WHERE tablename.company = "300":
tablename.company = "400".
END.

I want it to do that for every table in the list. Can it be done?

Thanks for any and all help.

 

[LarryD]

If you have a development system, here is a simple method (note the no-undo and NO-LOCK):

DEF VAR tablelist AS CHAR no-undo.

FOR EACH _field NO-LOCK
WHERE _field._field-name = "company",
EACH _file NO_LOCK OF _field:
tablelist = tablelist + (if tablelist <> "" then "," else "") + _file._file-name.
END.

def stream dotp.
def var i as int no-undo.

do i = 1 to num-entries(tablelist,","):

output stream dotp to value( "./changecomp.p" ).

put stream dotp unformatted
"for each " entry(i,tablelist,",") " EXCLUSIVE-LOCK" skip
"where " entry(i,tablelist,",") ".company = '300':" skip
entry(i,tablelist,",") ".company = '400'." skip
"end." skip.

output stream dotp close.
pause 1 no-message. /* give time to os to close file */

run ./changecomp.p .

end.

 

[mewhittaker]

It never occurred to me to take that approach, let progress generate all the code for me. That works great!!!

Thanks so much!!

 

[mirekpa]

you can write source code like this:

METHOD PUBLIC CHAR getDBLogNameForFile
     (INPUT cFileName AS CHAR):

DEF VAR i           AS INTEGER.
DEF VAR dbLogName   AS CHAR.
DEF VAR txtLogName   AS CHAR.
DEF VAR bhFileSys   AS HANDLE.
DEF VAR qhFileSys   AS HANDLE.
DEF VAR lQueryOk    AS LOGICAL.
DEF VAR cQStr       AS CHAR.

REPEAT i = 1 TO NUM-DBS:
    txtLogName = LDBNAME(i).
    CREATE BUFFER bhFileSys FOR TABLE txtLogName + "._File".
    CREATE QUERY qhFileSys.
    qhFileSys:SET-BUFFERS(bhFileSys).
    cQStr = "FOR EACH " + txtLogName + "._File WHERE " + txtLogName + "._File._File-name = '" + cFileName + "' NO-LOCK".
    lQueryOk = qhFileSys:QUERY-PREPARE(cQStr).
    IF (lQueryOk) THEN
    DO:
        qhFileSys:QUERY-OPEN.
        DO WHILE qhFileSys:GET-NEXT:
            /*
            dbLogName = txtLogName.
           
              your code
             
              bhFileSys:BUFFER-FIELD(your field name):BUFFER-VALUE .....
           
            */
        END.
    END.
    qhFileSys:QUERY-CLOSE.
    DELETE OBJECT qhFileSys.
    DELETE OBJECT bhFileSys.
END.

RETURN dbLogName.
END METHOD.

 

[palthe]

you can write source code like this:

METHOD PUBLIC CHAR getDBLogNameForFile
     (INPUT cFileName AS CHAR):
 
DEF VAR i           AS INTEGER.
DEF VAR dbLogName   AS CHAR.
DEF VAR txtLogName   AS CHAR.
DEF VAR bhFileSys   AS HANDLE.
DEF VAR qhFileSys   AS HANDLE.
DEF VAR lQueryOk    AS LOGICAL.
DEF VAR cQStr       AS CHAR.
 
REPEAT i = 1 TO NUM-DBS:
    txtLogName = LDBNAME(i).
    CREATE BUFFER bhFileSys FOR TABLE txtLogName + "._File".
    CREATE QUERY qhFileSys.
    qhFileSys:SET-BUFFERS(bhFileSys).
    cQStr = "FOR EACH " + txtLogName + "._File WHERE " + txtLogName + "._File._File-name = '" + cFileName + "' NO-LOCK".
    lQueryOk = qhFileSys:QUERY-PREPARE(cQStr).
    IF (lQueryOk) THEN
    DO:
        qhFileSys:QUERY-OPEN.
        DO WHILE qhFileSys:GET-NEXT:
            /*
            dbLogName = txtLogName.
 
              your code
 
              bhFileSys:BUFFER-FIELD(your field name):BUFFER-VALUE .....
 
            */
        END.
    END.
    qhFileSys:QUERY-CLOSE.
    DELETE OBJECT qhFileSys.
    DELETE OBJECT bhFileSys.
END.
 
RETURN dbLogName.
END METHOD.

Agreed. Use dynamic querying to do a loop on a variable as tablename.

 

[LarryD]

While this may fall into the 'beating a dead horse' category....

Dynamic queries as shown above are fine for later versions of Progress (don't recall when they were introduced). However, the original request did not specify which version of Progress they are using, so the 'works in all versions' answer was given.

 

[TomBascom]

While this may fall into the 'beating a dead horse' category....

Dynamic queries as shown above are fine for later versions of Progress (don't recall when they were introduced). However, the original request did not specify which version of Progress they are using, so the 'works in all versions' answer was given.

Dynamic queries were introduced in v9.

The works in all versions technique is a good one and in some ways is easier to understand but it does have the disadvantage of requiring a license that can compile code -- whereas the dynamic query will work even in a runtime only situation.

Horses for courses