Subject: (makedic.4gl) Columns info for tables!
From: [email-masked] (Mario Estrada)
Date: 10 Oct 1996 14:33:27 -0400
Newsgroups: comp.databases.informix

This is a multi-part message in MIME format.

--------------47046CAB3449
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit

Hi, this is the one containing the makedic.4gl, sorry i made a mistake,
 
 Somebody asked about obtaining information from tables, I as thinking
 if he wanted it for documentation pourpose like:
 
  <Put the Project Name here>    DICCTIONARY STRUCTURE
  <Put your company name here>

------------------------------------------------------------------------
 
      Entity: ALUXBOL            Type : TABLE
      Description:
        Sequence    ColName        ColType  Length  Scale   Allow Null
            1   numboleta           INTEGER       4    0        N
            2   codpersona          INTEGER       4    0        N
            3   calificacion        CHAR          1    0        N
 .
 .
 .
 
 If you want this type of documentation, here is a 4gl program i wrote
 2 years ago, i hope this help.(4gl attached as file makedic.4gl).
 
 I hope this help,
 
 Regards,
 
 --------------------------------------------------------------------
 Mario Estrada Rosa  | LESCO, S.A. (Guatemala City ,Central America)
   Systems Engineer  | [email-masked]
                     | Phone (502) 3318116
                     | Fax   (502) 3348447
                     |
                     | SISTECO, S.A.
                     | [email-masked]
                     | Phone (502) 3340214
                     | Fax   (502) 3344835
                     | INFORMIX & SUN MicroSystems
                     | Support Department
 ---------------------------------------------------------------------

--------------47046CAB3449
Content-Type: text/plain; charset=us-ascii; name="MAKEDIC.4GL"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline; filename="MAKEDIC.4GL"

####################################################################
#
# Program: makedic.4gl
# Author : Mario Estrada
# Date   : Sept. 18 1994
#
# Sintax : <makedic> <database-name> <Project-name> <output-file>
#
# Comments:  
#          Generates a Dictionary File, for a better presentation
#          in your documentation.
# 
# You can compile it: c4gl makedic.4gl -o makedic.4ge and then run it
# as follows:
#      makedic.4ge <database-name> <Project-name> <output-file>
#
# Or compile it with: fglpc makedic.4gl and then run it as follows: 
#      
#      fglgo makedic <database-name> <Project-name> <output-file>
#
GLOBALS
   DEFINE g_tabname  		CHAR(20),
	  g_dbname   		CHAR(20),
 	  g_tabid    		INTEGER,
	  g_trans    		SMALLINT,
	  g_numparam 		INTEGER,
	  g_param1   		CHAR(02),
	  g_param3   		CHAR(02),
	  g_tecla_val      	INTEGER,
	  g_tecla_nom       	CHAR(10),
	  g_coltype		CHAR(05),
	  g_nomemp		CHAR(40), 
	  g_output		CHAR(40),
	  gr_systables          RECORD
               tabname              char(18),                
               owner                char(8),  
               dirpath              char(64),                                
               tabid                integer,                                 
               rowsize              smallint,                                
               ncols                smallint,                                
               nindexes             smallint,                                
               nrows                integer,                                 
               created              date,                                    
               version              integer,                                 
               tabtype              char(1),                                 
               audpath              char(64)
	   END RECORD,
	  gr_syscolumns         RECORD 
              colname              char(18),                                
              tabid                integer,                                 
              colno                smallint,                                
              coltype              smallint,                                
              collength            smallint                                
	 END RECORD,
	  gr_sysindexes         RECORD 
              idxname              char(18),                                
              owner                char(8),                                 
              tabid                integer,                                 
              idxtype              char(1),                                 
              clustered            char(1),                                 
              part1                smallint,                                
              part2                smallint,                                
              part3                smallint,                                
              part4                smallint,                                
              part5                smallint,                                
              part6                smallint,                                
              part7                smallint,                                
              part8                smallint                                
	END RECORD
	  
