IDS Forum

Re: Weird request

Posted By: Jonathan Leffler
Date: Saturday, 19 July 2008, at 7:22 p.m.

In Response To: Re: Weird request (MIKE MAGIE)

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.

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.