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 :
I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.
Method 1
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 + 41b2= 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.
Method 2
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 🙂
All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0