is there a rowsize function in SQL similar to rowid?

This should be a quickie: Yes or no. And I'd classify it as low priority. We can always select rowid for an individual row. I was curious, though I could not easily find it with google: Can I do this in SQL? select rowsize, rowid, <some real columns > from <table> In a table with lots of varchar column I can see a use for such a function. Maybe by another name? Please don't refer me to systables.rowsize; that gives only the full size of a row if all varchars are to their max length. No, I mean for an individual row, ad I am fetching them. I suspect the answer is no but I am curious. Thanks. ------------------------------ Jacob Salomon ------------------------------

You suspect correctly. AFAIK, there is no ROWSIZE function. ------------------------------ Jonathan Leffler ------------------------------

Hello Jacob, Just by curiosity, why would you need to know the size of individual rows? Most SQL APIs (ESQL/C, ODBC, JDBC) provide a "describe columns" functionality, that can be used to get column names, types and consequently data sizes. The typical use case is to allocate buffers to fetch the data, especially when you write some generic code. Pay attention to CLOB/BLOB/TEXT/BYTE columns, where the size of the actual data is undefined in the DB schema. Seb ------------------------------ Sebastien FLAESCH ------------------------------

What's worth noting here is that, from SQL's perspective, i.e. your DML or queries being processed, a row is always 'rowsize' long. Only on storage level, that is in data (and remainder) pages, it might be shorter, but SQL doesn't really have visibility into this. This also is why it does make a difference, in memory consumption but possibly also things like sorting, whether you're storing e.g. your <= 30 character values in a varchar(30) or varchar(255) field, and even more so when considering lvarchar. ------------------------------ Andreas Legner Informix Dev HCL Software ------------------------------

Jacob: You are correct, there is no such function. However, it would not be hard to write one that took in a table name and a rowid, built a dynamic query from the table's syscolumns records, and added up length( column ) returning the total. This could be written in SPL, C, or Java. The only rub would be BLOB and CLOB type columns for which there is not length() function defined. You could fix that though rather easily. The EXCOMPAT datablade package contains the function dbms_lob_getlength(). You could either create a length() function that called that function or, what I have done, is to create a length function that uses the underlying library function. Here: execute function sysbldprepare( 'excompat.*', 'create' ); CREATE FUNCTION length ( blob ) returns integerexternal name "$INFORMIXDIR/extend/excompat.1.0/excompat.bld(dbms_lob_getlength)" language c; alter function length( blob ) with (Modify External Name = "$INFORMIXDIR/extend/excompat.1.0/excompat.bld(dbms_lob_getlength)"); REVOKE EXECUTE ON FUNCTION "art".length( blob ) FROM "public" AS "art"; GRANT EXECUTE ON FUNCTION "art".length( blob ) TO "public" AS "art"; CREATE FUNCTION length ( clob ) returns integerexternal name "$INFORMIXDIR/extend/excompat.1.0/excompat.bld(dbms_lob_getlength)" language c; alter function length( clob ) with (Modify External Name = "$INFORMIXDIR/extend/excompat.1.0/excompat.bld(dbms_lob_getlength)"); REVOKE EXECUTE ON FUNCTION "art".length( clob ) FROM "public" AS "art"; GRANT EXECUTE ON FUNCTION "art".length( clob ) TO "public" AS "art";------------------------------ Art S. Kagel, President and Principal Consultant ASK Database Management Corp. www.askdbmgt.com ------------------------------