SQL data type representation in .NET CLR routines

.NET CLR routines can reference SQL data type values as routine parameters, parameter values to be used as part of SQL statement execution, and as variables, however the appropriate IBM SQL data type values, IBM® Data Server Provider for .NET data type values, and .NET Framework data type values must be used to ensure that there is no truncation or loss of data when accessing or retrieving the values.

For routine parameter specifications within the CREATE PROCEDURE or CREATE FUNCTION statements used to create .NET CLR routines, SQL data type values are used. Most SQL data types can be specified for routine parameters, however there are some exceptions.

For specifying parameter values to be used as part of an SQL statement to be executed, IBM Data Server Provider for .NET objects must be used. The DB2Parameter object is used to represent a parameter to be added to a DB2Command object which represents a SQL statement. When specifying the data type value for the parameter, the IBM Data Server Provider for .NET data type values available in the IBM.Data.DB2Types namespace must be used. The IBM.Data.DB2Types namespace provides classes and structures to represent each of the supported SQL data types.

For parameters and local variables that might temporarily hold SQL data type values appropriate IBM Data Server Provider for .NET data types, as defined in the IBM.Data.DB2Types Namespace, must be used.

Note: The dbinfo structure is passed into CLR functions and procedures as a parameter. The scratchpad and call type for CLR UDFs are also passed into CLR routines as parameters. For information about the appropriate CLR data types for these parameters, see the related topic:

The following table shows mappings between DB2Type data types, database data types, Informix® data types, Microsoft .NET Framework types, and DB2Types classes and structures.

Category DB2Types Classes and Structures DB2Type Data Type Database Data Type Informix Data Type .NET Data Type
Numeric DB2Int16 SmallInt SMALLINT BOOLEAN, SMALLINT Int16
Numeric DB2Int32 Integer INT INTEGER, INT, SERIAL Int32
Numeric DB2Int64 BigInt BIGINT BIGINT, BIGSERIAL, INT8, SERIAL8 Int64
Numeric DB2Real, DB2Real370 Real REAL REAL, SMALLFLOAT Single
Numeric DB2Double Double DOUBLE PRECISION DECIMAL (≤31), DOUBLE PRECISION Double
Numeric DB2Double Float FLOAT DECIMAL (32), FLOAT Double
Numeric DB2Decimal Decimal DECIMAL MONEY Decimal
Numeric DB2DecimalFloat DecimalFloat DECFLOAT (16|34)1,4   Decimal
Numeric DB2Decimal Numeric DECIMAL DECIMAL (≤31), NUMERIC Decimal
Date/Time DB2Date Date DATE DATETIME (date precision) Datetime
Date/Time DB2Time Time TIME DATETIME (time precision) TimeSpan
Date/Time DB2TimeStamp Timestamp TIMESTAMP DATETIME (time and date precision) DateTime
XML DB2Xml Xml2 XML   Byte[]
Character data DB2String Char CHAR CHAR String
Character data DB2String VarChar VARCHAR VARCHAR String
Character data DB2String LongVarChar1 LONG VARCHAR LVARCHAR String
Binary data DB2Binary Binary CHAR FOR BIT DATA   Byte[]
Binary data DB2Binary Binary3 BINARY   Byte[]
Binary data DB2Binary VarBinary3 VARBINARY   Byte[]
Binary data DB2Binary LongVarBinary1 LONG VARCHAR FOR BIT DATA   Byte[]
Graphic data DB2String Graphic GRAPHIC   String
Graphic data DB2String VarGraphic VARGRAPHIC   String
Graphic data DB2String LongVarGraphic1 LONG VARGRAPHIC   String
LOB data DB2Clob Clob CLOB CLOB, TEXT String
LOB data DB2Blob Blob BLOB BLOB, BYTE Byte[]
LOB data DB2Clob DbClob DBCLOB   String
Row ID DB2RowId RowId ROWID   Byte[]
1 These data types are not supported as parameters in the .NET common language runtime routines.
2 A DB2ParameterClass.ParameterName property of the type DB2Type.Xml can accept variables of the following types: String, byte[], DB2Xml, and XmlReader.
3 These data types are applicable only to Db2® for z/OS®.
4 This data type is only supported for Db2 for z/OS, Version 9 and later releases and for Db2 Version 9.5 and later releases.