IDS Forum

Re: Weird request

Posted By: Art Kagel
Date: Sunday, 20 July 2008, at 10:14 a.m.

In Response To: Re: Weird request (Jonathan Leffler)

I don't see the new BIGINT or BIGSERIAL types in there either.

Art

On Sat, Jul 19, 2008 at 7:22 PM, Jonathan Leffler <jleffler.iiug@gmail.com>
wrote:

> Someone asked "what's this weird request about?", and the answer is:-
>
> How do I convert the coltype and collength values from syscolumns
> into the name of the column. Mike Magie provided a solution as a CASE
> statement that could be embedded into the body of an SQL statement
> that had an alias b referring to a table that contained coltype and
> collength columns of the appropriate types (INTEGER or SMALLINT).
>
> On Fri, Jul 18, 2008 at 6:29 AM, MIKE MAGIE <jmmagie@yahoo.com> wrote:
> > Small change to the decimal stuff...
> >
> > when (b.coltype = 5) and MOD(b.collength,256) = 255 then ('decimal(' ||
> > ROUND(b.collength/256) - 1 || ')')
> > when b.coltype = 5 then ('decimal(' || ROUND(b.collength/256) || "," ||
> > MOD(b.collength,256) || ')')
> > when (b.coltype = 261) and MOD(b.collength,256) = 255 then ('decimal(' ||
> > ROUND(b.collength/256) - 1 || ') not null')
> > when b.coltype = 261 then ('decimal(' || ROUND(b.collength/256) || "," ||
> > MOD(b.collength,256) || ') not null')
>
> Here's a stored procedure (ok, a pair of stored procedures) based on
> Mike's CASE statement, but with some more rigour in the formatting of
> MONEY, INTERVAL, DATETIME, VARCHAR and NVARCHAR. Note that because
> this is a procedure instead of a CASE statement, it can process the
> null-ness separately - reducing the complexity by about a half (and,
> strictly, correcting the algorithm for types such as INT8 NOT NULL).
> It also uses a procedure to map the parts of a DATETIME or INTERVAL
> encoding into strings. The rest of the encoding for them is mostly a
> matter of knowing from the datetime.h header how the type information
> is encoded.
>
> I ran it on my stores database, which includes a table whose name is
> 128 double quotes, and another table which contains 32511 separate
> CHAR(1) columns, and the complete set of possible DECIMAL, MONEY,
> DATETIME and INTERVAL types (test case for DBD::Informix), and it
> works on all of these. About the only stuff it doesn't handle is
> user-defined types - such as distinct types, or UDTs in datablades.
> Someone else can do that extension work. It does seem to cover the
> base types OK.
>
> Sorry about line breaks...I'll upload it to the IIUG Software Archive too.
>
> -- @(#)$Id: informixtypes.spl,v 1.3 2008/07/19 23:14:22 jleffler Exp $
> -- @(#)Create InformixTypeName Stored Procedure
> --
> -- Basic coding provided by Mike Magie (jmmagie@yahoo.com) in a posting
> -- to ids@iius.org mailing list, one of them on 2008-07-18.
> -- Converted to two stored procedures by J Leffler.
>
> CREATE PROCEDURE DateTimeFieldName(field SMALLINT, lead SMALLINT DEFAULT 0)
>
> RETURNING VARCHAR(11) AS FieldName;
>
> DEFINE retval VARCHAR(11);
>
> IF field = 0 THEN LET retval = 'YEAR';
>
> ELIF field = 2 THEN LET retval = 'MONTH';
>
> ELIF field = 4 THEN LET retval = 'DAY';
>
> ELIF field = 6 THEN LET retval = 'HOUR';
>
> ELIF field = 8 THEN LET retval = 'MINUTE';
>
> ELIF field = 10 THEN LET retval = 'SECOND';
>
> ELIF field = 11 THEN LET retval = 'FRACTION(1)';
>
> ELIF field = 12 THEN
>
> IF lead == 0 THEN
>
> LET retval = 'FRACTION(2)';
>
> ELSE
>
> LET retval = 'FRACTION';
>
> END IF;
>
> ELIF field = 13 THEN LET retval = 'FRACTION(3)';
>
> ELIF field = 14 THEN LET retval = 'FRACTION(4)';
>
> ELIF field = 15 THEN LET retval = 'FRACTION(5)';
>
> ELSE LET retval = 'BOGUS';
>
> END IF;
>
> RETURN retval;
> END PROCEDURE;
>
> CREATE PROCEDURE InformixTypeName(coltype INT, collength INT)
>
> RETURNING VARCHAR(64) AS typname;
>
> DEFINE retval VARCHAR(64);
>
> DEFINE suffix VARCHAR(9);
>
> IF coltype >= 256 AND coltype < 512 THEN
>
> LET suffix = " NOT NULL";
>
> LET coltype = coltype - 256;
>
> ELSE
>
> LET suffix = "";
>
> END IF;
>
> IF coltype = 0 THEN LET retval = 'CHAR(' || collength || ')';
>
> ELIF coltype = 1 THEN LET retval = 'SMALLINT';
>
> ELIF coltype = 2 THEN LET retval = 'INTEGER';
>
> ELIF coltype = 3 THEN LET retval = 'FLOAT';
>
> ELIF coltype = 4 THEN LET retval = 'SMALLFLOAT';
>
> ELIF coltype = 5 AND MOD(collength,256) = 255 THEN
>
> LET retval = 'DECIMAL(' || TRUNC(collength/256) || ')';
>
> ELIF coltype = 5 THEN
>
> LET retval = 'DECIMAL(' || ROUND(collength/256) || "," ||
> MOD(collength,256) || ')';
>
> ELIF coltype = 6 THEN LET retval = 'SERIAL';
>
> ELIF coltype = 7 THEN LET retval = 'DATE';
>
> ELIF coltype = 8 THEN
>
> -- MONEY is always fixed point
>
> LET retval = 'MONEY(' || ROUND(collength/256) || "," ||
> MOD(collength,256) || ')';
>
> ELIF coltype = 9 THEN LET retval = 'NULL';
>
> ELIF coltype = 10 THEN
>
> BEGIN
>
> DEFINE lead SMALLINT;
>
> DEFINE tail SMALLINT;
>
> LET tail = MOD(collength, 16);
>
> LET lead = MOD(TRUNC(collength/16), 16);
>
> LET retval = 'DATETIME ' || DateTimeFieldName(lead,1) || ' to
> ' || DateTimeFieldName(tail);
>
> END;
>
> ELIF coltype = 11 THEN LET retval = 'BYTE';
>
> ELIF coltype = 12 THEN LET retval = 'TEXT';
>
> ELIF coltype = 13 THEN
>
> BEGIN
>
> DEFINE INFO VARCHAR(9);
>
> IF TRUNC(collength/256) = 0 THEN
>
> LET INFO = MOD(collength, 256);
>
> ELSE
>
> LET INFO = MOD(collength, 256) || ',' || TRUNC(collength/256);
>
> END IF;
>
> LET retval = 'VARCHAR(' || INFO || ')';
>
> END;
>
> ELIF coltype = 14 THEN
>
> BEGIN
>
> DEFINE lead SMALLINT;
>
> DEFINE tail SMALLINT;
>
> DEFINE tlen SMALLINT;
>
> DEFINE rlen SMALLINT;
>
> DEFINE ldig VARCHAR(4);
>
> LET tail = MOD(collength, 16);
>
> LET lead = MOD(TRUNC(collength/16), 16);
>
> LET tlen = TRUNC(collength/256);
>
> LET rlen = tlen - (tail - lead);
>
> IF (lead == 0 AND rlen == 4) OR
>
> (lead != 0 AND rlen == 2) THEN
>
> LET ldig = '';
>
> ELSE
>
> LET ldig = '(' || rlen || ')';
>
> END IF;
>
> LET retval = 'INTERVAL ' || DateTimeFieldName(lead) || ldig ||
> ' to ' || DateTimeFieldName(tail);
>
> END;
>
> ELIF coltype = 15 THEN LET retval = 'NCHAR(' || TRIM(collength) || ')';
>
> ELIF coltype = 16 THEN
>
> BEGIN
>
> DEFINE INFO VARCHAR(9);
>
> IF TRUNC(collength/256) = 0 THEN
>
> LET INFO = MOD(collength, 256);
>
> ELSE
>
> LET INFO = MOD(collength, 256) || ',' || TRUNC(collength/256);
>
> END IF;
>
> LET retval = 'NVARCHAR(' || INFO || ')';
>
> END;
>
> ELIF coltype = 17 THEN LET retval = 'INT8';
>
> ELIF coltype = 18 THEN LET retval = 'SERIAL8';
>
> ELIF coltype = 19 THEN LET retval = 'SET';
>
> ELIF coltype = 20 THEN LET retval = 'MULTISET';
>
> ELIF coltype = 21 THEN LET retval = 'LIST';
>
> ELIF coltype = 22 THEN LET retval = 'ROW';
>
> ELIF coltype = 23 THEN LET retval = 'COLLECTION';
>
> ELIF coltype = 24 THEN LET retval = 'ROWREF';
>
> ELIF coltype = 40 AND collength = 2048 THEN LET retval = 'LVARCHAR';
>
> ELIF coltype = 40 THEN LET retval = 'LVARCHAR(' || collength || ')';
>
> ELSE
>
> LET retval = "Unknown type (" || coltype || ", " || collength || ")";
>
> END IF;
>
> LET retval = retval || suffix;
>
> RETURN retval;
>
> END PROCEDURE;
>
> SELECT T.tabname, T.tabid, C.colno, C.colname, C.coltype, C.collength,
>
> informixtypename(C.coltype, C.collength) AS coltypename
>
> FROM SysTables T, SysColumns C
>
> WHERE C.tabid = T.tabid
>
> ORDER BY T.tabname, C.colno;
>
> --
> Jonathan Leffler #include <disclaimer.h>
> Email: jleffler@earthlink.net, jleffler@us.ibm.com
> Guardian of DBD::Informix v2008.0513 -- http://dbi.perl.org/
> "Blessed are we who can laugh at ourselves, for we shall never cease
> to be amused."
> NB: Please do not use this email for correspondence.
> I don't necessarily read it every week, even.
>
>
>
> *******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>

--
Art S. Kagel
Oninit (www.oninit.com)
IIUG Board of Directors (art@iiug.org)

Disclaimer: Please keep in mind that my own opinions are my own opinions and
do not reflect on my employer, Oninit, the IIUG, nor any other organization
with which I am associated either explicitly or implicitly. Neither do those
opinions reflect those of other individuals affiliated with any entity with
which I am affiliated nor those of the entities themselves.

Messages In This Thread

• Weird request
  MIKE MAGIE -- Thursday, 10 July 2008, at 2:35 p.m.
  • RE: Weird request
    Clifton Bean -- Thursday, 10 July 2008, at 3:13 p.m.
    • Re: Weird request
      Obnoxio The Clown -- Thursday, 10 July 2008, at 3:40 p.m.
      • Re: Weird request
        MIKE MAGIE -- Thursday, 10 July 2008, at 3:48 p.m.
        • RE: Weird request
          Paul Watson -- Thursday, 10 July 2008, at 3:50 p.m.
          • Re: Weird request
            Jonathan Smaby -- Thursday, 10 July 2008, at 3:58 p.m.
            • Re: Weird request
              Obnoxio The Clown -- Thursday, 10 July 2008, at 4:01 p.m.
            • RE: Weird request
              Paul Watson -- Thursday, 10 July 2008, at 4:02 p.m.
              • Re: Weird request
                Jonathan Smaby -- Friday, 11 July 2008, at 12:17 p.m.
        • Re: Weird request
          Obnoxio The Clown -- Thursday, 10 July 2008, at 3:57 p.m.
  • Re: Weird request
    Art Kagel -- Thursday, 10 July 2008, at 3:34 p.m.
  • Re: Weird request
    MIKE MAGIE -- Thursday, 10 July 2008, at 3:43 p.m.
  • Re: Weird request
    Richard Snoke -- Thursday, 10 July 2008, at 4:19 p.m.
    • Re: Weird request
      Fernando Nunes -- Thursday, 10 July 2008, at 5:35 p.m.
      • Re: Weird request
        Richard Snoke -- Friday, 11 July 2008, at 8:01 a.m.
        • Re: Weird request
          MIKE MAGIE -- Friday, 11 July 2008, at 11:02 a.m.
    • Re: Weird request
      MIKE MAGIE -- Friday, 11 July 2008, at 8:46 a.m.
    • Re: Weird request
      MIKE MAGIE -- Thursday, 17 July 2008, at 10:49 p.m.
      • Re: Weird request
        MIKE MAGIE -- Friday, 18 July 2008, at 9:29 a.m.
        • Re: Weird request
          Jim Tranny -- Friday, 18 July 2008, at 10:22 a.m.
        • Re: Weird request
          Jonathan Leffler -- Saturday, 19 July 2008, at 7:22 p.m.
          • Re: Weird request
            MIKE MAGIE -- Sunday, 20 July 2008, at 1:10 a.m.
          • Re: Weird request
            Art Kagel -- Sunday, 20 July 2008, at 10:14 a.m.
            • Re: Weird request
              Jonathan Leffler -- Sunday, 20 July 2008, at 4:19 p.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.