XSQLVAR Field Descriptions

The following table describes the fields of the XSQLVAR structure:

Table 6.2 XSQLVAR Field Descriptions

Field Definition	Description
short sqltype	Indicates the SQL datatype of parameters or select-list items; set by InterBase during isc_dsql_describe(), isc_dsql_describe_bind(), or isc_dsql_prepare()
short sqlscale	Indicates the SQL datatype of parameters or select-list items; set by InterBase during isc_dsql_describe(), isc_dsql_describe_bind(), or isc_dsql_prepare()
short sqlprecision	Provides precision for the exact numeric data types (DECIMAL, NUMERIC); Set by InterBase during isc_dsql_describe(), isc_dsql_describe_bind(), or isc_dsql_prepare()
short short sqlsubtype	Specifies the subtype for Blob data; set by InterBase during isc_dsql_describe(), isc_dsql_describe_bind(), or isc_dsql_prepare()
short sqllen	Indicates the maximum size, in bytes, of data in the sqldata field; set by InterBase during isc_dsql_describe(), isc_dsql_describe_bind(), or isc_dsql_prepare()
char *sqldata	For input descriptors, specifies either the address of a select-list item or a parameter; set by the application. For output descriptors, contains a value for a select-list item; set by InterBase.
short *sqlind	On input, specifies the address of an indicator variable; set by an application; on output, specifies the address of column indicator value for a selectlist item following a FETCH A value of 0 indicates that the column is not NULL; a value of –1 indicates the column is NULL; set by InterBase
short sqlname_length	Specifies the length, in bytes, of the data in field, sqlname; set by InterBase during isc_dsql_prepare() or isc_dsql_describe()
char sqlname[METADATALENGTH]	Contains the name of the column. Not NULL (\0) terminated; set by InterBase during isc_dsql_prepare() or isc_dsql_describe()
short relname_length	Specifies the length, in bytes, of the data in field, relname; set by InterBase during isc_dsql_prepare() or isc_dsql_describe()
char relname[METADATALENGTH	Contains the name of the table; not NULL (\0) terminated, set by InterBase during isc_dsql_prepare() or isc_dsql_describe()
short ownname_length	Specifies the length, in bytes, of the data in field, ownname; set by InterBase during isc_dsql_prepare() or isc_dsql_describe()
char ownname[METADATALENGTH]	Contains the name of the table owner; not NULL (\0) terminated, set by InterBase during isc_dsql_prepare() or isc_dsql_describe()
short aliasname_length	Specifies the length, in bytes, of the data in field, aliasname; set by InterBase during isc_dsql_prepare() or isc_dsql_describe()
char aliasname [METADATALENGTH]	Contains the alias name of the column. If no alias exists, contains the column name; not NULL (\0) terminated, set by InterBase during isc_dsql_prepare() or isc_dsql_describe()

Input Descriptors

Input descriptors are used to process SQL statement strings that contain parameters. Before an application can execute a statement with parameters, it must supply values for them. The application indicates the number of parameters passed in the XSQLDA sqld field, then describes each parameter in a separate XSQLVAR structure. For example, the following statement string contains two parameters, so an application must set sqld to 2, and describe each parameter:

char *str = "UPDATE DEPARTMENT SET BUDGET = ? WHERE LOCATION =?";

When the statement is executed, the first XSQLVAR supplies information about the BUDGET value, and the second XSQLVAR supplies the LOCATION value.

For more information about using input descriptors, see DSQL Programming Methods.

Output Descriptors

Output descriptors return values from an executed query to an application. The sqld field of the XSQLDA indicates how many values were returned. Each value is stored in a separate XSQLVAR structure. The XSQLDA sqlvar field points to the first of these XSQLVAR structures. The following statement string requires an output descriptor:

char *str = "SELECT * FROM CUSTOMER WHERE CUST_NO > 100";

For information about retrieving information from an output descriptor, see “DSQL Programming Methods”.

Using the XSQLDA_LENGTH Macro

The ibase.h header file defines a macro, XSQLDA_LENGTH, to calculate the number of bytes that must be allocated for an input or output XSQLDA. XSQLDA_LENGTH is defined as follows:

#define XSQLDA_LENGTH (n) (sizeof (XSQLDA) + (n – 1) * sizeof(XSQLVAR))

n is the number of parameters in a statement string, or the number of select-list items returned from a query. For example, the following C statement uses the XSQLDA_LENGTH macro to specify how much memory to allocate for an XSQLDA with 5 parameters or return items:

XSQLDA *my_xsqlda;

. . .

my_xsqlda = (XSQLDA *)malloc(XSQLDA_LENGTH(5));

. . .

For more information about using the XSQLDA_LENGTH macro, see “DSQL Programming Methods”.

SQL Datatype Macro Constants

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

Table 6.3 SQL data types, macro expressions, and C data types

SQL datatype	Macro expression	C datatype 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, or ISC_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	SC_TIMESTAMP	No
TIMESTAMP	SQL_TIMESTAMP + 1	SC_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 datatypes are stored internally as SMALLINT, INTEGER, DOUBLE PRECISION, or 64-bit integer datatypes. 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 datatype 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 datatype 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.

Handling Varying String Datatypes

VARCHAR, CHARACTER VARYING, and NCHAR VARYING datatypes require careful handling in DSQL. The first two bytes of these datatypes contain string length information, while the remainder of the data contains the actual bytes of string data to process.

To avoid having to write code to extract and process variable-length strings in an application, it is possible to force these data types to fixed length using SQL macro expressions. For more information about forcing variable-length data to fixed length for processing, see “Coercing Datatypes” on page 6-16.

Applications can, instead, detect and process variable-length data directly. To do so, they must extract the first two bytes from the string to determine the byte-length of the string itself, then read the string, byte-by-byte, into a null-terminated buffer.

Handling NUMERIC and DECIMAL Datatypes

DECIMAL and NUMERIC datatypes are stored internally as SMALLINT, INTEGER, DOUBLE PRECISION, or 64-bit integer datatypes, depending on the precision and scale defined for a column definition that uses these types. To determine how a DECIMAL or NUMERIC value is actually stored in the database, use isql to examine the column definition in the table. If NUMERIC is reported, then data is actually being stored as DOUBLE PRECISION.

When a DECIMAL or NUMERIC value is stored as a SMALLINT, INTEGER, or 64-bit integer, the value is stored as a whole number. During retrieval in DSQL, the sqlscale field of the XSQLVAR is set to a negative number that indicates the factor of 10 by which the whole number (returned in sqldata), must be divided in order to produce the correct NUMERIC or DECIMAL value with its fractional part. If sqlscale is –1, then the number must be divided by 10, if it is –2, then the number must be divided by 100, –3 by 1000, and so forth.

Coercing Datatypes

Sometimes when processing DSQL input parameters and select-list items, it is desirable or necessary to translate one datatype to another. This process is referred to as datatype coercion. For example, datatype coercion is often used when parameters or select-list items are of type VARCHAR. The first two bytes of VARCHAR data contain string length information, while the remainder of the data is the string to process. By coercing the data from SQL_VARYING to SQL_TEXT, data processing can be simplified.

Coercion can only be from one compatible datatype to another. For example, SQL_VARYING to SQL_TEXT, or SQL_SHORT to SQL_LONG.

Coercing Character Datatypes

To coerce SQL_VARYING datatypes to SQL_TEXT datatypes, change the sqltype field in the XSQLVAR structure of the parameter or select-list item to the desired SQL macro datatype constant. For example, the following statement assumes that var is a pointer to an XSQLVAR structure, a nd that it contains a SQL_VARYING datatype to convert to SQL_TEXT:

var->sqltype = SQL_TEXT;

After coercing a character datatype, provide proper storage space for it. The XSQLVAR field, sqllen, contains information about the size of the uncoerced data. Set the XSQLVAR sqldata field to the address of the data.

Coercing Numeric Datatypes

To coerce one numeric datatype to another, change the sqltype field in the parameter’s or select-list item’s XSQLVAR structure to the desired SQL macro datatype constant. For example, the following statement assumes that var is a pointer to an XSQLVAR structure, and that it contains a SQL_SHORT datatype to convert to SQL_LONG:

var->sqltype = SQL_LONG;

Important: Do not coerce a larger datatype to a smaller one. Data can be lost in such a translation.

Setting a NULL Indicator

If a parameter or select-list item contains a NULL value, the sqlind field should be used to indicate its NULL status. Appropriate storage space must be allocated for sqlind before values can be stored there.

Before insertion, set sqlind to –1 to indicate that NULL values are legal. Otherwise, set sqlind to 0.

After selection, a sqlind of –1 indicates a field contains a NULL value. Other values indicate a field contains non-NULL data.

Aligning Numerical Data

Ordinarily, when a variable with a numeric datatype is created, the compiler will ensure that the variable is stored at a properly aligned address, but when numeric data is stored in a dynamically allocated buffer space, such as can be pointed to by the XSQLDA and XSQLVAR structures, the programmer must take precautions to ensure that the storage space is properly aligned. Certain platforms, in particular those with RISC processors, require that numerical data in dynamically allocated storage structures be aligned properly in memory. Alignment is dependent both on datatype and platform.

For example, a short integer on a Sun SPARCstation must be located at an address divisible by 2, while a long on the same platform must be located at an address divisible by 4. In most cases, a data item is properly aligned if the address of its starting byte is divisible by the correct alignment number. Consult specific system and compiler documentation for alignment requirements.

A useful rule of thumb is that the size of a datatype is always a valid alignment number for the datatype. For a given type T, if size of (T) equals n, then addresses divisible by n are correctly aligned for T. The following macro expression can be used to align data:

#define ALIGN(ptr, n) ((ptr + n - 1) & ~(n - 1))

where ptr is a pointer to char.

The following code illustrates how the ALIGN macro might be used:

char *buffer_pointer, *next_aligned;

next_aligned = ALIGN(buffer_pointer, sizeof(T));

Advance to Next Section

• DSQL Programming Methods