Re: DATETIME columns
Sally Woolrich (Sally@excelsis.demon.co.uk) wrote:
: I would like to know how to interpret the 'syscolumns' entry for
: DATETIME columns so as to be able to build the string defining their
: type e.g. 'my_column DATETIME(YEAR TO SECOND) NOT NULL'.
: If anyone can help it would be most appreciated - I can't find it in
: the manuals we have.
Below is some code I wrote long ago to create my own schema. Although
there is a formula for figuring out DATETIME based on the collength
number, I think it involved hex values or something else that I didn't
have direct access to in 4GL. So I did it the long way; created every
variation of DATETIME in a table and viewed what the collength for it
was. The last function in the code below has those results.
=======================================================================
Dennis J. Pimple dennisp@informix.com Opinions expressed
Principal Consultant -------------------- are mine, and do not
Informix Software Inc Voice: 303-850-0210 necessarily reflect
Denver Colorado USA Fax: 303-779-4025 those of my employer.
## sch.4gl - run against a Unix 5.01 Database to get a schema listing
## tables, columns, and indexes in a format maybe a little nicer than
## dbschema.
DEFINE r_systab RECORD
tabname CHAR(18),
owner CHAR(8),
dirpath CHAR(64), # site || dbname for OnLine
tabid INTEGER,
rowsize SMALLINT,
ncols SMALLINT,
nindexes SMALLINT,
nrows INTEGER,
created DATE,
version INTEGER,
tabtype CHAR(1)
END RECORD
DEFINE r_syscol RECORD
colname CHAR(18),
colno SMALLINT,
coltype SMALLINT,
collength SMALLINT
END RECORD
DEFINE r_sysind RECORD
idxname CHAR(18),
idxtype CHAR(1),
clustered CHAR(1)
END RECORD
DEFINE r_db CHAR(60)
DEFINE r_file CHAR(60)
DEFINE online SMALLINT # TRUE if running online
MAIN
DEFINE ans CHAR(1)
DEFINE sqltxt CHAR(1000)
DEFINE sqlexec CHAR(80)
CLEAR SCREEN
INITIALIZE r_db TO NULL
WHILE r_db IS NULL
PROMPT "Enter Database (full path or in DBPATH): " FOR r_db
END WHILE
DATABASE r_db
INITIALIZE ans TO NULL
WHILE ans IS NULL
PROMPT "Update Statistics (Y/N)? " FOR CHAR ans
END WHILE
IF ans MATCHES "[Yy]" THEN
MESSAGE "Updating ..."
UPDATE STATISTICS
MESSAGE ""
END IF
INITIALIZE r_file TO NULL
WHILE r_file IS NULL
PROMPT "Enter file name to report schemas to: " FOR r_file
IF int_flag THEN
LET int_flag=FALSE
EXIT PROGRAM
END IF
END WHILE
# see if we're running online
LET sqlexec = fgl_getenv("SQLEXEC")
IF sqlexec IS NULL OR sqlexec MATCHES "*sqlturbo" THEN
LET online = TRUE
LET sqltxt = "SELECT systables.tabname, systables.owner, ",
"systables.site||systables.dbname, systables.tabid, ",
"systables.rowsize, systables.ncols, ",
"systables.nindexes, systables.nrows, ",
"systables.created, systables.version, ",
"systables.tabtype,"
ELSE
LET online = FALSE
LET sqltxt = "SELECT systables.tabname, systables.owner, ",
"systables.dirpath, systables.tabid, ",
"systables.rowsize, systables.ncols, ",
"systables.nindexes, systables.nrows, ",
"systables.created, systables.version, ",
"systables.tabtype,"
END IF
LET sqltxt = sqltxt CLIPPED, " syscolumns.colname, ",
"syscolumns.colno, syscolumns.coltype, ",
"syscolumns.collength ",
"FROM systables, syscolumns ",
"WHERE systables.tabid > 99 ",
"AND syscolumns.tabid = systables.tabid ",
"ORDER BY systables.tabname, syscolumns.colno"
PREPARE s_systab FROM sqltxt
DECLARE c_systab CURSOR FOR s_systab
START REPORT rpt_sch TO r_file
FOREACH c_systab INTO r_systab.*, r_syscol.*
MESSAGE "Outputting Table ", r_systab.tabname CLIPPED,
" Column ", r_syscol.colname CLIPPED, " "
OUTPUT TO REPORT rpt_sch()
END FOREACH
ERROR ""
MESSAGE "File ", r_file CLIPPED, " holds schemas"
FINISH REPORT rpt_sch
END MAIN
REPORT rpt_sch()
DEFINE type_text CHAR(41)
DEFINE sel_text CHAR(200)
DEFINE idx_text CHAR(18)
DEFINE ict, jct, kct, tct, rct SMALLINT
OUTPUT
LEFT MARGIN 0
FORMAT
FIRST PAGE HEADER
LET rct=0
PRINT "DATABASE ", r_db CLIPPED, COLUMN 72, TODAY USING "mm/dd/yy"
SKIP 3 LINES
PAGE HEADER
PRINT COLUMN 74, "Page ", PAGENO USING "<<<<<"
IF r_syscol.colno>1 AND r_systab.ncols>r_syscol.colno THEN
IF r_systab.tabtype = "T" THEN
PRINT "TABLE ";
ELSE
IF r_systab.tabtype = "V" THEN
PRINT "VIEW ";
ELSE
PRINT "SYNONYM ";
END IF
END IF
PRINT r_systab.tabname CLIPPED, " (cont)"
PRINT "COLUMN ",
"TYPE INDEX"
PRINT "------------------ ",
"----------------------------------------- ------------------"
ELSE
SKIP 3 LINES
END IF
ON EVERY ROW
IF r_syscol.colno=1 THEN
LET rct=rct+1
IF r_systab.ncols<51 THEN
NEED r_systab.ncols+5 LINES
ELSE
SKIP TO TOP OF PAGE
END IF
SKIP 2 LINES
IF r_systab.tabtype = "T" THEN
PRINT "TABLE ";
ELSE
IF r_systab.tabtype = "V" THEN
PRINT "VIEW ";
ELSE
PRINT "SYNONYM ";
END IF
END IF
PRINT r_systab.tabname CLIPPED, " ",
r_systab.nrows USING "<<<<<&", " rows ",
r_systab.dirpath CLIPPED
PRINT "COLUMN ",
"TYPE INDEX"
PRINT "------------------ ",
"----------------------------------------- ------------------"
END IF
CALL get_type() RETURNING type_text
PRINT r_syscol.colname, " ", type_text;
# see if any indexes exist for this column
IF r_systab.nindexes > 0 THEN
IF online THEN
SELECT count(*) INTO ict FROM sysindexes
WHERE sysindexes.tabid=r_systab.tabid
AND (sysindexes.part1=r_syscol.colno
OR sysindexes.part2=r_syscol.colno
OR sysindexes.part3=r_syscol.colno
OR sysindexes.par