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.
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[] |
DB2ParameterClass.ParameterName
property of the type
DB2Type.Xml can accept variables of the following types: String, byte[],
DB2Xml, and XmlReader.