Catalog Functions

There are several system tables in a relational database which record information about tables, columns, etc. We call these system tables catalogs. When you want to know the schema of tables and indexes in a database, you can look at the catalogs to get the information.

Using the catalog functions you can create a view of the database dynamically without having to know ahead of time what tables are present and what columns are present in the tables.

Retrieving table names

To retrieve a list of tables contained in the database use the dbListTables function.  A successful connection to the database must be established first using dbConnect or dbConnectDSN.

Example:

DEF pdb,pTables as POINTER
pdb = dbConnectDSN("XYZ CORP","")
IF pdb <> NULL    pTables = dbListTables(pdb)
    PRINT "Tables:"
    IF pTables <> NULL
        FOR temp = EACH pTables as STRING
            PRINT #temp
        NEXT
        ListRemoveAll(pTables,TRUE)
    ENDIF
    dbDisconnect(pdb)
ENDIF

As indicated in the above example dbListTables returns a pointer to an Emergence BASIC linked list. The list contains the names of all of the tables in the database as STRING types.  After you are done with the list remember to delete it with ListRemoveAll(listvar, TRUE).

Retrieving column names

The names of the columns contained in a table, or returned in a result set, can be obtained by using dbListColumns. A successful connection to the database must be established first using dbConnect or dbConnectDSN.

Examples:

pColumns = dbListColumns(pdb,"",hstmt)
IF pColumns <> NULL
    FOR temp2 = EACH pColumns as STRING
        PRINT #temp2,", ",
    NEXT
    PRINT
    ListRemoveAll(pColumns,TRUE)
ENDIF
 
pColumns = dbListColumns(pdb,"USERS")
IF pColumns <> NULL
    FOR temp2 = EACH pColumns as STRING
        PRINT #temp2,", ",
    NEXT
    PRINT
    ListRemoveAll(pColumns,TRUE)
ENDIF

As indicated in the above example dbListColumns returns a pointer to an Emergence BASIC linked list. After you are done with the list remember to delete it with ListRemoveAll(listvar, TRUE).

Getting a count of rows

The count of rows in a table, known as its cardinality, can be determined with the dbCardinality funtion.

Example:

DEF pdb,pTables as POINTER
pdb = dbConnectDSN("XYZ CORP","")
IF pdb <> NULL
    pTables = dbListTables(pdb)
    PRINT "Tables:"
    IF pTables <> NULL
        FOR temp = EACH pTables as STRING
            PRINT #temp
            PRINT "Cardinality:",dbCardinality(pdb,#temp)
        NEXT
        ListRemoveAll(pTables,TRUE)
    ENDIF
    dbDisconnect(pdb)
ENDIF