Informix

Informix

Connect with Db2, Informix, Netezza, open source, and other data experts to gain value from your data, share insights, and solve problems.

 View Only

  • 1.  is there a rowsize function in SQL similar to rowid?

    Posted 20 days ago

    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
    ------------------------------


  • 2.  RE: is there a rowsize function in SQL similar to rowid?

    Posted 19 days ago

    You suspect correctly.  AFAIK, there is no ROWSIZE function.



    ------------------------------
    Jonathan Leffler
    ------------------------------

    Original Message


  • 3.  RE: is there a rowsize function in SQL similar to rowid?

    Posted 19 days ago

    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 integer
          external 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 integer
           external 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
    ------------------------------

    Original Message


  • 4.  RE: is there a rowsize function in SQL similar to rowid?

    Posted 19 days ago

    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
    ------------------------------

    Original Message


  • 5.  RE: is there a rowsize function in SQL similar to rowid?

    Posted 19 days ago

    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
    ------------------------------

    Original Message


Related Content