END GLOBALS

DEFINE 
	V RECORD
		tabid	      INTEGER,
		tabname       CHAR(18),
		tabtype       CHAR(15), 
           	sequence      SMALLINT,
	 	colname	      CHAR(18),
		colType	      CHAR(10),
	        collength     INTEGER,
		colScale      INTEGER,
	        AllowNull     CHAR(01)
	END RECORD 

MAIN

 LET g_dbname = Arg_Val(1)
 DATABASE g_dbname
 LET g_nomemp = Arg_val(2)
 LET g_output = Arg_val(3)

 DECLARE SysTab SCROLL CURSOR FOR
	SELECT * INTO gr_systables.*
	FROM SYSTABLES
	WHERE TABID > 99
	ORDER BY TABNAME

DECLARE SysIndex SCROLL CURSOR FOR
	SELECT * INTO gr_sysindexes.*
	FROM SYSINDEXES
	WHERE TABID > 99

DECLARE ColTab SCROLL CURSOR FOR
	SELECT * INTO gr_syscolumns.*
	FROM SYSCOLUMNS
	WHERE TABID = gr_systables.tabid

{ ENTITIES }
START REPORT SysTabRep TO g_output  

  FOREACH SysTab

	LET V.sequence = 0

	FOREACH ColTab

		LET v.tabid    = gr_systables.tabid
		LET v.tabname  = UPSHIFT(gr_systables.tabname)
		LET v.tabtype  = TABTYPE(gr_systables.tabtype)
	        LET v.sequence = v.sequence + 1
		LET v.colname  = gr_syscolumns.colname
		CALL Col_Type(gr_syscolumns.collength,
			      gr_syscolumns.coltype)
			RETURNING v.coltype,v.collength,v.colscale,v.allownull

		OUTPUT TO REPORT SysTabRep(v.*)

	END FOREACH
        
  END FOREACH

  FOREACH SysIndex

		LET v.tabid    = gr_sysindexes.tabid
		LET v.tabname  = UPSHIFT(gr_sysindexes.idxname)
		LET v.tabtype  = TABTYPE(gr_sysindexes.idxtype)

		{ Part1 }
	        CALL GET_COL(gr_sysindexes.tabid,
			     gr_sysindexes.part1) RETURNING STATUS
		IF STATUS = 0 THEN
		       CALL Col_Type(gr_syscolumns.collength,
			      gr_syscolumns.coltype)
			RETURNING v.coltype,v.collength,v.colscale,v.allownull
			LET v.sequence = 01
		        LET v.colname  = gr_syscolumns.colname
		        OUTPUT TO REPORT SysTabRep(v.*)
		END IF

		{ Part2 }
	        CALL GET_COL(gr_sysindexes.tabid,
			     gr_sysindexes.part2) RETURNING STATUS
		IF STATUS = 0 THEN
		       CALL Col_Type(gr_syscolumns.collength,
			      gr_syscolumns.coltype)
			RETURNING v.coltype,v.collength,v.colscale,v.allownull
			LET v.sequence =02 
		        LET v.colname  = gr_syscolumns.colname
		        OUTPUT TO REPORT SysTabRep(v.*)
		END IF

		{ Part3 }
	        CALL GET_COL(gr_sysindexes.tabid,
			     gr_sysindexes.part3) RETURNING STATUS
		IF STATUS = 0 THEN
		       CALL Col_Type(gr_syscolumns.collength,
			      gr_syscolumns.coltype)
			RETURNING v.coltype,v.collength,v.colscale,v.allownull
			LET v.sequence = 03
		        LET v.colname  = gr_syscolumns.colname
		        OUTPUT TO REPORT SysTabRep(v.*)
		END IF

		{ Part4 }
	        CALL GET_COL(gr_sysindexes.tabid,
			     gr_sysindexes.part5) RETURNING STATUS
		IF STATUS = 0 THEN
		       CALL Col_Type(gr_syscolumns.collength,
			      gr_syscolumns.coltype)
			RETURNING v.coltype,v.collength,v.colscale,v.allownull
			LET v.sequence = 05
		        LET v.colname  = gr_syscolumns.colname
		        OUTPUT TO REPORT SysTabRep(v.*)
		END IF

		{ Part5 }
	        CALL GET_COL(gr_sysindexes.tabid,
			     gr_sysindexes.part6) RETURNING STATUS
		IF STATUS = 0 THEN
		       CALL Col_Type(gr_syscolumns.collength,
			      gr_syscolumns.coltype)
			RETURNING v.coltype,v.collength,v.colscale,v.allownull
			LET v.sequence = 06
		        LET v.colname  = gr_syscolumns.colname
		        OUTPUT TO REPORT SysTabRep(v.*)
		END IF

		{ Part6 }
	        CALL GET_COL(gr_sysindexes.tabid,
			     gr_sysindexes.part6) RETURNING STATUS
		IF STATUS = 0 THEN
		       CALL Col_Type(gr_syscolumns.collength,
			      gr_syscolumns.coltype)
			RETURNING v.coltype,v.collength,v.colscale,v.allownull
			LET v.sequence = 06
		        LET v.colname  = gr_syscolumns.colname
		        OUTPUT TO REPORT SysTabRep(v.*)
		END IF

		{ Part7 }
	        CALL GET_COL(gr_sysindexes.tabid,
			     gr_sysindexes.part7) RETURNING STATUS
		IF STATUS = 0 THEN
		       CALL Col_Type(gr_syscolumns.collength,
			      gr_syscolumns.coltype)
			RETURNING v.coltype,v.collength,v.colscale,v.allownull
			LET v.sequence = 07
		        LET v.colname  = gr_syscolumns.colname
		        OUTPUT TO REPORT SysTabRep(v.*)
		END IF

		{ Part8 }
	        CALL GET_COL(gr_sysindexes.tabid,
			     gr_sysindexes.part8) RETURNING STATUS
		IF STATUS = 0 THEN
		       CALL Col_Type(gr_syscolumns.collength,
			      gr_syscolumns.coltype)
			RETURNING v.coltype,v.collength,v.colscale,v.allownull
			LET v.sequence = 08
		        LET v.colname  = gr_syscolumns.colname
		        OUTPUT TO REPORT SysTabRep(v.*)
		END IF

  END FOREACH

