Need to get all _files for all Connected DBs

Posted by goo on 23-Apr-2018 03:12

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?

Posted by Mike Fechner on 23-Apr-2018 03:23

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” .

All Replies

Posted by Mike Fechner on 23-Apr-2018 03:16

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.

Posted by goo on 23-Apr-2018 03:20

Aaah, thanks Mike. I new it should be easier 😊
 
//Geir Otto
 

Posted by Mike Fechner on 23-Apr-2018 03:23

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” .

Posted by goo on 23-Apr-2018 03:26

10-4
 

Posted by JonathanWilson on 23-Apr-2018 04:22

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.

Posted by Peter Judge on 23-Apr-2018 09:06

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.

Posted by Kondra Mohan Raju on 23-Apr-2018 09:25

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.

This thread is closed