TMAP Source Code
----------------
This file contains the source code for the TMap database table mapping
utility, which uses the the IBM User Interface Class Library container
control to display some of the relevant characteristics of columns in
database tables.  To function correctly, TMap requires OS/2 2.11 (OS/2
2.10 plus corrective service), DB2/2 1.2, and C-Set++ 2.1.  This file
begins with a sample makefile (note debug is enabled), followed by
TMap.hpp, TMap.cpp, dbs.h, dbs.sqc, and finally TMap.def, each of which
is seperated from the others by a // comment beginning at the left hand
margin.  Please direct questions or comments via the Internet to the
author, Liston Tatum, at glt@dmt03.mcc.virginia.edu; time permitting, I
will try to respond.

This code is intended as an educational example only, and may be freely
distributed, with the proviso that the user assumes all liability;
anyone who does not agree with these terms is hereby denied license for
its use.
-------------------------------------------------------------------------
 # makefile - for TMap DB2 database table mapping utility
 #
 CPPFLAGS=/C /Q /N3 /G4 /Gm- /Ti /Fi /Fb- /Si /O-
 #
 # /C  does compile only
 # /Q  suppresses logo
 # /N3 quits after 3 errors
 # /G4 generates code for a 486
 # /Gm uses multi thread libraries
 # /Ti debug info
 # /Fi generates precompiled headers
 # /Fb generates browser file
 # /Si uses precompiled headers if available
 # /Pe suppresses #line macros, not compatible with /Fi- and /Si-
 # /O  optimizes
 #
 all:tmap
 #
 tmap:tmap.exe
 #
 #  link exe file              /Gd- static link to dde4mbs.lib
 #                             /Gd  dynamic link to dde4mbsi - the dll
 #                             /DE passes debug info - must precede /Q
 tmap.exe:tmap.obj dbs.obj
       icc /B /DE \
       tmap.obj dbs.obj \
       dde4muii.lib dde4cci.lib dde4mbsi.lib sql_dyn.lib tmap.def
 #
 #  compile tmap - /Fb generates browser file
 #
 tmap.obj:tmap.cpp tmap.hpp dbs.h
       icc $(CPPFLAGS) tmap.cpp
 #
 #  compile dbs - /Ss tolerates C++ //comments
 #
 dbs.obj:dbs.c dbs.h
       icc $(CPPFLAGS) /Ss dbs.c
 #
 #  sqlprep dbs.sqc - /# suppresses #LINE for debugging
 #                    /b create .bnd file instead of doing bind
 dbs.c:dbs.sqc dbs.h
       sqlprep dbs.sqc sample /# /b

 //TMap.hpp - show details of database table structure

 #ifndef TMAP_HPP
 #define TMAP_HPP

 #define SYS_TABLES      0x2000      //frame window - sysibm.systables
 #define SYS_TAB_ROWS    0x2010      //container - sysibm.systables
 #define SYS_COLUMNS     0x2100      //frame window - sysibm.syscolumns
 #define SYS_COL_ROWS    0x2110      //container - sysibm.syscolumns

 extern struct sqlca sqlca;          //for sqlca.sqlcode
 extern "c" {
    int connect(char *dbname);       //connect to dbname
    void reset();
    int openTabRow();
    int fetchTabRow(struct tabRow *tRow);
    void closeTabRow();
    int selectTabRem(char *creator, char *name, char (*tabRem)255Y);
    int openColRow(char *creator, char *name);
    int fetchColRow(struct colRow *cRow);
    void closeColRow();
 }

 class EMsg:public IObjectWindow {   //IMessagebox for error, exit
    public:
       EMsg();                       //show SQL error message text
       EMsg(char *msg);              //show our error string
 };

 class Database {
    public:
       Database(char *db) { if (connect(db)) EMsg(); }
       ~Database() { reset(); }
       class Relations {    //provider for Tables from sysibm.systables
          public:
             Relations() { if(openTabRow()) EMsg(); }
             int next() { return fetchTabRow(&tRow); }
             ~Relations() { closeTabRow(); }
             struct tabRow tRow;
       };
       class Attributes {   //supplies Details from sysibm.syscolumns
          public:
             Attributes(char *creator, char *name)
                   { if(openColRow(creator, name)) EMsg(); }
             int next() { return fetchColRow(&cRow); }
             ~Attributes() { closeColRow(); }
             struct colRow cRow;
       };
       class TabRem {       //remark for Details from sysibm.systables
          public:
             void getTabRem(char *creator, char *name, char (*tr)255Y)
                   { if (selectTabRem(creator, name, tr)) EMsg(); }
       };
 };

 class Tables:public IFrameWindow {  //tree view of creator, table name
    public:
       Tables(const IString *title);
       void showColumns(struct tabRow& tRow);
       class Creator:public IContainerObject {   //table creator object
          friend class Tables;
          public:
             Creator(char *creator, Tables *owner)
                :IContainerObject(IString(creator)) {};
             void handleOpen(IContainerControl *w) { w->expand(this); }
       };
       class Name:public IContainerObject {      //table name object
          friend class Tables;
          public:
             Name(struct tabRow *tRow, Tables *owner);
             void handleOpen(IContainerControl *w);
          private:
             IString creator, name;
       };
    private:
       IContainerControl *table;
       ICnrHandler event;
 };

 class Details:public IFrameWindow { //details view of table attributes
    public:
       Details(char *title, char *creator, char *name);
       void showColumns() { show(); }
       class Row:public IContainerObject {       //column details
          friend class Details;
          public:
             Row(struct colRow *cRow, Details *owner);
             void handleOpen(IContainerControl *w);
          private:
             IString type, length, nulls, rem;
       };
    private:
       IContainerControl *table;
       ICnrHandler event;
       IContainerColumn *cNam, *cTyp, *cLen, *cNul, *cRem;
 };
 #endif

 // TMap.cpp - show details of database table structure

 #define INCL_WINSYS           //include WINSYS stuff from os2.h
 #include <os2.h>              //WinQuerySysValue
 #include <sqlenv.h>           //access to sqlca.sqlcode
 #include <iobjwin.hpp>        //IObjectWindow for IMessageBox
 #include <imsgbox.hpp>        //IMessageBox for error message
 #include <iscroll.hpp>        //systemScrollBarWidth
 #include <ipoint.hpp>         //ISize
 #include <iapp.hpp>           //IApplication
 #include <icnrctl.hpp>        //IContainer control
 #include <icnrhdr.hpp>        //ICnrHandler - container control events
 #include <icnrcol.hpp>        //IContainerColumn
 #include <icolor.hpp>         //IColor
 #include <istring.hpp>        //IString
 #include <iframe.hpp>         //IFrameWindow
 #include <icmdhdr.hpp>        //ICommandEvent& ICommandHandler
 #include <ifont.hpp>          //IFont
 #include "dbs.h"              //table row structs shared with dbs.sqc
 #include "tmap.hpp"           //our class interface definitions

 void main(int argc, char *argvY) {
    if (argc != 2 || *argv1Y == '?') EMsg("format: TMAP dbname");
    Database DB2(argv1Y);           //if error, exit; else, connect
    IString title("Database: "); title += argv1Y;
    Tables DB2Tables(&title);        //application's main control panel
    IApplication::current().run();   //start PM message processing loop
 }

 Tables::Tables(const IString *title):IFrameWindow(*title,SYS_TABLES) {
    table = new IContainerControl(SYS_TAB_ROWS, this, this);
    setClient(table); event.handleEventsFor(table);
    Database::Relations *dbTbl = new Database::Relations();  //open csr
    int rc = dbTbl->next();                                  //get row
    while (rc != NOTFOUND) {
       Creator *creator = new Creator(dbTbl->tRow.creator, this);
       table->addObject(creator);          //first level tree node
       IString buf(dbTbl->tRow.creator);
       while (buf == dbTbl->tRow.creator && rc != NOTFOUND) {
          Name *name = new Name(&dbTbl->tRow, this);
          table->addObject(name, creator); //name; leaf node of creator
          rc = dbTbl->next();              //get next row
       }
    }
    delete dbTbl;                          //close the cursor
    table->setDeleteObjectsOnClose().showTreeTextView();
    IFont font("Helv",10); table->setFont(font);
    ISize xy = desktopWindow()->size(); int w = font.avgCharWidth()*40;
    sizeTo(ISize(w,xy.height() * .96));
    moveTo(IPoint(xy.width() * .98 - w, xy.height() * .02));
    table->setFocus(); show();             //take right side of screen
 };

 Tables::Name::Name(struct tabRow *tRow, Tables *owner)
       :IContainerObject(IString(tRow->name))
    { creator = tRow->creator; name = tRow->name; };

 void Tables::Name::handleOpen(IContainerControl *table) {
    IString title(creator); title += "."; title += name;
    Details *details = new Details(title, creator, name);
 };

 Details::Details(char *title, char *creator, char *name)
       :IFrameWindow (title, SYS_COLUMNS) {
    table=new IContainerControl(SYS_COL_ROWS, this, this);
    setClient(table); event.handleEventsFor(table);

    cNam=new IContainerColumn(IContainerObject::iconTextOffset());
    cTyp=new IContainerColumn(offsetof(Row,type));
    cLen=new IContainerColumn(offsetof(Row,length));
    cNul=new IContainerColumn(offsetof(Row,nulls));
    cRem=new IContainerColumn(offsetof(Row,rem));

    table->addColumn(&cNam->showSeparators().setHeadingText("name"));
    table->addColumn(&cTyp->showSeparators().setHeadingText("type"));
    table->addColumn(&cLen->showSeparators().setHeadingText("length"));
    table->addColumn(&cNul->showSeparators().setHeadingText("nulls"));
    table->addColumn(&cRem->showSeparators().setHeadingText("remark"));

    table->setDeleteObjectsOnClose().showDetailsView().setFocus();

    IFont font("Helv", 10); table->setFont(font);   //size logic OK if
    int charW = font.avgCharWidth();                //8 <= point <= 14
    int lineH = font.maxCharHeight() + table->lineSpacing();
    int dispH = font.maxCharHeight() * 1.75 + .5;   //column headings

    Database::TabRem *rem = new Database::TabRem();
    char tabRem255Y; rem->getTabRem(creator, name, &tabRem);
    if (tabRem0Y != '\0' ) {
       table->setTitle(tabRem).showTitle().showTitleSeparator();
       dispH += dispH;   //add title height; same as col headings above
    }
    Database::Attributes *att = new Database::Attributes(creator,name);
    int rc = att->next();                     //get first row
    while (rc != NOTFOUND) {
       Row *row = new Row(&att->cRow, this);
       table->addObject(row); dispH += lineH; //allow for display space
       rc = att->next();                      //get next row
    }
    delete att;                               //now close the cursor

    int dispW = cNam->show().displayWidth() + 2.5 * charW;   //name
    table->setDetailsViewSplit(cNam, dispW);                 //splitbar
    dispW += cTyp->show().displayWidth() + 3 * charW         //etc...
           + cLen->show().displayWidth() + 3 * charW
           + cNul->show().displayWidth()  + 3 * charW
           + WinQuerySysValue(HWND_DESKTOP, SV_CXVSCROLL);
    dispH += WinQuerySysValue(HWND_DESKTOP, SV_CYHSCROLL);
    IRectangle xy =
             frameRectFor(IRectangle(IPoint(0,0),ISize(dispW,dispH)));
    sizeTo(xy.topRight() - xy.bottomLeft()); show();
 };

 Details::Row::Row(struct colRow *cRow, Details *owner)
       :IContainerObject(IString(cRow->name)) {
    type = cRow->type; length = cRow->length;
    nulls = cRow->nulls; rem = cRow->rem;
 };

 void Details::Row::handleOpen(IContainerControl *table)
 {
    return;
 };

 EMsg::EMsg():IObjectWindow() {
    char msg512Y;
    if (sqlaintp(msg, 512, 0, &sqlca) < 0)
       strcpy(msg,"SQLAINTP error - can't retrieve message text");
    IMessageBox mbx(this);
    mbx.show(msg,mbx.cancelButton|mbx.errorIcon|mbx.applicationModal);
    exit(12);
 };

 EMsg::EMsg(char *msg):IObjectWindow() {
    IMessageBox mbx(this);
    mbx.show(msg,mbx.cancelButton|mbx.errorIcon|mbx.applicationModal);
    exit(12);
 };

 //dbs.h - interface structs shared by TMap and our database services

 #ifndef DBS_H
 #define DBS_H
 #include <sqlcodes.h>         //SQLcode defines for TMap and dbs
 #define NOTFOUND SQL_RC_W100

 struct tabRow {               //good stuff from sysibm.systables
    char creator9Y;           //table creator
    char name19Y;             //table name
 };

 struct colRow {               //of interest from sysibm.syscolumns
    char name19Y;             //column name
    char rem255Y;             //remark on column
    char type9Y;              //column data type
    short length;              //column data length
    char nulls2Y;             //nulls OK?
 };

 #endif

 //dbs.sqc - our database services access routines

 #include <string.h>
 #include <stdlib.h>
 #include <sql.h>
 #include <sqlenv.h>
 #include "dbs.h"     //our database interface data structures

 EXEC SQL INCLUDE sqlca;
 EXEC SQL BEGIN DECLARE SECTION;
    char *db, (*ptbcreator)9Y, (*ptbname)19Y;
 EXEC SQL END DECLARE SECTION;

 int connect(char *dbname) {               //connect to database
    sqleisig(&sqlca);                      //install signal handler
    db = dbname;                           //point DBRM at buffer
    EXEC SQL CONNECT TO :*db IN SHARE MODE;
    return sqlca.sqlcode;
 }

 void reset() { EXEC SQL CONNECT RESET; }  //close DB2 session

 int openTabRow() {                        //cursor on sysibm.systables
    EXEC SQL DECLARE TabRow CURSOR FOR     /* note test of not bound */
       SELECT CREATOR, NAME                /* condition (discovered  */
          FROM SYSIBM.SYSTABLES            /* on first open); useful */
          ORDER BY CREATOR, NAME;          /* for a DBA utility...   */
    EXEC SQL OPEN TabRow;
    if (sqlca.sqlcode == SQL_RC_E818 || sqlca.sqlcode == SQL_RC_E805) {
       sqlabind("dbs.bnd",db,NULL,"","USA",&sqlca); //not bound? try it
       if (sqlca.sqlcode != SQL_RC_OK)              //if bind fails,
          return sqlca.sqlcode;                     //give up, if not,
          else EXEC SQL OPEN TabRow;                //try open again
    }
    return sqlca.sqlcode;
 }

 int fetchTabRow(struct tabRow *t) { //get a row from sysibm,systables
    char *c;
    EXEC SQL BEGIN DECLARE SECTION;
       char (*ptcreator)9Y;         /* systables.creator char(8)  */
       char (*ptname)19Y;           /* systables.name varchar(18) */
    EXEC SQL END DECLARE SECTION;
    ptcreator = &t->creator; ptname = &t->name;     //tell DBRM where
    EXEC SQL FETCH TabRow INTO :ptcreator, :ptname; //to put the data
    c = strchr(t->creator,' ');                     //now truncate
    if (c != '\0') *c = '\0';                       //extra spaces
    return sqlca.sqlcode;
 }

 void closeTabRow(void) { EXEC SQL CLOSE TabRow; }  //sysibm.systables

 int selectTabRem  //get remark on creator.name from sysibm.systables
    (char (*creator)9Y, char (*name)19Y, char (*rem)255Y) {
    EXEC SQL BEGIN DECLARE SECTION;
       char (*ptbrem)255Y;          /* remarks on table, if any   */
       short tbrem_nid;              /* null indicator             */
    EXEC SQL END DECLARE SECTION;
    ptbrem = rem; ptbcreator = creator; ptbname = name;
    EXEC SQL SELECT REMARKS INTO :ptbrem :tbrem_nid
       FROM SYSIBM.SYSTABLES
       WHERE CREATOR = :ptbcreator AND NAME = :ptbname;
    if (tbrem_nid < 0) *rem0Y = NULL;                    //truncate?
    return sqlca.sqlcode;
 }

 int openColRow(char (*cre)9Y, char (*nam)19Y) {  //sysibm.syscolumns
    ptbcreator = cre; ptbname = nam;
    EXEC SQL DECLARE ColRow CURSOR FOR
       SELECT NAME, REMARKS, COLTYPE, LENGTH, NULLS, COLNO
          FROM SYSIBM.SYSCOLUMNS
          WHERE TBCREATOR = :ptbcreator AND TBNAME = :ptbname
          ORDER BY COLNO;
    EXEC SQL OPEN ColRow;
    return sqlca.sqlcode;
 }

 int fetchColRow(struct colRow *c) { //get row from sysibm.syscolumns
    EXEC SQL BEGIN DECLARE SECTION;
       char (*pcname)19Y;           /* name varchar(18)        */
       char (*pcrem)255Y;           /* remarks varchar(254)    */
       short crem_nid;               /* remarks null indicator  */
       char (*pctype)9Y;            /* coltype char(8)         */
       short clength;                /* length smallint         */
       char (*pcnulls)2Y;           /* nulls char(1)           */
    EXEC SQL END DECLARE SECTION;
    pcname = &c->name; pcrem = &c->rem;       //point at fields in
    pctype = &c->type; pcnulls = &c->nulls;   //syscolumn row structure
    EXEC SQL FETCH ColRow INTO
      :pcname, :pcrem :crem_nid, :pctype, :clength, :pcnulls;
    c->length = clength;
    if (crem_nid < 0) c->rem0Y = NULL;       //null remark? trunc it!
    return sqlca.sqlcode;
 }

 void closeColRow(void) { EXEC SQL CLOSE ColRow; }  //sysibm.syscolumns

 //that's it, except TMap.def which follows:

 NAME    TMAP        WINDOWAPI

 DESCRIPTION 'display database table structure'

 CODE    LOADONCALL MOVEABLE
 DATA    MOVEABLE   MULTIPLE