SQL Data Types

UNSIGNED BIGINT – an unsigned 64-bit integer, requiring 8 bytes of storage. The data type identifier to be used within UDF code is DT_UNSBIGINT, and the C/C++ data type typedef to be used for such values within a UDF is “a_sql_uint64”. Several C/C++ typedefs are included with Sybase IQ to make it easier for application developers to write portable UDF implementations.

BIGINT – a signed 64-bit integer, requiring 8 bytes of storage. The data type identifier is DT_BIGINT, and the C/C++ data type typedef to be used for such values is “a_sql_int64.”

UNSIGNED INT – an unsigned 32-bit integer, requiring 4 bytes of storage. The data type identifier is DT_UNSINT, and the C/C++ data type typedef to be used for such values is “a_sql_uint32.”

INT – a signed 32-bit integer, requiring 4 bytes of storage. The data type identifier is DT_INT, and the C/C++ data type typedef to be used for such values is “a_sql_int32.”

SMALLINT – a signed 16-bit integer, requiring 2 bytes of storage. The data type identifier is DT_SMALLINT, and the C/C++ data type to be used for such values is “short.”

TINYINT – An unsigned 8-bit integer, requiring 1 byte of storage. The data type identifier is DT_TINYINT, and the C/C++ data type to be used for such values is “unsigned char.”

DOUBLE – a signed 64-bit double-precision floating point number, requiring 8 bytes of storage. The data type identifier is DT_DOUBLE, and the C/C++ data type to be used for such values is “double.”

REAL – a signed 32-bit floating point number, requiring 4 bytes of storage. The data type identifier is DT_FLOAT, and the C/C++ data type to be used for such values is “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, for which the data type identifier is DT_FLOAT, and the C/C++ data type to be used for such values is “float.”

CHAR(<n>) – 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. The data type identifier is DT_FIXCHAR, and the C/C++ data type to be used for such values is “char *.”

VARCHAR(<n>) – 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. The data type identifier is DT_VARCHAR, and the C/C++ data type to be used for such values is “char *.”

LONG VARCHAR(<n>) or CLOB – 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). 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. The data type identifier is DT_VARCHAR, and the C/C++ data type to be used for such values is “char *.”

To use LOB data types as input parameters with an existing user-defined function, drop and re-create the function, declaring a LOB data type as an input parameter.

You need not rebuild or recompile an existing user-defined function 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.

Large object data support requires a separately licensed Sybase IQ option.

BINARY(<n>) – a fixed-length null-byte padded binary, value with a maximum possible binary length, “<n>”, of 32767. The data is not null-byte terminated. The data type identifier is DT_BINARY, and the C/C++ data type usually used for such values is “unsigned char *.”

VARBINARY(<n>) – 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. The data type identifier is DT_BINARY, and the C/C++ data type usually used for such values is “unsigned char *.”

LONG BINARY(<n>) or BLOB – a fixed-length null-byte padded binary, value with a maximum possible binary length, “<n>”, of 4GB (gigabytes). Use the LONG BINARY data type only as an input argument, not as a return-value data type. The data is not null-byte terminated. The data type identifier is DT_BINARY, and the C/C++ data type usually used for such values is “unsigned char *.”

To use LOB data types as input parameters with an existing user-defined function, drop and re-create the function, declaring a LOB data type as an input parameter.

You need not rebuild or recompile an existing user-defined function 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.

Large object data support requires a separately licensed Sybase IQ option.

DATE – 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;

TIME – a value that precisely describes a moment within a given day. The value is passed to the UDF as an UNSIGNED BIGINT. 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 – a calendar date and time value, which is passed to or from a UDF as an UNSIGNED BIGINT. 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.