2

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?

1
  • After getting an answer from Jonathan I realized that the "answer" is under the first link, under the section titled "NOT NULL constraints".
    – wha7ever
    Apr 6 at 12:09
7

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.

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.

2
  • @mustaccio I found it on this non-official page. Looks like somebody took a very deep look into it and found the answer. Maybe they looked into that sqltype.h header file. If somebody can find official source of this information it would be amazing, but until then this is better than nothing.
    – wha7ever
    Apr 5 at 15:01
  • 2
    Note that the basic values are in the range 0..44 in the list shown; the values in the range 256..299 should be the same as the type minus 256. The 0x100 bit indicates that the column is qualified with 'NOT NULL'. Thus, BOOLEAN NOT NULL should have the value 300, not 299. In fact, though, the ESQL/C header file sqltypes.h lists BOOLEAN as #define SQLBOOL 45, but CREATE TABLE bool_check(b1 BOOLEAN NOT NULL, b2 BOOLEAN) creates columns with type numbers 41 and 297 in the syscolumns table, and 41 corresponds to SQLUDTFIXED. Apr 5 at 17:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.