Re: Weird request
A user on IDS 10.00 under Solaris wanted a pipe-delimited dump of database|table|column|datatype across many databases, and needed to decode syscolumns.coltype (where values 256+ indicate NOT NULL) into readable type names. Dick Snoke posted a CASE statement over systables/syscolumns; Fernando Nunes pointed to the ansicoltype procedure in $INFORMIXDIR/etc/xpg4_is.sql. Mike wrapped the query in a ksh loop over sysdatabases with dbaccess/unload plus awk, then extended the CASE to show lengths, DECIMAL precision/scale, DATETIME qualifiers and NOT NULL. Jonathan Leffler turned it into stored procedures (InformixTypeName/DateTimeFieldName) handling MONEY, INTERVAL, VARCHAR/NVARCHAR, LVARCHAR, later adding BIGINT/BIGSERIAL, and uploaded it to the IIUG archive. Problem solved.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hi, The following SQL may save a lot of time: select substr(a.tabname,1,20) as table , substr(b.colname,1,20) as column , case when b.coltype = 0 or b.coltype = 256 then 'char' when b.coltype = 1 or b.coltype = 256 then 'smallint' when b.coltype = 2 or b.coltype = 257 then 'integer' when b.coltype = 3 or b.coltype = 258 then 'float' when b.coltype = 4 or b.coltype = 259 then 'smallfloat' when b.coltype = 5 or b.coltype = 260 then 'decimal' when b.coltype = 6 or b.coltype = 261 then 'serial' when b.coltype = 7 or b.coltype = 262 then 'date' when b.coltype = 8 or b.coltype = 263 then 'money' when b.coltype = 9 or b.coltype = 264 then 'null' when b.coltype = 10 or b.coltype = 265 then 'datetime' when b.coltype = 11 or b.coltype = 266 then 'byte' when b.coltype = 12 or b.coltype = 267 then 'text' when b.coltype = 13 or b.coltype = 268 then 'varchar' when b.coltype = 14 or b.coltype = 269 then 'interval' when b.coltype = 15 or b.coltype = 270 then 'nchar' when b.coltype = 16 or b.coltype = 271 then 'nvchar' when b.coltype = 17 or b.coltype = 272 then 'int8' when b.coltype = 18 or b.coltype = 273 then 'serial8' when b.coltype = 19 or b.coltype = 274 then 'set' when b.coltype = 20 or b.coltype = 275 then 'multiset' when b.coltype = 21 or b.coltype = 276 then 'list' when b.coltype = 22 or b.coltype = 277 then 'row' when b.coltype = 23 or b.coltype = 278 then 'collection' when b.coltype = 24 or b.coltype = 279 then 'rowref' end as columntype , b.coltype from systables a, syscolumns b where a.tabid = b.tabid and a.tabname not like 'sys%' order by colname ; You'll have to repeat it for each database in the instance. I haven't worked out a way to do that entirely in SQL. You could add an additional CASE pieces to each current case (nesting is allowed) to decode the null v. not null and size of the numeric types. I just haven't been that ambitious. Cheers, Dick Snoke IBM Data Management - ChannelWorks dsnoke@us.ibm.com (404) 487-1595 From: "MIKE MAGIE" <jmmagie@yahoo.com> To: ids@iiug.org Date: 07/10/2008 02:36 PM Subject: Weird request [12637] Pertinents: Informix 10.00.UC8 on Solaris 2.8 Mission: Create a file that contains the following - database_name|table_name|column_name|column_data_type For like a bajillion databases with gobs of tables and oodles of columns. I have already began playing with awk and sed - and looking at sysmaster queries and stuff. Coltype is the main issue - as many of you probably know the coltype is stored as a smallint, in decimal, that when converted to hex can be mapped to the actual data type, i.e if you had a coltype value of 257 you'd convert it to hex, get 101, map it to the coltype values and figure that you had a not null integer column. But that is like hard and stuff. I am sure I am trying to reinvent the wheel here and would love to see what some of you ladies and gents might come up with. MM ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.
Check procedure ansicoltype in xpg4_is.sql located at $INFORMIXDIR/etc... Regards On Thu, Jul 10, 2008 at 9:19 PM, Richard Snoke <dsnoke@us.ibm.com> wrote: > Hi, > > The following SQL may save a lot of time: > select substr(a.tabname,1,20) as table > > , substr(b.colname,1,20) as column > > , case when b.coltype = 0 or b.coltype = 256 then 'char' > > when b.coltype = 1 or b.coltype = 256 then 'smallint' > > when b.coltype = 2 or b.coltype = 257 then 'integer' > > when b.coltype = 3 or b.coltype = 258 then 'float' > > when b.coltype = 4 or b.coltype = 259 then 'smallfloat' > > when b.coltype = 5 or b.coltype = 260 then 'decimal' > > when b.coltype = 6 or b.coltype = 261 then 'serial' > > when b.coltype = 7 or b.coltype = 262 then 'date' > > when b.coltype = 8 or b.coltype = 263 then 'money' > > when b.coltype = 9 or b.coltype = 264 then 'null' > > when b.coltype = 10 or b.coltype = 265 then 'datetime' > > when b.coltype = 11 or b.coltype = 266 then 'byte' > > when b.coltype = 12 or b.coltype = 267 then 'text' > > when b.coltype = 13 or b.coltype = 268 then 'varchar' > > when b.coltype = 14 or b.coltype = 269 then 'interval' > > when b.coltype = 15 or b.coltype = 270 then 'nchar' > > when b.coltype = 16 or b.coltype = 271 then 'nvchar' > > when b.coltype = 17 or b.coltype = 272 then 'int8' > > when b.coltype = 18 or b.coltype = 273 then 'serial8' > > when b.coltype = 19 or b.coltype = 274 then 'set' > > when b.coltype = 20 or b.coltype = 275 then 'multiset' > > when b.coltype = 21 or b.coltype = 276 then 'list' > > when b.coltype = 22 or b.coltype = 277 then 'row' > > when b.coltype = 23 or b.coltype = 278 then 'collection' > > when b.coltype = 24 or b.coltype = 279 then 'rowref' > > end as columntype > > , b.coltype > from systables a, > > syscolumns b > where a.tabid = b.tabid > > and a.tabname not like 'sys%' > order by colname > ; > > You'll have to repeat it for each database in the instance. I haven't > worked out a way to do that entirely in SQL. You could add an additional > CASE pieces to each current case (nesting is allowed) to decode the null > v. not null and size of the numeric types. I just haven't been that > ambitious. > > Cheers, > Dick Snoke > IBM Data Management - ChannelWorks > dsnoke@us.ibm.com > (404) 487-1595 > > From: > "MIKE MAGIE" <jmmagie@yahoo.com> > To: > ids@iiug.org > Date: > 07/10/2008 02:36 PM > Subject: > Weird request [12637] > > Pertinents: > > Informix 10.00.UC8 on Solaris 2.8 > > Mission: > > Create a file that contains the following - > > database_name|table_name|column_name|column_data_type > > For like a bajillion databases with gobs of tables and oodles of columns. > > I have already began playing with awk and sed - and looking at sysmaster > queries and stuff. Coltype is the main issue - as many of you probably > know > the coltype is stored as a smallint, in decimal, that when converted to > hex > can be mapped to the actual data type, i.e if you had a coltype value of > 257 > you'd convert it to hex, get 101, map it to the coltype values and figure > that > you had a not null integer column. But that is like hard and stuff. I am > sure > I am trying to reinvent the wheel here and would love to see what some of > you > ladies and gents might come up with. > > MM > > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > >
Interesting...... What value is used for CLOB or BLOB columns? Are those the same as TEXT and BYTE respectively? I can't find those data types in any of the header files or this file or anywhere else. Cheers, Dick Snoke IBM Data Management - ChannelWorks dsnoke@us.ibm.com (404) 487-1595 From: "Fernando Nunes" <domusonline@gmail.com> To: ids@iiug.org Date: 07/10/2008 05:37 PM Subject: Re: Weird request [12654] Check procedure ansicoltype in xpg4_is.sql located at $INFORMIXDIR/etc... Regards On Thu, Jul 10, 2008 at 9:19 PM, Richard Snoke <dsnoke@us.ibm.com> wrote: > Hi, > > The following SQL may save a lot of time: > select substr(a.tabname,1,20) as table > > , substr(b.colname,1,20) as column > > , case when b.coltype = 0 or b.coltype = 256 then 'char' > > when b.coltype = 1 or b.coltype = 256 then 'smallint' > > when b.coltype = 2 or b.coltype = 257 then 'integer' > > when b.coltype = 3 or b.coltype = 258 then 'float' > > when b.coltype = 4 or b.coltype = 259 then 'smallfloat' > > when b.coltype = 5 or b.coltype = 260 then 'decimal' > > when b.coltype = 6 or b.coltype = 261 then 'serial' > > when b.coltype = 7 or b.coltype = 262 then 'date' > > when b.coltype = 8 or b.coltype = 263 then 'money' > > when b.coltype = 9 or b.coltype = 264 then 'null' > > when b.coltype = 10 or b.coltype = 265 then 'datetime' > > when b.coltype = 11 or b.coltype = 266 then 'byte' > > when b.coltype = 12 or b.coltype = 267 then 'text' > > when b.coltype = 13 or b.coltype = 268 then 'varchar' > > when b.coltype = 14 or b.coltype = 269 then 'interval' > > when b.coltype = 15 or b.coltype = 270 then 'nchar' > > when b.coltype = 16 or b.coltype = 271 then 'nvchar' > > when b.coltype = 17 or b.coltype = 272 then 'int8' > > when b.coltype = 18 or b.coltype = 273 then 'serial8' > > when b.coltype = 19 or b.coltype = 274 then 'set' > > when b.coltype = 20 or b.coltype = 275 then 'multiset' > > when b.coltype = 21 or b.coltype = 276 then 'list' > > when b.coltype = 22 or b.coltype = 277 then 'row' > > when b.coltype = 23 or b.coltype = 278 then 'collection' > > when b.coltype = 24 or b.coltype = 279 then 'rowref' > > end as columntype > > , b.coltype > from systables a, > > syscolumns b > where a.tabid = b.tabid > > and a.tabname not like 'sys%' > order by colname > ; > > You'll have to repeat it for each database in the instance. I haven't > worked out a way to do that entirely in SQL. You could add an additional > CASE pieces to each current case (nesting is allowed) to decode the null > v. not null and size of the numeric types. I just haven't been that > ambitious. > > Cheers, > Dick Snoke > IBM Data Management - ChannelWorks > dsnoke@us.ibm.com > (404) 487-1595 > > From: > "MIKE MAGIE" <jmmagie@yahoo.com> > To: > ids@iiug.org > Date: > 07/10/2008 02:36 PM > Subject: > Weird request [12637] > > Pertinents: > > Informix 10.00.UC8 on Solaris 2.8 > > Mission: > > Create a file that contains the following - > > database_name|table_name|column_name|column_data_type > > For like a bajillion databases with gobs of tables and oodles of columns. > > I have already began playing with awk and sed - and looking at sysmaster > queries and stuff. Coltype is the main issue - as many of you probably > know > the coltype is stored as a smallint, in decimal, that when converted to > hex > can be mapped to the actual data type, i.e if you had a coltype value of > 257 > you'd convert it to hex, get 101, map it to the coltype values and figure > that > you had a not null integer column. But that is like hard and stuff. I am > sure > I am trying to reinvent the wheel here and would love to see what some of > you > ladies and gents might come up with. > > MM > > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.
Awesome - thanks Dick! Also - thanks Fernando - never knew about that .sql file. MM
Okay I got around the problem with the database name not being listed in the
output in kind of a goofy way, but it works. Also handled the problem with
multiple databases - again kind of whacky - here is the ksh script I used:
#!/usr/bin/ksh
if [[ $# -lt 1 ]]
then
echo "*ERROR* Usage: Arg required"
echo " Arg: SERVER "
exit 1
fi
SERVER=$1
#### Get my list of databases ####
dbaccess sysmaster@${SERVER} - << EOT!
output to '/tmp/dbnames.out' without headings
select name from sysdatabases where name not like "sys%";EOT!
###In for loop run the query for each db#####
for DBNAME in `cat /tmp/dbnames.out`
do
dbaccess ${DBNAME}@${SERVER} - << EOT!
unload to '/tmp/info.out'
select '${DBNAME}' , substr(a.tabname,1,20) as table, substr(b.colname,1,20)
as column,
case when b.coltype = 0 or b.coltype = 256 then 'char'
when b.coltype = 1 or b.coltype = 256 then 'smallint'
when b.coltype = 2 or b.coltype = 257 then 'integer'
when b.coltype = 3 or b.coltype = 258 then 'float'
when b.coltype = 4 or b.coltype = 259 then 'smallfloat'
when b.coltype = 5 or b.coltype = 260 then 'decimal'
when b.coltype = 6 or b.coltype = 261 then 'serial'
when b.coltype = 7 or b.coltype = 262 then 'date'
when b.coltype = 8 or b.coltype = 263 then 'money'
when b.coltype = 9 or b.coltype = 264 then 'null'
when b.coltype = 10 or b.coltype = 265 then 'datetime'
when b.coltype = 11 or b.coltype = 266 then 'byte'
when b.coltype = 12 or b.coltype = 267 then 'text'
when b.coltype = 13 or b.coltype = 268 then 'varchar'
when b.coltype = 14 or b.coltype = 269 then 'varchar'
when b.coltype = 15 or b.coltype = 270 then 'nchar'
when b.coltype = 16 or b.coltype = 271 then 'nvchar'
when b.coltype = 17 or b.coltype = 272 then 'int8'
when b.coltype = 18 or b.coltype = 273 then 'serial8'
when b.coltype = 19 or b.coltype = 274 then 'set'
when b.coltype = 20 or b.coltype = 275 then 'multiset'
when b.coltype = 21 or b.coltype = 276 then 'list'
when b.coltype = 22 or b.coltype = 277 then 'row'
when b.coltype = 23 or b.coltype = 278 then 'collection'
when b.coltype = 24 or b.coltype = 279 then 'rowref'
end as columntype
, b.coltype
from systables a, syscolumns b
where a.tabid = b.tabid
and a.tabid > 99
order by tabname, colname;
EOT!
##### awk out the column I don't need ####
`cat /tmp/info.out | awk -F"|" '{print $1"|"$2"|"$3"|"$4"|"}' >
/tmp/${DBNAME}_info.out"`
done
#### create the file with all dbnames, tables, cols, and types!!!####
cat /tmp/*_info.out >> /tmp/master.out
Well I made some mods to the sql that Dick sent - it is kind of cloodgy and a
bit spaghetti-like, but it works... I am particulary proud of the handling of
decimals...
Anyhoo - Thanks Dick for getting the ball rolling and doing the major work.
MM
select a.tabname as table, b.colname as column,
case
when b.coltype = 0 then ('char(' || b.collength || ')')
when b.coltype = 256 then ('char(' || b.collength || ') not null')
when b.coltype = 1 then 'smallint'
when b.coltype = 257 then 'smallint not null'
when b.coltype = 2 then 'integer'
when b.coltype = 258 then 'integer not null'
when b.coltype = 3 then 'float'
when b.coltype = 259 then 'float not null'
when b.coltype = 4 then 'smallfloat'
when b.coltype = 260 then 'smallfloat not null'
when b.coltype = 5 then ('decimal(' || ROUND(b.collength/256) || "," ||
MOD(b.collength,256) || ')')
when b.coltype = 261 then ('decimal(' || ROUND(b.collength/256) || "," ||
MOD(b.collength,256) || ') not null')
when b.coltype = 6 then 'serial'
when b.coltype = 262 then 'serial not null'
when b.coltype = 7 then 'date'
when b.coltype = 263 then 'date not null'
when b.coltype = 8 then 'money'
when b.coltype = 264 then 'money not null'
when b.coltype = 9 then 'null'
when (b.coltype = 10) and b.collength = 4363 then 'datetime ' || "year to
fraction(1)"
when (b.coltype = 266) and b.collength = 4363 then 'datetime ' || "year to
fraction(1) not null"
when (b.coltype = 10) and b.collength = 4364 then 'datetime ' || "year to
fraction(2)"
when (b.coltype = 266) and b.collength = 4364 then 'datetime ' || "year to
fraction(2) not null"
when (b.coltype = 10) and b.collength = 4365 then 'datetime ' || "year to
fraction(3)"
when (b.coltype = 266) and b.collength = 4365 then 'datetime ' || "year to
fraction(3) not null"
when (b.coltype = 10) and b.collength = 4366 then 'datetime ' || "year to
fraction(4)"
when (b.coltype = 266) and b.collength = 4366 then 'datetime ' || "year to
fraction(4) not null"
when (b.coltype = 10) and b.collength = 4367 then 'datetime ' || "year to
fraction(5)"
when (b.coltype = 266) and b.collength = 4367 then 'datetime ' || "year to
fraction(5) not null"
when (b.coltype = 10) and b.collength = 3594 then 'datetime ' || "year to
second"
when (b.coltype = 266) and b.collength = 3594 then 'datetime ' || "year to
second not null"
when (b.coltype = 10) and b.collength = 3080 then 'datetime ' || "year to
minute"
when (b.coltype = 266) and b.collength = 3080 then 'datetime ' || "year to
minute not null"
when (b.coltype = 10) and b.collength = 2566 then 'datetime ' || "year to hour"
when (b.coltype = 266) and b.collength = 2566 then 'datetime ' || "year to
hour not null"
when (b.coltype = 10) and b.collength = 2052 then 'datetime ' || "year to day"
when (b.coltype = 266) and b.collength = 2052 then 'datetime ' || "year to day
not null"
when (b.coltype = 10) and b.collength = 1538 then 'datetime ' || "year to
month"
when (b.coltype = 266) and b.collength = 1538 then 'datetime ' || "year to
month not null"
when (b.coltype = 10) and b.collength = 1024 then 'datetime ' || "year to year"
when (b.coltype = 266) and b.collength = 1024 then 'datetime ' || "year to
year not null"
when b.coltype = 11 then 'byte'
when b.coltype = 267 then 'byte not null'
when b.coltype = 12 then 'text'
when b.coltype = 268 then 'text not null'
when b.coltype = 13 then ('varchar(' || b.collength || ')')
when b.coltype = 269 then ('varchar(' || b.collength || ') not null')
when b.coltype = 14 then ('varchar(' || b.collength || ')')
when b.coltype = 270 then ('varchar(' || b.collength || ') not null')
when b.coltype = 15 then 'nchar'
when b.coltype = 271 then 'nchar not null'
when b.coltype = 16 then 'nvchar'
when b.coltype = 272 then 'nvchar not null'
when b.coltype = 17 or b.coltype = 273 then 'int8'
when b.coltype = 18 or b.coltype = 274 then 'serial8'
when b.coltype = 19 or b.coltype = 275 then 'set'
when b.coltype = 20 or b.coltype = 276 then 'multiset'
when b.coltype = 21 or b.coltype = 277 then 'list'
when b.coltype = 22 or b.coltype = 278 then 'row'
when b.coltype = 23 or b.coltype = 279 then 'collection'
when b.coltype = 24 or b.coltype = 280 then 'rowref'
when (b.coltype = 40) and (b.collength = 2048) then 'lvarchar'
end as columntype
from systables a, syscolumns b
where a.tabid = b.tabid
and a.tabid > 99
order by tabname, colname;
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')
On Fri, Jul 18, 2008 at 9: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') > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > Mike, because you don't include the content of the original post, many of us have no idea what this "Weird request" is about. Please, please include the content of replies so that we all can follow the conversation. Thanks, Jim
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.ta
Also - big thanks to Dick Snoke for the original case statement. I added the stuff to get 'not nulls', and decimal/ character field lengths - but Dick got me started in a BIG way. Very cool spl also. MM
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';
>@@NL@
On Sun, Jul 20, 2008 at 7:14 AM, Art Kagel <art.kagel@gmail.com> wrote: > I don't see the new BIGINT or BIGSERIAL types in there either. 'Tis a fair cop, guv. The version uploaded to the IIUG (the second version - RCS version 1.5) has this and some other minor bits and pieces tweaked in it. > 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). >> [...] >> >> 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. -- 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.