Figure 2-1 Correspondence of SQL and ESQL/C Data Types SQL Data Type ESQL/C Predefined Data Type C Language Type BYTE loc_t CHAR(n)CHARACTER(n) fixchar array[n] or string array[n+1] char array[n + 1] or char * DATE date long int DATETIME datetime or dtime_t DECIMALDECNUMERICMONEY decimal or dec_t FLOATDOUBLE PRECISION double INTEGERINT 4-byte integer INTERVAL interval or intrvl_t MULTISET(e) collection NCHAR(n) fixchar array[n] orstring array[n+1] char array[n + 1] or char * NVARCHAR(m) varchar[m+1] orstring array[m+1] char array[m+1] SERIAL 4-byte integer SMALLFLOATREAL float SMALLINT 2-byte integer TEXT loc_t VARCHAR(m,x) varchar[m+1] orstring array[m+1] char array[m+1]
BYTE
loc_t
CHAR(n)CHARACTER(n)
fixchar array[n] or string array[n+1]
char array[n + 1] or char *
DATE
date
long int
DATETIME
datetime or dtime_t
DECIMALDECNUMERICMONEY
decimal or dec_t
FLOATDOUBLE PRECISION
double
INTEGERINT
4-byte integer
INTERVAL
interval or intrvl_t
MULTISET(e)
collection
NCHAR(n)
fixchar array[n] orstring array[n+1]
NVARCHAR(m)
varchar[m+1] orstring array[m+1]
char array[m+1]
SERIAL
SMALLFLOATREAL
float
SMALLINT
2-byte integer
TEXT
VARCHAR(m,x)
Figure 2-2 SQL Data Type ESQL/C Predefined Data Type C Language Type BLOB ifx_lo_t BOOLEAN boolean CLOB ifx_lo_t INT8 int8 or ifx_int8_t 8-byte integer LIST(e) collection LVARCHAR lvarchar char Opaque data type lvarchar, fixed binary, or var binary ROW(...) row SERIAL8 int8 or ifx_int8_t 8-byte integer SET(e) collection IUS Correspondence of SQL and ESQL/C Data Types Specific to INFORMIX-Universal Server To declare a host variable for an SQL data type, you must use the appropriate ESQL/C predefined data type, or structure, for the host variable. Figure 2-3 shows where you can find the description of how to use these specialized structures for the appropriate host data type for all database servers. Figure 2-4 shows where you can find the description of how to use these specialized structures that are specific to Universal Server.
BLOB
ifx_lo_t
BOOLEAN
boolean
CLOB
INT8
int8 or ifx_int8_t
8-byte integer
LIST(e)
LVARCHAR
lvarchar
char
Opaque data type
lvarchar, fixed binary, or var binary
ROW(...)
row
SERIAL8
SET(e)
Figure 2-3 References for Using Host Data Types Data Type Reference BYTE Chapter 8, "Working with Simple Large Objects" CHAR Chapter 3, "Working with Character and String Data Types" DATETIME Chapter 6, "Working with Time Data Types" DECIMAL Chapter 4, "Working with the DECIMAL Data Type" FLOAT Chapter 5, "Working with Numeric Data Types" INTEGER Chapter 5, "Working with Numeric Data Types" INTERVAL Chapter 6, "Working with Time Data Types" LIST Chapter 9, "Working with INFORMIX-Universal Server Complex Data Types" MONEY Chapter 4, "Working with the DECIMAL Data Type" NCHAR Chapter 3, "Working with Character and String Data Types" NVARCHAR Chapter 3, "Working with Character and String Data Types" SERIAL Chapter 5, "Working with Numeric Data Types" SERIAL8 Chapter 5, "Working with Numeric Data Types" SMALLFLOAT Chapter 5, "Working with Numeric Data Types" SMALLINT Chapter 5, "Working with Numeric Data Types" TEXT Chapter 8, "Working with Simple Large Objects" VARCHAR Chapter 3, "Working with Character and String Data Types"
Chapter 8, "Working with Simple Large Objects"
CHAR
Chapter 3, "Working with Character and String Data Types"
Chapter 6, "Working with Time Data Types"
DECIMAL
Chapter 4, "Working with the DECIMAL Data Type"
FLOAT
Chapter 5, "Working with Numeric Data Types"
INTEGER
LIST
Chapter 9, "Working with INFORMIX-Universal Server Complex Data Types"
MONEY
NCHAR
NVARCHAR
SMALLFLOAT
VARCHAR
Figure 2-4 Data Type Reference BLOB Chapter 7, "Working with INFORMIX-Universal Server Smart Large Objects" BOOLEAN "The BOOLEAN Data Type" CLOB Chapter 7, "Working with INFORMIX-Universal Server Smart Large Objects" INT8 Chapter 5, "Working with Numeric Data Types" LVARCHAR Chapter 3, "Working with Character and String Data Types" MULTISET Chapter 9, "Working with INFORMIX-Universal Server Complex Data Types" Opaque Chapter 10, "Working with INFORMIX-Universal Server Opaque Data Types" ROW Chapter 9, "Working with INFORMIX-Universal Server Complex Data Types" SET Chapter 9, "Working with INFORMIX-Universal Server Complex Data Types" IUS References for Using Host Data Types That Are Specific to INFORMIX-Universal Server Defined Constants for Data Types The sqltypes.h file contains the following two sets of defined integer constants for the data types that Informix database servers use:
Chapter 7, "Working with INFORMIX-Universal Server Smart Large Objects"
"The BOOLEAN Data Type"
MULTISET
Opaque
Chapter 10, "Working with INFORMIX-Universal Server Opaque Data Types"
ROW
SET
Figure 2-5 Constants for Informix SQL Column Data Types SQL Data Type Defined Constant Integer Value CHAR SQLCHAR 0 SMALLINT SQLSMINT 1 INTEGER SQLINT 2 FLOAT SQLFLOAT 3 SMALLFLOAT SQLSMFLOAT 4 DECIMAL SQLDECIMAL 5 SERIAL SQLSERIAL 6 DATE SQLDATE 7 MONEY SQLMONEY 8 DATETIME SQLDTIME 10 BYTE SQLBYTES 11 TEXT SQLTEXT 12 VARCHAR SQLVCHAR 13 INTERVAL SQLINTERVAL 14 NCHAR SQLNCHAR 15 NVARCHAR SQLNVCHAR 16
SQLCHAR
0
SQLSMINT
1
SQLINT
2
SQLFLOAT
3
SQLSMFLOAT
4
SQLDECIMAL
5
SQLSERIAL
6
SQLDATE
7
SQLMONEY
8
SQLDTIME
10
SQLBYTES
11
SQLTEXT
12
SQLVCHAR
13
SQLINTERVAL
14
SQLNCHAR
15
SQLNVCHAR
16
Figure 2-6 SQL Data Type Defined Constant Integer Value INT8 SQLINT8 17 SERIAL8 SQLSERIAL8 18 SET SQLSET 19 MULTISET SQLMULTISET 20 LIST SQLLIST 21 ROW SQLROW 22 Varying-length opaque type SQLUDTVAR 40 Fixed-length opaque type SQLUDTFIXED 41 LVARCHAR SQLLVARCHAR 43 BOOLEAN SQLBOOL 45 IUS Constants for Informix SQL Column Data Types That Are Specific to INFORMIX-Universal Server However, if your programs conform to the X/Open standards (compile with the -xopen option), you must use the data- type values that Figure 2-7 shows. Informix defines the constants for these values in the sqlxtype.h header file.
SQLINT8
17
SQLSERIAL8
18
SQLSET
19
SQLMULTISET
20
SQLLIST
21
SQLROW
22
Varying-length opaque type
SQLUDTVAR
40
Fixed-length opaque type
SQLUDTFIXED
41
SQLLVARCHAR
43
SQLBOOL
45
Figure 2-7 Constants for Informix SQL Column Data Types in an X/Open Environment SQL Data Type Defined Constant X/Open Integer Value CHAR XSQLCHAR 1 DECIMAL XSQLDECIMAL 3 INTEGER XSQLINT 4 SMALLINT XSQLSMINT 5 FLOAT XSQLFLOAT 6 The sqltypes.h header file also contains constants for the ESQL/C data types. You assign an ESQL/C data type to a host variable in an ESQL/C program. Figure 2-8 shows these constants.
XSQLCHAR
XSQLDECIMAL
XSQLINT
XSQLSMINT
XSQLFLOAT
Figure 2-8 Constants for ESQL/C Host-Variable Data Types (1 of 2) ESQL/C Data Type Constant Integer Value char CCHARTYPE 100 short int CSHORTTYPE 101 int (long or short depending on machine type) CINTTYPE 102 long CLONGTYPE 103 float CFLOATTYPE 104 double CDOUBLETYPE 105 dec_t or decimal CDECIMALTYPE 107 fixchar CFIXCHARTYPE 108 string CSTRINGTYPE 109 long or date CDATETYPE 110 dec_t or decimal CMONEYTYPE 111 datetime or dtime_t CDTIMETYPE 112 loc_t CLOCATORTYPE 113 varchar CVCHARTYPE 114 intrvl_t or interval CINVTYPE 115 char CFILETYPE 116 int8 CINT8TYPE 117 collection CCOLTYPE 118 lvarchar CLVCHARTYPE 119 fixed binary CFIXBINTYPE 120 var binary CVARBINTYPE 121 boolean CBOOLTYPE 122 row CROWTYPE 123 You can use these ESQL/C data types as arguments for some of the functions in the ESQL/C library. When you need to use one of these functions, check the contents of the sqltypes.h file for the appropriate value. Header Files for Data Types To use an SQL data type, your program must include the appropriate ESQL/C header file. Figure 2-9 shows the relationship between host-variable data types and ESQL/C header files for all database servers. Figure 2-10 shows the relationship between host-variable data types and ESQL/C header files that are specific to Universal Server.
(1 of 2)
CCHARTYPE
short int
CSHORTTYPE
int (long or short depending on machine type)
CINTTYPE
long
CLONGTYPE
CFLOATTYPE
CDOUBLETYPE
dec_t or decimal
CDECIMALTYPE
fixchar
CFIXCHARTYPE
string
CSTRINGTYPE
long or date
CDATETYPE
CMONEYTYPE
CDTIMETYPE
CLOCATORTYPE
varchar
CVCHARTYPE
intrvl_t or interval
CINVTYPE
CFILETYPE
int8
CINT8TYPE
CCOLTYPE
CLVCHARTYPE
fixed binary
CFIXBINTYPE
var binary
CVARBINTYPE
CBOOLTYPE
CROWTYPE
Figure 2-9 SQL Data Types and ESQL/C Header Files (1 of 2) SQL Data Type ESQL/C or C Data Type ESQL/C Header File BLOB ifx_lo_t locator.h BYTE loc_t locator.h CHAR(n)CHARACTER(n) fixchar array[n] or string array[n+1] Defined automatically DATE date Defined automatically DATETIME datetime or dtime_t datetime.h DECIMALDECNUMERICMONEY decimal or dec_t decimal.h FLOATDOUBLE PRECISION double Defined automatically INTEGERINT long int Defined automatically INTERVAL interval or intrvl_t datetime.h LVARCHAR lvarchar Defined automatically MULTISET(e) collection Defined automatically NCHAR(n) fixchar array[n] orstring array[n+1] Defined automatically NVARCHAR(m) varchar[m+1] orstring array[m+1] Defined automatically SERIAL long int Defined automatically SMALLFLOATREAL float Defined automatically SMALLINT short int Defined automatically TEXT loc_t locator.h VARCHAR(m,x) varchar[m+1] orstring array [m+1] Defined automatically
locator.h
Defined automatically
datetime.h
decimal.h
varchar[m+1] orstring array [m+1]
Figure 2-10 SQL Data Type ESQL/C or C Data Type ESQL/C Header File BOOLEAN boolean Defined automatically CLOB ifx_lo_t locator.h INT8 int8 int8.h LIST(e) collection Defined automatically Opaque data type lvarchar or fixed binary or var binary User-defined header file that contains definition of internal structure for opaque type ROW(...) row Defined automatically SERIAL8 int8 int8.h SET(e) collection Defined automatically IUS SQL Data Types and ESQL/C Header Files That Are Specific to INFORMIX-Universal Server
int8.h
lvarchar or fixed binary or var binary
User-defined header file that contains definition of internal structure for opaque type