FINISH REPORT SysTabRep

{INDEXES}

END MAIN

REPORT SysTabRep(V)

  DEFINE 
	V RECORD
		tabid	      INTEGER,
		tabname       CHAR(18),
		tabtype       CHAR(15), 
           	sequence      SMALLINT,
	 	colname	      CHAR(18),
		colType	      CHAR(10),
	        collength     INTEGER,
		colScale      INTEGER,
	        AllowNull     CHAR(01)
	END RECORD 
FORMAT 

BEFORE GROUP OF v.tabname

        SKIP  TO TOP OF PAGE
	PRINT COLUMN 01,g_nomemp CLIPPED,
	      COLUMN 55,'DICCTIONARY STRUCTURE' 
	PRINT COLUMN 01,'<Put your company name here>'

	PRINT COLUMN 01,'---------------------------------------',
			'---------------------------------------'
	SKIP 01 LINE
	PRINT COLUMN 01,'Entity: ',V.tabname,' Type : ',V.tabtype
	PRINT COLUMN 01,'Description:'
	PRINT COLUMN 03,'Sequence',
	      COLUMN 15,'ColName',
	      COLUMN 30,'ColType',
	      COLUMN 39,'Length',
	      COLUMN 47,'Scale',
	      COLUMN 55,'Allow Null'

