SQL Data Type Macro Constants

Go Up to Understanding the XSQLDA

InterBase defines a set of macro constants to represent SQL data types and NULL status information in an XSQLVAR. An application should use these macro constants to specify the data type of parameters and to determine the data types of select-list items in a SQL statement. The following table lists each SQL data type, its corresponding macro constant expression, C data type or InterBase typedef, and whether or not the sqlind field is used to indicate a parameter or variable that contains NULL or unknown data:

SQL Data Types, Macro Expressions, and C Data Types

SQL data type	Macro expression	C data type or typedef	sqlind used?
Array	SQL_ARRAY	ISC_QUAD	No
Array	SQL_ARRAY + 1	ISC_QUAD	Yes
Blob	SQL_BLOB	ISC_QUAD	No
Blob	SQL_BLOB + 1	ISC_QUAD	Yes
BOOLEAN	SQL_BOOLEAN	signed short	No
BOOLEAN	SQL_BOOLEAN + 1	signed short	Yes
CHAR	SQL_TEXT	char[]	No
CHAR	SQL_TEXT + 1	char[]	Yes
DATE	SQL_TYPE_DATE	ISC_DATE	No
DATE	SQL_TYPE_DATE + 1	ISC_DATE	Yes
DECIMAL	SQL_SHORT SQL_LONG SQL_DOUBLE or SQL_INT64	int, long, double, or ISC_INT64	No
DECIMAL	SQL_SHORT + 1 SQL_LONG + 1 SQL_DOUBLE + 1 or SQL_INT64 + 1	int, long, double, or ISC_INT64	Yes
DOUBLE PRECISION	SQL_DOUBLE	double	No
DOUBLE PRECISION	SQL_DOUBLE + 1	double	Yes
INTEGER	SQL_LONG	long	No
INTEGER	SQL_LONG + 1	ISC_LONG	Yes
FLOAT	SQL_FLOAT	float	No
FLOAT	SQL_FLOAT + 1	float	Yes
NUMERIC	SQL_SHORT SQL_LONG SQL_DOUBLE or SQL_INT64	int, long, double, orISC_INT64	No
NUMERIC	SQL_SHORT + 1 SQL_LONG + 1 SQL_DOUBLE + 1 or SQL_INT64 + 1	int, long, double, or ISC_INT64	Yes
SMALLINT	SQL_SHORT	short	No
SMALLINT	SQL_SHORT + 1	short	Yes
TIME	SQL_TYPE_TIME	ISC_TIME	No
TIME	SQL_TYPE_TIME + 1	ISC_TIME	Yes
TIMESTAMP	SQL_TIMESTAMP	ISC_TIMESTAMP	No
TIMESTAMP	SQL_TIMESTAMP + 1	ISC_TIMESTAMP	Yes
VARCHAR	SQL_VARYING	First 2 bytes: short containing the length of the character string; remaining bytes: char[]	No
VARCHAR	SQL_VARYING + 1	First 2 bytes: short containing the length of the character string; remaining bytes: char[]	Yes

DECIMAL and NUMERIC data types are stored internally as SMALLINT, INTEGER, DOUBLE PRECISION, or 64-bit integer data types. To specify the correct macro expression to provide for a DECIMAL or NUMERIC column, use isql to examine the column definition in the table to see how InterBase is storing column data, then choose a corresponding macro expression.

The data type information for a parameter or select-list item is contained in the sqltype field of the XSQLVAR structure. The value contained in sqltype provides two pieces of information:

• The data type of the parameter or select-list item.
• Whether sqlind is used to indicate NULL values. If sqlind is used, its value specifies whether the parameter or select-list item is NULL (–1), or not NULL (0).

For example, if sqltype equals SQL_TEXT, the parameter or select-list item is a CHAR that does not use sqlind to check for a NULL value (because, in theory, NULL values are not allowed for it). If sqltype equals SQL_TEXT + 1, then sqlind can be checked to see if the parameter or select-list item is NULL.

Tip:
The C language expression, sqltype & 1, provides a useful test of whether a parameter or select-list item can contain a NULL. The expression evaluates to 0 if the parameter or select-list item cannot contain a NULL, and 1 if the parameter or select-list item can contain a NULL. The following code fragment demonstrates how to use the expression:

if (sqltype & 1 == 0) {
/* parameter or select-list item that CANNOT contain a NULL */
}
else {
/* parameter or select-list item CAN contain a NULL */
}

By default, both isc_dsql_prepare() and isc_dsql_describe() return a macro expression of type + 1, so sqlind should always be examined for NULL values with these statements.

Advance To:

• Handling Varying String Data Types