Subject: (makedic.4gl) Columns info for tables! From: lesco@guate.net (Mario Estrada) Newsgroups: comp.databases.informix Date: 10 Oct 1996 14:33:27 -0400 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: DICCTIONARY STRUCTURE ------------------------------------------------------------------------ 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 | lesco@guate.net | Phone (502) 3318116 | Fax (502) 3348447 | | SISTECO, S.A. | sisteco@guate.net | 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 : # # 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 # # Or compile it with: fglpc makedic.4gl and then run it as follows: # # fglgo makedic # 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,'' 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--