ON EVERY ROW
	PRINT COLUMN 05,v.sequence USING '##&',
	      COLUMN 11,v.colname,
	      COLUMN 31,v.coltype,
	      COLUMN 40,v.collength USING '####&',
	      COLUMN 48,v.colscale USING '##&',
	      COLUMN 59,v.allownull

END REPORT

FUNCTION NomTabla(id)
   DEFINE id  INTEGER,
	  nom CHAR(20)

   SELECT TABNAME INTO nom
   FROM SYSTABLES WHERE TABID = id

   RETURN nom CLIPPED
END FUNCTION

FUNCTION IdTabla(nom)
   DEFINE nom CHAR(20),
          id  INTEGER

   LET nom = nom CLIPPED

   SELECT TABID INTO id 
   FROM SYSTABLES WHERE TABNAME = nom

   RETURN id
END FUNCTION

{ Return ColType, IsNull,Length, Scale for Decimals or Money Values }
FUNCTION Col_Type(pcollen,Arg)
   DEFINE pcollen,               {Coll Length Sent via parameter}
          Arg        Integer ,
	  TypeReturn Char(10),   {ColType}
          Allow_Null Char(01),   {IsNull }
	  LenReturn  Integer,    {Length }
	  ScaReturn  Integer     {Scale  }
	
  { Calculate IS NULL }
  IF Arg > 256 Then
		LET Allow_Null = 'Y'
		LET Arg = Arg - 256
  ELSE
		LET Allow_Null = 'N'
  END IF

  { Calculate ColType }
  CASE Arg
	WHEN 0 LET TypeReturn = 'CHAR      '
	WHEN 1 LET TypeReturn = 'SMALLINT  '
	WHEN 2 LET TypeReturn = 'INTEGER   '
	WHEN 3 LET TypeReturn = 'FLOAT     '
	WHEN 4 LET TypeReturn = 'SMALLFLOAT'
	WHEN 5 LET TypeReturn = 'DECIMAL   '
	WHEN 6 LET TypeReturn = 'SERIAL    '
	WHEN 7 LET TypeReturn = 'DATE      '
	WHEN 8 LET TypeReturn = 'MONEY     '
	WHEN 9 LET TypeReturn = 'DATETIME  '
       WHEN 10 LET TypeReturn = 'INTERVAL  '
    OTHERWISE  LET TypeReturn = 'UNDEFINED '
  END CASE
 { Calculate LENGTH }

  IF Arg = 5 Then 
	CALL LenScale(pcollen) RETURNING LenReturn,ScaReturn  
  ELSE
	LET LenReturn = pcollen 
	LET ScaReturn = 0
  END IF 

 { Return Values }

 RETURN TypeReturn,LenREturn,ScaReturn,Allow_Null
END FUNCTION 

FUNCTION LenScale(dividendo)

   DEFINE 

      Dividendo,
      Len,
      Scale        INTEGER
  
   LET Scale    = dividendo MOD 256 
   LET Len      = (dividendo - Scale)/256

   RETURN Len,Scale 

END FUNCTION

FUNCTION TABTYPE(Arg)
   DEFINE Arg CHAR(01),
	  ArgReturn CHAR(15)

   CASE Arg
	WHEN 'T' LET ArgReturn = 'TABLE'
	WHEN 'V' LET ArgReturn = 'VIEW '
	WHEN 'S' LET ArgReturn = 'SYNONIME'
	WHEN 'P' LET ArgReturn = 'PRIVATE SYN.'
	WHEN 'U' LET ArgReturn = 'UNIQUE INDEX'
	WHEN 'D' LET ArgReturn = 'DUPLICATE INDEX'
   END CASE
   RETURN ArgReturn
END FUNCTION

FUNCTION GET_COL(ltabid,lcolno)
	DEFINE ltabid  integer, 
	       lcolno  smallint

   SELECT * INTO gr_syscolumns.*
   FROM SYSCOLUMNS
   WHERE TABID = ltabid and
	 colno = lcolno

  RETURN STATUS 
END FUNCTION 

--------------47046CAB3449--