Re: Finding row size of a query

it would/will be hard/impossible to calc the exact size, however a max size can be calculated providing you do not have blob/byte/clob/text cols...???; you could use an spl to calc the size of a column and add up all the sizes for each column in your query: dono if the spl below is what you are after... CREATE PROCEDURE "informix".calc_idx_size(collength SMALLINT, coltype INT) -- this procedure gets the encoded column length and type and converts -- it to the real length for storage on disk -- the columns with datatype decimal money datetime interval varchar -- and nvarchar are the ones that have to be converted because informix -- stores these values encoded in the system catalog. -- other datatypes have their length in the system catalog -- the length in bytes is returned in an integer. RETURNING INT; DEFINE ret_idx_size INT; -- when we have a decimal or money column in an index calculate -- it's size if the column does not allow nulls the type is the -- original type plus 256 IF (coltype = 8 or coltype = (8 + 256) OR coltype = 5 OR coltype = (5 + 256)) THEN -- corrected by Alan Stanford training manual is wrong --(1 + (((collength - (MOD(collength , 256))) /256 )/2)); LET ret_idx_size = (1 +(1+((collength-MOD(collength,256))/256)-MOD(collength,256))/2 +(1+(MOD(collength , 256)))/2 ); -- the formula to get the number of bytes on disk for -- decimal or money data types -- we are dealing with varchar or nvarchar calculate the size ELIF (coltype = 13 OR coltype = (13 + 256) OR coltype = 16 OR coltype = (16 + 256)) THEN IF (collength < 0) THEN LET ret_idx_size = (MOD((collength + 65536) , 256)); ELSE LET ret_idx_size = (MOD(collength , 256)); END IF -- formula to get the size for nvarchar or varchar -- now we are dealing with datetime or intervals ELIF (coltype = 10 OR coltype = (10 + 256) OR coltype = 14 OR coltype = (14 + 256)) THEN LET ret_idx_size = (1 + ROUND(((collength - (MOD(collength , 256))) /256 )/2)); -- the formula to calculate their sizes ELSE -- finally if none of the above is true we can take the length from the -- system catalog so a column without a decoded length LET ret_idx_size = collength; END IF -- return the found value RETURN ret_idx_size; END PROCEDURE; have fun with it. Superboer. way fast=http://www.clipjes.nl/clip/nederlands/n/normaal_- _oerend_hard.html dcrunch...@aim.com schreef: > Informix 7.31 > Informix-ESQL Version 9.53.UC3XJ > > I am writing a tool which will calculate the disk space required to > download the data from a table or a query, in binary form via onpload. > The data download can be a simple select * from table or complex join, > or can be a view. > > 1. How do I find out what will be the size of the row of the query. > The only way I am aware of is using ESQL/C DESCRIBE. Is there a > simpler way. > > 2. I would really like to avoid writing the tool in ESQL/C since very > few in our group, besides me, know ESQL/C. Is there a generic way > of finding the row size of a query so that I can write the tool in > perl, my preferred language. > > TIA.