Anyone have a function to compute the size of a column?

> > Hi, > > I am working on an application which estimates the size a > table will take. It looks at the data and index space for > a table and therefore needs individual column sizes. This > is easy for columns which are integers, character strings, > etc since you can just query collength from syscolumns. > However, it's not so easy for columns which are datetime > since collength does not store the size dierctly. I have > located the information needed to do this in the SQL guide > but am looking to see if someone has already done this since > it would be a tedious function to write. > > Thanks in advance. > Bill > -- > --- |^^^^^^| --------------------- From the desk of Bill Ennis ---------- > | (o)(o) / \\ E-mail: ennis@eis.comm.mot.com > @ _) __/ Dont have \\ > | ,___| /___ a cow. Dude!| > I'm stealing this from dbdiff2. I'm not going to test it. If it works would you kindly let me know and I'll make this a library or something and stick it in the archives. While I'm at it allow me to say that dbdiff2 has a lot of this sort of code in it. It's primary function is to read system catalogues and turn that code into SQL. Whenever you have a need for an example on how to do this sort of thing feel free to plageurize. It is of course located at the archives. As soon as I get a chance (soon) to work out the final bugs with the current release I'll even have a new copy out there. Without further ado: ------- GLOBALS DEFINE datatype ARRAY[40] OF CHAR(20), # for coltype conversions datetype ARRAY[16] OF CHAR(11) # for coltype conversions END GLOBALS MAIN CALL hskpng() END MAIN ########################################################################### # Convert coltype/length into an SQL descriptor string ########################################################################### FUNCTION col_cnvrt(coltype, collength) DEFINE coltype, collength, NONULL SMALLINT, SQL_strg CHAR(40), tmp_strg CHAR(4) LET coltype = coltype + 1 # datatype[] is offset by one LET NONULL = coltype/256 # if > 256 then is NO NULLS LET coltype = coltype MOD 256 # lose the NO NULLS determinator LET SQL_strg = datatype[coltype] CASE coltype WHEN 1 # char LET tmp_strg = collength using "<<<<" LET SQL_strg = SQL_strg clipped, " (", tmp_strg clipped, ")" # SQL syntax supports float(n) - Informix ignores this # WHEN 4 # float # LET SQL_strg = SQL_strg clipped, " (", ")" WHEN 6 # decimal LET SQL_strg = SQL_strg clipped, " (", fix_nm(collength,0) clipped, ")" # Syntax supports serial(starting_no) - starting_no is unavaliable # WHEN 7 # serial # LET SQL_strg = SQL_strg clipped, " (", ")" WHEN 9 # money LET SQL_strg = SQL_strg clipped, " (", fix_nm(collength,0) clipped, ")" WHEN 11 # datetime LET SQL_strg = SQL_strg clipped, " ", fix_dt(collength) clipped WHEN 14 # varchar LET SQL_strg = SQL_strg clipped, " (", fix_nm(collength,1) clipped, ")" WHEN 15 # interval LET SQL_strg = SQL_strg clipped, " ", fix_dt(collength) clipped END CASE IF NONULL THEN LET SQL_strg = SQL_strg clipped, " NOT NULL" END IF RETURN SQL_strg END FUNCTION ########################################################################### # Turn collength into two numbers - return as string ########################################################################### FUNCTION fix_nm(num,tp) DEFINE num integer, tp smallint, strg CHAR(8), i, j SMALLINT, strg1, strg2 char(3) LET i = num / 256 LET j = num MOD 256 LET strg1 = i using "<<&" LET strg2 = j using "<<&" IF tp = 0 THEN IF j > i THEN LET strg = strg1 clipped ELSE LET strg = strg1 clipped, ", ", strg2 clipped END IF ELSE # varchar is just the opposite IF i = 0 THEN LET strg = strg2 clipped ELSE LET strg = strg2 clipped, ", ", strg1 clipped END IF END IF RETURN strg END FUNCTION ########################################################################### # Turn collength into meaningful date info - return as string ########################################################################### FUNCTION fix_dt(num) DEFINE num integer, i, j SMALLINT, strg CHAR(20) LET i = (num mod 16) + 1 # offset again LET j = ((num mod 256) / 16) + 1 # offset again LET strg = datetype[j] clipped, " TO ", datetype[i] clipped RETURN strg END FUNCTION ############ Set up datatype arrays FUNCTION hskpng() DEFINE i SMALLINT, lne CHAR(129), retcode INTEGER LET datatype[1] = "CHAR" LET datatype[2] = "SMALLINT" LET datatype[3] = "INTEGER" LET datatype[4] = "FLOAT" LET datatype[5] = "SMALLFLOAT" LET datatype[6] = "DECIMAL" LET datatype[7] = "SERIAL" LET datatype[8] = "DATE" LET datatype[9] = "MONEY" LET datatype[10] = "UNKNOWN" LET datatype[11] = "DATETIME" LET datatype[12] = "BYTE" LET datatype[13] = "TEXT" LET datatype[14] = "VARCHAR" LET datatype[15] = "INTERVAL" LET datatype[16] = "UNKNOWN" # little room for growth LET datatype[17] = "UNKNOWN" LET datatype[18] = "UNKNOWN" LET datatype[19] = "UNKNOWN" LET datatype[20] = "UNKNOWN" LET datetype[1] = "YEAR" LET datetype[3] = "MONTH" LET datetype[5] = "DAY" LET datetype[7] = "HOUR" LET datetype[9] = "MINUTE" LET datetype[11] = "SECOND" LET datetype[12] = "FRACTION(1)" LET datetype[13] = "FRACTION(2)" LET datetype[14] = "FRACTION(3)" LET datetype[15] = "FRACTION(4)" LET datetype[16] = "FRACTION(5)" END FUNCTION -- cheers j. _____________________________________________________________________________ Jack Parker - Hewlett Packard, BSMC Boise, Idaho, USA jparker@hpbs3645.boi.hp.com _____________________________________________________________________________ "A thing is bigger for being shared" - Gaelic _____________________________________________________________________________ Any op