What are the various Informix column data types?

I am writing a query where I need to know if a column is of the "BLOB" type of column. According to this documentation 41 is the number for "BLOB" types of columns, in general. However, when I query the syscolumns table I see that columns, which store BLOB data, actually have values of 297, in my case. That page linked above does not even have number "297" anywhere on its page. I found another page, titled "Data Type Constants" but here again, number "297" is not even present anywhere on the page.

It appears that documentation's list of data types is not complete. Is there a (more) complete list of column types in Informix?

The values in the coltype column of the syscolumns system catalog table are defined primarily in the Informix ESQL/C header sqltypes.h. This header is used throughout the Informix server code.

The values in the coltype column are a mixture of an 8-bit (unsigned) integer in the low-order bits, and various flag values in the high-order bits. In particular, when a column is defined with the NOT NULL qualifier, the 0x100 bit is set — which corresponds to 'add 256'. There are other flag bits which you are less likely to see.

The value 4118 (decimal) shown in another answer corresponds to hex 0x1016; the 16 (aka 22 decimal) corresponds to SQLROW, and the 0x1000 (4096 decimal) bit corresponds to #define SQLNAMED 0x1000 / Named row type vs row type /. The type is (as noted in the other answer) a 'named row type'.

Consider a table:
CREATE TABLE bool_check
(
b1 BOOLEAN NOT NULL,
b2 BOOLEAN
);


The values in the sqltype column of syscolumns are:

• b1 = 297 = 256 + 41

• b2 = 41

These correspond to SQLUDTFIXED (type 41). The type SQLBOOL is marked 'used by FE [front end], … not real major types in BE [back end, meaning database server]'. The collength is 1 for both.

Relevant segments of the header include:

SQL Types:
#define SQLCHAR 0
#define SQLSMINT 1
#define SQLINT 2
#define SQLFLOAT 3
#define SQLSMFLOAT 4
#define SQLDECIMAL 5
#define SQLSERIAL 6
#define SQLDATE 7
#define SQLMONEY 8
#define SQLNULL 9
#define SQLDTIME 10
#define SQLBYTES 11
#define SQLTEXT 12
#define SQLVCHAR 13
#define SQLINTERVAL 14
#define SQLNCHAR 15
#define SQLNVCHAR 16
#define SQLINT8 17
#define SQLSERIAL8 18
#define SQLSET 19
#define SQLMULTISET 20
#define SQLLIST 21
#define SQLROW 22
#define SQLCOLLECTION 23
#define SQLROWREF 24
/*
* Note: SQLXXX values from 25 through 39 are reserved to avoid collision
* with reserved PTXXX values in that same range. See p_types_t.h
*
* REFSER8: create tab with ref: referenced serial 8 rsam counter
* this is essentially a SERIAL8, but is an additional rsam counter
* this type only lives in the system catalogs and when read from
* disk is converted to SQLSERIAL8 with CD_REFSER8 set in ddcol_t
* ddc_flags we must distinguish from SERIAL8 to allow both
* counters in one tab
*
* SQLSTREAM: Is a synonym for SQLUDTFIXED used by CDR (Enterprise
* Replication) code
*/
#define SQLUDTVAR 40
#define SQLUDTFIXED 41
#define SQLSTREAM SQLUDTFIXED
#define SQLREFSER8 42

/ These types are used by FE, they are not real major types in BE /
#define SQLLVARCHAR 43
#define SQLSENDRECV 44
#define SQLBOOL 45
#define SQLIMPEXP 46
#define SQLIMPEXPBIN 47

/* This type is used by the UDR code to track default parameters,
it is not a real major type in BE */
#define SQLUDRDEFAULT 48
#define SQLUNKNOWN 51
#define SQLBIGINT 52
#define SQLBIGSERIAL 53
#define SQLMAXTYPES 54

#define SQLLABEL SQLINT


Flags:
#define SQLNONULL 0x0100 / disallow nulls /
/ a bit to show that the value is from a host variable /
#define SQLHOST 0x0200 / Value is from host var. /
#define SQLNETFLT 0x0400 / float-to-decimal for networked backend /
#define SQLDISTINCT 0x0800 / distinct bit /
#define SQLNAMED 0x1000 / Named row type vs row type /
#define SQLDLVARCHAR 0x2000 / Distinct of lvarchar /
#define SQLDBOOLEAN 0x4000 / Distinct of boolean /
#define SQLCLIENTCOLL 0x8000 / Collection is processed on client /

/ we are overloading SQLDBOOLEAN for use with row types /
#define SQLVARROWTYPE 0x4000 / varlen row type /


There are also 'C-ISAM types' with numbers 100 to 125 and names such a CCHARTYPE and CDECIMALTYPE. They are not of immediate concern here. There are 524 lines in the header file (at least in the version I looked at). Of those, 74 are blank, 315 contain code, and the remaining lines are pure comment lines. AFAIK, the SQLREFSER8 type is stillborn; it does not exist outside this file.

The types BLOB NOT NULL and CLOB NOT NULL are both encoded in coltype as 297 (41 + 256 — the same a BOOLEAN NOT NULL), or SQLUDTFIXED with collength of 72 (as opposed to 1 for BOOLEAN NOT NULL). The fixed-length data is a descriptor that provides all the details about where the BLOB or CLOB value is actually stored.

StackExchange Database Administrators Q#289296, answer score: 8