All we need is an easy explanation of the problem, so here it is.
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?
How to solve :
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
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
b1= 297 = 256 + 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
1 for both.
Relevant segments of the header include:
#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
#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
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.
BLOB NOT NULL and
CLOB NOT NULL are both encoded in
coltype as 297 (41 + 256 — the same a
BOOLEAN NOT NULL), or
collength of 72 (as opposed to
BOOLEAN NOT NULL). The fixed-length data is a descriptor that provides all the details about where the
CLOB value is actually stored.
Here is the list of Informix datatypes:
0 = CHAR 1 = SMALLINT 2 = INTEGER 3 = FLOAT 4 = SMALLFLOAT 5 = DECIMAL 6 = SERIAL 7 = DATE 8 = MONEY 9 = NULL 10 = DATETIME 11 = BYTE 12 = TEXT 13 = VARCHAR 14 = INTERVAL 15 = NCHAR 16 = NVARCHAR 17 = INT8 18 = SERIAL8 19 = SET 20 = MULTISET 21 = LIST 22 = Unnamed ROW 40 = LVARCHAR 41 = CLOB 43 = BLOB 44 = BOOLEAN 256 = CHAR 257 = SMALLINT 258 = INTEGER 259 = FLOAT 260 = REAL 261 = DECIMAL 262 = SERIAL 263 = DATE 264 = MONEY 266 = DATETIME 267 = BYTE 268 = TEXT 269 = VARCHAR 270 = INTERVAL 271 = NCHAR 272 = NVARCHAR 273 = INT8 274 = SERIAL8 275 = SET 276 = MULTISET 277 = LIST 278 = Unnamed ROW 296 = LVARCHAR 297 = CLOB 298 = BLOB 299 = BOOLEAN 4118 = Named ROW
This information was found on this non-official webpage.
Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