Need to get all _files for all Connected DBs - Forum - OpenEdge General - Progress Community

Need to get all _files for all Connected DBs

 Forum

Need to get all _files for all Connected DBs

This question is answered

Is there a better way of traversing _file ?

REPEAT ix = 1 TO NUM-DBS:

  IF icDbName = "" OR icDbName = LDBNAME(ix) THEN DO:
    DELETE ALIAS dictdb.
    CREATE ALIAS dictdb FOR DATABASE VALUE (LDBNAME(ix)).
......
    iy = iy + 1.
  END. 
END.

I need to get a list of all tables we have in 4 different dbs. I Wonder if there is a dynamic way of doing this?

Verified Answer
  • The communities editor does not provide syntax checking ... I believe you need to add a TABLE keyword here:

    CREATE BUFFER hBuffer FOR TABLE „sports2000._file” .

    Architect of the SmartComponent Library and WinKit

    Consultingwerk Ltd.

All Replies
  • CREATE BUFFER hBuffer FOR „sports2000._file” .
    hQuery:SET-BUFFERS (hBuffer) .
    hQuery:QUERY-PREPARE (“for each _file”) .
    hQuery:QUERY-OPEN().
     
    Don’t forget to delete hBuffer and hQuery with every iteration of the look that iterates the the connected databases.

    Architect of the SmartComponent Library and WinKit

    Consultingwerk Ltd.

  • Aaah, thanks Mike. I new it should be easier 😊
     
    //Geir Otto
     
  • The communities editor does not provide syntax checking ... I believe you need to add a TABLE keyword here:

    CREATE BUFFER hBuffer FOR TABLE „sports2000._file” .

    Architect of the SmartComponent Library and WinKit

    Consultingwerk Ltd.

  • 10-4
     
  • The following gives all the tablenames... have needed to do this in the past...

    DEFINE VARIABLE hTblQ AS HANDLE NO-UNDO.
    DEFINE VARIABLE lValidQ AS LOGICAL NO-UNDO.
    DEFINE VARIABLE hTblHdl AS HANDLE NO-UNDO.
    DEFINE VARIABLE iDBCount AS INTEGER NO-UNDO.

    DEFINE TEMP-TABLE ttFile NO-UNDO
    field ttDBName AS CHAR
    field ttTblName AS CHAR.

    DbLoop:
    DO iDBCount = 1 TO NUM-DBS
    ON ERROR UNDO, RETURN ERROR:
    CREATE QUERY hTblQ.
    CREATE BUFFER hTblHdl FOR TABLE LDBNAME(iDBCount) + "._file".
    hTblQ:ADD-BUFFER(hTblHdl) NO-ERROR.
    lValidQ = hTblQ:QUERY-PREPARE("FOR EACH _file WHERE _tbl-type = 'T' BY _file-number").
    hTblQ:QUERY-OPEN NO-ERROR.
    TableLoop:
    REPEAT ON ERROR UNDO, RETURN ERROR:
    hTblQ:GET-NEXT.
    IF hTblQ:QUERY-OFF-END THEN LEAVE TableLoop.
    create ttFile.
    assign
    ttFile.ttDBName = LDBNAME(iDBCount)
    ttFile.ttTblName = hTblHdl:BUFFER-FIELD("_file-name"):BUFFER-VALUE.
    END. /*TableLoop*/
    END. /*iDBCount*/

    for each ttFile no-lock:
    disp ttDBName ttTblName.
    end.

  • You can also use the “dictdb” alias (or another of your choosing) and loop over the NUM-DBs. But if you do do that, then this code must be in a separate .P from the code that calls the CREATE ALIAS statement.
  • Object oriented way of doing this.

    USING OpenEdge.DataAdmin.Lang.Collections.IIterator FROM PROPATH.

    USING OpenEdge.DataAdmin.ITable  FROM PROPATH.

    USING OpenEdge.DataAdmin.ITableSet  FROM PROPATH.

    USING OpenEdge.DataAdmin.DataAdminService  FROM PROPATH.

    DEFINE VARIABLE service     AS DataAdminService NO-UNDO.

    DEFINE VARIABLE tblSet      AS ITableSet        NO-UNDO.

    DEFINE VARIABLE oiter       AS IIterator        NO-UNDO.

    DEFINE VARIABLE tbl         AS ITable           NO-UNDO.

    DEFINE VARIABLE iDBCount    AS INTEGER          NO-UNDO.

    DEFINE VARIABLE iTableCount AS INTEGER          NO-UNDO.

    DEFINE TEMP-TABLE ttFile NO-UNDO

       FIELD ttDBName  AS CHAR

       FIELD ttTblName AS CHAR.

    DO iDBCount = 1 TO NUM-DBS

       ON ERROR UNDO, RETURN ERROR:

       //get the service for the database

       service = NEW DataAdminService(LDBNAME(idbcount)).

       //get all the user tables for the database    

       tblSet = service:GetTables().

       //get the table count from the table collection

       iTableCount = tblSet:Count.

       //get the iterator for the tables

       oiter = tblSet:Iterator().

       DO WHILE oIter:HasNext():

           tbl = CAST(oIter:next(),ITable).

           CREATE ttFile.

           ASSIGN

               ttFile.ttDBName  = LDBNAME(iDBCount)

               ttFile.ttTblName = tbl:NAME.  

       END.    

       //make sure to delete the objects in each iteration to reset them

       DELETE OBJECT Service NO-ERROR.

       DELETE OBJECT tblset NO-ERROR.

       DELETE OBJECT tbl NO-ERROR.    

    END.

    FOR EACH ttFile NO-LOCK:

       DISP ttDBName ttTblName.

    END.

    CATCH e AS Progress.Lang.Error :

       MESSAGE e:GetMessage(1) SKIP

           e:callstack VIEW-AS ALERT-BOX.    

       UNDO, THROW e.

    END CATCH.