Working with OE database, schema holder, and foreign database.

Is there a way to identify which are connected to know which one is the schema holder to know which one does / does not have data?

Is there a way to differentiate the true (foreign) database from the schema holder?
 

When working with foreign(MS Sql Server/Oracle) databases from within the ABL through the DataServer layer it is necessary to be aware of which database to reference data in and which one contains the actual schema.

When working with generic code that doesn't hard-code the table/field names there are a number of tells that you can use to identify which one of the connected databases is the schema holder and which one you should actually be querying data from.

The below code demonstrates looping through the connected databases to determine which is the Schema holder and which is the actual foreign database.

DEFINE VARIABLE iDb     AS INTEGER     NO-UNDO.
DEFINE VARIABLE hDb     AS HANDLE      NO-UNDO.
DEFINE VARIABLE iDbs    AS INTEGER     NO-UNDO.
DEFINE VARIABLE hBuff   AS HANDLE      NO-UNDO.
DEFINE VARIABLE hQuery  AS HANDLE      NO-UNDO.
DEFINE VARIABLE cResult AS CHARACTER   NO-UNDO
    INITIAL "Foreign Databases:".

DO iDb = 1 TO NUM-DBS:
    /* Create a buffer for the _db table.  Most Progress databases contain
       a single record in the _db table, schema holder databases contain 
       multiple _db records.  A single PROGRESS type _db record and one _db 
       record for each foreign database it stores schema for. 
       
       The code below is querying this table to find the associated foreign 
       database records and adding them to a list */

    /* Note that if the below code fails you know right there that you're looking at the 
       foreign db, because it has no Progress metaschema tables in it like _db, _file, etc */
    CREATE BUFFER hDb FOR TABLE LDBNAME(iDb) + "._db" NO-ERROR.
    IF VALID-HANDLE(hDb) THEN DO:
        /* Note that, since there are multiple _db records in schema holder databases, you 
           can execute the FIND-UNIQUE method or FIND statement without specifying a directional 
           option (FIRST,LAST,etc) and this will return error 3166(below)

           More than one <table> records found by a unique FIND. (3166)
        */
        hDb:FIND-UNIQUE() NO-ERROR.
        IF ERROR-STATUS:GET-NUMBER(1)EQ 3166 THEN DO:
            CREATE QUERY hQuery.
            hQuery:SET-BUFFERS(hDb).
            hQuery:QUERY-PREPARE("FOR EACH _db").
            hQuery:QUERY-OPEN().
        
            DO WHILE hQuery:GET-NEXT():
                /* If the dbtype is not Progress this is the reference to the 
                   foreign db */
                IF hDb::_db-type NE "PROGRESS" THEN
                    cResult = cResult + CHR(10) +
                              hDb::_db-name + "(" + hDb::_db-type + ") --- " +
                              "Schema Holder: " + LDBNAME(iDb).
            END.
        END.
        IF VALID-HANDLE(hQuery) AND 
           hQuery:IS-OPEN THEN
            hQuery:QUERY-CLOSE() NO-ERROR.
        DELETE OBJECT hQuery NO-ERROR.
    END.
    
    /* Bonus code */
    /* This code queries the actual foreign database - 
       Substitute a table/field in your database for customer/name */
    IF DBTYPE(LDBNAME(iDb)) NE "PROGRESS" THEN DO:
        CREATE BUFFER hBuff FOR TABLE LDBNAME(iDb) + ".customer" NO-ERROR.
        hBuff:FIND-FIRST() NO-ERROR.
        IF hBuff:AVAILABLE THEN
            MESSAGE "Customer: " + hBuff::NAME
                VIEW-AS ALERT-BOX INFORMATION BUTTONS OK.
    END.
    DELETE OBJECT hBuff NO-ERROR.
    DELETE OBJECT hDb   NO-ERROR.
END.
MESSAGE cResult
    VIEW-AS ALERT-BOX INFORMATION BUTTONS OK.