UDF declarations support only certain SQL data types.
You can use the following SQL data types in a UDF declaration, either as data types for arguments to a UDF, or as return-value data types:
| SQL Data Type | C or C++ Data Type Identifier | C or C++ Typedef | Description |
|---|---|---|---|
| UNSIGNED BIGINT | DT_UNSBIGINT | a_sql_uint64 | An unsigned 64-bit integer, requiring 8 bytes of storage. |
| BIGINT | DT_BIGINT | a_sql_int64 | A signed 64-bit integer, requiring 8 bytes of storage. |
| UNSIGNED INT | DT_UNSINT | a_sql_uint32 | An unsigned 32-bit integer, requiring 4 bytes of storage. |
| INT | DT_INT | a_sql_int32 | A signed 32-bit integer, requiring 4 bytes of storage. |
| SMALLINT | DT_SMALLINT | short | A signed 16-bit integer, requiring 2 bytes of storage. |
| TINYINT | DT_TINYINT | unsigned char | An unsigned 8-bit integer, requiring 1 byte of storage. |
| DOUBLE | DT_DOUBLE | double | A signed 64-bit double-precision floating point number, requiring 8 bytes of storage. |
| REAL | DT_FLOAT | float | A signed 32-bit floating point number, requiring 4 bytes of storage. |
| FLOAT | DT_FLOAT | float | In SQL, depending on the associated precision, a FLOAT is either a signed 32-bit floating point number requiring 4 bytes of storage, or a signed 64-bit double-precision floating point number requiring 8 bytes of storage. You can use the SQL data type FLOAT only in a UDF declaration if the optional precision for FLOAT data types is not supplied. Without a precision, FLOAT is a synonym for REAL. |
| CHAR(<n>) | DT_FIXCHAR | char | A fixed-length blank-padded character string, in the database default character set. The maximum possible length, “<n>”, is 32767. The data is not null-byte terminated. |
| VARCHAR(<n>) | DT_VARCHAR | char | A varying-length character string, in the database default character set. The maximum possible length, “<n>”, is 32767. The data is not null-byte terminated. For UDF input arguments, the actual length, when the value is not NULL, must be retrieved from the total_len field within the an_extfn_value structure. Similarly, for a UDF result of this type, the actual length must be set in the total_len field. |
| LONG VARCHAR(<n>) or CLOB | DT_VARCHAR | char | A varying-length character string, in the database default character set. Use the LONG VARCHAR data type only as an input argument, not as a return-value data type. The maximum possible length, “<n>”, is 4GB (gigabytes) for v3 UDFs. The data is not null-byte terminated. LONG VARCHAR data type can have a WD or TEXT index. For UDF input arguments, the actual length, when the value is not NULL, must be retrieved from the total_len field within the an_extfn_value structure. You need not rebuild or recompile an existing scalar or aggregate UDF to use a LOB data type as an input parameter, if the function contains a loop that reads pieces of the value via the get_value() and get_piece() methods. The loop continues until remain_len > 0 or until 4GB is reached for v3 UDFs (there is no 4GB limit in v4). Table UDFs and TPFs do not use the get_piece() method to process and retrieve data. Table UDFs and TPFs must use the Blob (a_v4_extfn_blob) API instead. Use blob_length to determine length of input parameters. Large object data support requires a separately licensed SAP Sybase IQ option. |
| BINARY(<n>) | DT_BINARY | unsigned char | A fixed-length null-byte padded binary, value with a maximum possible binary length, “<n>”, of 32767. The data is not null-byte terminated. |
| VARBINARY(<n>) | DT_BINARY | unsigned char | A varying-length binary value, for which the maximum possible length, “<n>”, is 32767. The data is not null-byte terminated. For UDF input arguments, the actual length, when the value is not NULL, must be retrieved from the total_len field within the an_extfn_value structure. Similarly, for a UDF result of this type, you must set the actual length in the total_len field. The data is not null-byte terminated. |
| LONG BINARY(<n>) or BLOB | DT_BINARY | unsigned char | A fixed-length null-byte padded binary, value with a maximum possible binary length, “<n>”, of 4GB (gigabytes) for v3 UDFs. Use the LONG BINARY data type only as an input argument, not as a return-value data type. You need not rebuild or recompile an existing scalar or aggregate UDF to use a LOB data type as an input parameter, if the function contains a loop that reads pieces of the value via the get_value() and get_piece() methods. The loop continues until remain_len > 0 or until 4GB is reached for v3 UDFs (there is no 4GB limit in v4). Table UDFs and TPFs do not use the get_piece() method to process and retrieve data. Table UDFs and TPFs must use the Blob (a_v4_extfn_blob) API instead. Use blob_length to determine length of input parameters. Large object data support requires a separately licensed SAP Sybase IQ option. |
| DATE | DT_TIMESTAMP_STRUCT | unsigned integer | A calendar date value, which is passed to or from a UDF as an unsigned integer. The value given to the UDF is guaranteed to be usable in comparison and sorting operations. A larger value indicates a later date. If the actual date components are required, the UDF must invoke the convert_value function in order to convert to the type DT_TIMESTAMP_STRUCT. This date type represents date and time with this structure: typedef struct sqldatetime {
unsigned short year; /* e.g. 1992*/
unsigned char month; /* 0-11 */
unsigned char day_of_week; /* 0-6 0=Sunday, 1=Monday, ... */
unsigned short day_of_year; /* 0-365 */
unsigned char day; /* 1-31 */
unsigned char hour; /* 0-23 */
unsigned char minute; /* 0-59 */
unsigned char second; /* 0-59 */
a_sql_uint32 microsecond; /* 0-999999 */
} SQLDATETIME;
|
| DT_TIMESTAMP_STRUCT | unsigned bigint | A value that precisely describes a moment within a given day. . The value given to the UDF is guaranteed to be usable in comparison and sorting operations. A larger value indicates a later time. If the actual time components are required, the UDF must invoke the convert_value function to convert to the type DT_TIMESTAMP_STRUCT. | |
| DATETIME, SMALLDATETIME, or TIMESTAMP | DT_TIMESTAMP_STRUCT | unsigned bigint | A calendar date and time value. The value given to the UDF is guaranteed to be usable in comparison and sorting operations. A larger value indicates a later datetime. If the actual time components are required, the UDF must invoke the convert_value function to convert to the type DT_TIMESTAMP_STRUCT. |
| TABLE | DT_EXTFN_TABLE | a_v4_extfn_table | Represents an input table parameter result set. This datatype is only available on TPFs. |
You cannot use the following SQL data types in a UDF declaration, either as data types for arguments to a UDF, or as return-value data types:
BIT – should typically be handled in the UDF declaration as a TINYINT data type, and then the implicit data type conversion from BIT automatically handles the value translation.
DECIMAL(<precision>, <scale>) or NUMERIC(<precision>, <scale>) – depending on the usage, DECIMAL is typically handled as a DOUBLE data type, but various conventions may be imposed to enable the use of INT or BIGINT data types.
LONG VARCHAR (CLOB) – supported only as an input argument, not as a return-value data type. An exception exists for pass-through TPFs, where LONG VARCHAR is supported as a return-value data type.
LONG BINARY (BLOB) – supported only as an input argument, not as a return-value data type. An exception exists for pass-through TPFs, where LONG BINARY is supported as a return-value data type.
TEXT – not currently supported.