Output Mode

DB Link uses its own types, mapped to C or C++ types, structures, and objects, to fetch or send the data values from or to a database server. Due to variations in the way different RDBMSs implement their own types, the correspondence may vary. However, the SQL standard types, when they exist, are handled by the same DB Link types, regardless of the RDBMS. That is:

• CHAR, VARCHAR, NCHAR, LVARCHAR, and NVARCHAR are mapped to IldStringType.

• INTEGER and SMALLINT are mapped to IldIntegerType.

• FLOAT, REAL, and DOUBLE PRECISION are mapped to IldRealType.

• NUMBER, NUMERIC, DECIMAL are mapped to:

  • IldRealType when the default settings are active.

  • IldStringType when the “numeric as string” feature is turned on.

  • IlNumeric when the “numeric as object” feature is turned on.

• DATE, TIME, and TIMESTAMP are mapped to IldDateType or IldDateTimeType when the “date as string” feature is turned off.

The following tables are organized by RDBMS. They show which DB Link type is used to retrieve data from the database. Tables for the following RDBMSs are provided:

• DB2

• Informix

• MS SQL Server

• ODBC

• Oracle

• Sybase

DB2

Mapping between DB Link Types and DB2 Types

DB Link Type	SQL Type
IldByteType	-
IldStringType	CHAR, CHAR FOR BIT DATA VARCHAR, VARCHAR FOR BIT DATA
IldDateType IldDateTimeType	DATE TIME TIMESTAMP
IldRealType1 When the numeric as string feature is turned on, the column data types are converted into IldStringType. IldNumericType	DECIMAL NUMERIC FLOAT DOUBLE REAL
IldIntegerType	INTEGER, SMALLINT
IldLongTextType	LONG VARCHAR
IldBinaryType	LONG VARCHAR FOR BIT DATA
IldBLOBType	BLOB
IldCLOBType	CLOB
IldDecFloatType	DEC_FLOAT

Informix

Mapping between DB Link Types and Informix Types

DB Link Type	SQL Type
IldByteType	-
IldStringType	CHAR, CHARACTER NCHAR CHARACTER VARYING, VARCHAR NVARCHAR, LVARCHAR
IldDateType IldDateTimeType	DATE DATETIME INTERVAL
IldRealType2 When the numeric as string feature is turned on, the column data types are converted into IldStringType. IldNumericType	DEC, DECIMAL, NUMERIC REAL, SMALLFLOAT DOUBLE PRECISION, FLOAT
IldIntegerType	INT, INTEGER SMALLINT SERIAL
IldMoneyType(1)	MONEY
IldLongTextType	TEXT
IldBinaryType	BYTE
IldCollectionType3 Only supported for Informix Universal Server.	LIST, SET, MULTISET
IldObjectType(2)	[NAMED] ROW
IldCLOBType	CLOB
IldBLOBType	BLOB

MS SQL Server

Mapping Between DB Link Types and MS SQL Server Types

DB Link Type	SQL Type
IldByteType	TINYINT BIT
IldIntegerType	SMALLINT INT
IldRealType	NUMERIC4 When the numeric as string feature is turned on, these column data types are converted into IldStringType. DECIMAL(1) FLOAT, DOUBLE PRECISION REAL
IldMoneyType	SMALLMONEY MONEY
IldDateType IldDateTimeType	SMALLDATETIME DATETIME
IldStringType	CHAR, NCHAR VARCHAR, NVARCHAR BINARY
IldLongTextType	TEXT
IldBinaryType	IMAGE

ODBC

Mapping Between DB Link Types and ODBC Types

DB Link Type	SQL Type
IldByteType	SQL_BIT SQL_TINYINT
IldIntegerType	SQL_SMALLINT SQL_INTEGER SQL_BIGINT
IldRealType	SQL_FLOAT SQL_DOUBLE SQL_REAL SQL_DECIMAL5 When the numeric as string feature is turned on, these column data types are converted into IldStringType. SQL_NUMERIC(1)
IldStringType	SQL_CHAR SQL_VARCHAR SQL_BINARY SQL_VARBINARY
IldDateType IldDateTimeType	SQL_DATE SQL_TIME SQL_TIMESTAMP
IldMoneyType	6 Database Money Type is translated to NUMERIC or DECIMAL.
IldLongTextType	SQL_LONGVARCHAR
IldBinaryType	SQL_LONGVARBINARY

Oracle

Mapping Between DB Link Types and Oracle Types

DB Link Type	SQL Type
IldByteType	-
IldStringType	CHAR, CHARACTER VARCHAR, VARCHAR2, CHARACTER VARYING, CHAR VARYING ROWID MLSLABEL RAW
IldIntegerType7 All numeric types are described through the external type SQLT_NUM. DB Link differentiates between integer and floating-point numbers using the precision and scale values. If the scale is non-null or is null and precision is null or greater than 10, or if both scale and precision are null, then the default type is IldRealType. Otherwise, it is IldIntegerType.Such a protocol leaves a potential problem for numbers with a precision set to 10 and no scale. They can overflow the C or C++ limit for integer values. If such an overflow occurs due to the values stored in the database system, your application can use the “numeric as string” or “numeric as object” features. IldRealType8 When the numeric as string feature is turned on, these column data types are converted into IldStringType. IldNumericType	NUMBER, NUMERIC, DECIMAL, DEC, INTEGER, INT, SMALLINT, FLOAT, DOUBLE PRECISION, REAL, BINARY_FLOAT, BINARY_DOUBLE
IldDateType9 When the date as string feature is turned off, these column data types are converted into IldDateTimeType. IldDateTimeType	DATE,TIMESTAMP, TIMESTAMP WITH TIME ZONE, TIMESTAMP WITH LOCAL TIME ZONE, INTERVAL YEAR TO MONTH, INTERVAL DAY TO SECOND
IldMoneyType	-
IldLongTextType	LONG, LONGVARCHAR
IldBinaryType	LONG RAW
IldCollectionType	VARRAY NESTED TABLE
IldObjectType	OBJECT
IldCursorType	CURSOR
IldBLOBType	BLOB
IldCLOBType	CLOB

Sybase

Mapping Between DB Link Types and Sybase Types

DB Link Type	SQL Type
IldByteType	TINYINT BIT
IldIntegerType	SMALLINT INT
IldRealType	NUMERIC10 When the numeric as string feature is turned on, these column data types are converted into IldStringType. DECIMAL(1) FLOAT, DOUBLE PRECISION REAL
IldMoneyType	SMALLMONEY MONEY
IldDateType IldDateTimeType	SMALLDATETIME DATETIME
IldStringType	CHAR, NCHAR VARCHAR, NVARCHAR BINARY
IldLongTextType	TEXT
IldBinaryType	IMAGE