The SQL descriptor area (SQLDA)

Part V. The SQL Anywhere Programming Interfaces

Chapter 34. The Embedded SQL Interface
The SQL descriptor area (SQLDA)

The SQLDA (SQL Descriptor Area) is an interface structure that is used for dynamic SQL statements. The structure passes information to and from the database regarding host variables and select statement results. The SQLDA is defined in the header file SQLDA.H.

There are functions in the database interface library or DLL that you can use to manage SQLDAs (see "SQLDA management functions").

When host variables are used with static SQL statements, the preprocessor actually constructs an SQLDA for those host variables. It is this SQLDA that is actually passed to and from the database engine.

Structure packing option
All Embedded SQL programs must be compiled with the structure packing option of the compiler set to one-byte alignment (usually the default).

     #define SQL_MAX_NAME_LEN 30
     typedef short int a_sql_type;
     struct sqlname {
         short int length;
         unsigned char data[ SQL_MAX_NAME_LEN ];
     };
     struct sqlvar {
         short int sqltype;
         short int sqllen;
         void _sqldafar *sqldata;
         short int _sqldafar *sqlind;
         struct sqlname sqlname;
     };
     struct sqlda{
         unsigned char sqldaid[8];
         long int sqldabc;
         short int sqln;
         short int sqld;
         struct sqlvar sqlvar[1];
     };
     #define SCALE(sqllen) ((sqllen)/256)
     #define PRECISION(sqllen) ((sqllen)&0xff)
     #define SET_PRECISION_SCALE(sqllen,precision,scale) \
         sqllen = (scale)*256 + (precision)
     typedef struct sqlda SQLDA;
     typedef struct sqlvar SQLVAR, SQLDA_VARIABLE;
     typedef struct sqlname SQLNAME, SQLDA_NAME;
     #define SQLDASIZE(n) ( sizeof( struct sqlda ) + \
                 (n-1) * sizeof( struct sqlvar) )

The SQLDA fields have the following meanings:

Field Description

sqldaid An 8 byte character field that contains the string "SQLDA" as an identification of the SQLDA structure. This field helps in debugging when looking at memory contents.

sqldabc A long integer containing the length of the SQLDA structure (depends on the size of the array of sqlvar's --- 16 + 44*n bytes).

sqln The number of variable descriptors in the sqlvar array (i.e. how much actual space there is).

sqld The number of variable descriptors which are valid (i.e. contain information describing a host variable). This field is set by the DESCRIBE statement and sometimes by the programmer when supplying data to the database engine.

sqlvar An array of descriptors of type structsqlvar each describing a host variable.

Field	Description
sqldaid	An 8 byte character field that contains the string "SQLDA" as an identification of the SQLDA structure. This field helps in debugging when looking at memory contents.
sqldabc	A long integer containing the length of the SQLDA structure (depends on the size of the array of `sqlvar`'s --- 16 + 44*n bytes).
sqln	The number of variable descriptors in the sqlvar array (i.e. how much actual space there is).
sqld	The number of variable descriptors which are valid (i.e. contain information describing a host variable). This field is set by the DESCRIBE statement and sometimes by the programmer when supplying data to the database engine.
sqlvar	An array of descriptors of type structsqlvar each describing a host variable.

The sqlvar fields have the following meanings:

Field Description

sqltype The type of the variable described by this descriptor (see "Embedded SQL interface data types"). The low order bit indicates whether NULL values are allowed. Valid types and constant definitions can be found in the SQLDEF.H header file (see "SQLDEF.H header file").This field is filled by the DESCRIBE statement. You can set this field to any type when supplying data to the database engine or retrieving data from the database engine and any necessary type conversion will be done automatically.

sqllen The length of the variable. What the length actually means depends upon the type information and how the SQLDA is being used.For decimal types, this field is divided into two 1-byte fields. The high byte is the precision and the low byte is the scale. The precision is the total number of digits and the scale is the number of digits that appear after the decimal point.For more discussion of the length field, see "Length field values".

sqldata
A four-byte pointer to the memory occupied by this variable. The memory pointed to by the sqldata field must correspond to the sqltype and sqllen fields. See "Embedded SQL interface data types" for storage formats. For update and insert commands, this variable will not be involved in the operation if the sqldata pointer is a null pointer. For a fetch, no data is returned if the sqldata pointer is the null pointer.

sqlind A four-byte pointer to the indicator value. An indicator value is a short int. A negative indicator value indicates a NULL value. A positive indicator value indicates that this variable has been truncated by a fetch statement, and the indicator value contains the length of the data before truncation (see "Indicator variables").If the sqlind pointer is the null pointer, then there is no indicator variable pertaining to this host variable.The sqlind field is also used by the DESCRIBE statement to indicate parameter types.

sqlname A VARCHAR structure containing a length and character buffer. It is filled in on a DESCRIBE statement and is not otherwise used. This field has a different meaning for the two formats of the describe statement:On a DESCRIBE SELECT LIST command, indicator variables, if present, are filled with a flag indicating whether the select list item is updatable or not. More information on this flag can be found in the SQLDEF.H header file (see "SQLDEF.H header file").

SELECT LIST the name buffer is filled with the column heading of the corresponding item in the select list.

BIND VARIABLES the name buffer is filled with the name of the host variable that was used as a bind variable, or "?" if an unnamed parameter marker is used.

Field	Description
sqltype	The type of the variable described by this descriptor (see "Embedded SQL interface data types"). The low order bit indicates whether NULL values are allowed. Valid types and constant definitions can be found in the SQLDEF.H header file (see "SQLDEF.H header file").This field is filled by the DESCRIBE statement. You can set this field to any type when supplying data to the database engine or retrieving data from the database engine and any necessary type conversion will be done automatically.
sqllen	The length of the variable. What the length actually means depends upon the type information and how the SQLDA is being used.For decimal types, this field is divided into two 1-byte fields. The high byte is the precision and the low byte is the scale. The precision is the total number of digits and the scale is the number of digits that appear after the decimal point.For more discussion of the length field, see "Length field values".
sqldata	A four-byte pointer to the memory occupied by this variable. The memory pointed to by the sqldata field must correspond to the sqltype and sqllen fields. See "Embedded SQL interface data types" for storage formats. For update and insert commands, this variable will not be involved in the operation if the sqldata pointer is a null pointer. For a fetch, no data is returned if the sqldata pointer is the null pointer.
sqlind	A four-byte pointer to the indicator value. An indicator value is a short int. A negative indicator value indicates a NULL value. A positive indicator value indicates that this variable has been truncated by a fetch statement, and the indicator value contains the length of the data before truncation (see "Indicator variables").If the sqlind pointer is the null pointer, then there is no indicator variable pertaining to this host variable.The sqlind field is also used by the DESCRIBE statement to indicate parameter types.
sqlname	A VARCHAR structure containing a length and character buffer. It is filled in on a DESCRIBE statement and is not otherwise used. This field has a different meaning for the two formats of the describe statement:On a DESCRIBE SELECT LIST command, indicator variables, if present, are filled with a flag indicating whether the select list item is updatable or not. More information on this flag can be found in the SQLDEF.H header file (see "SQLDEF.H header file"). SELECT LIST the name buffer is filled with the column heading of the corresponding item in the select list. BIND VARIABLES the name buffer is filled with the name of the host variable that was used as a bind variable, or "?" if an unnamed parameter marker is used.

Length field values

The sqllen field length of the SQLVAR struct in an SQLDA is used in 3 different kinds of interactions with the database engine. The following tables detail each of these interactions. These tables list the interface constant types (the DT_ types) found in the SQLDEF.H header file. These constants would be placed in the SQLDA sqltype field. The types are described in "Embedded SQL interface data types".

Note that in static SQL, an SQLDA is still used but it is generated and completely filled in by the SQL preprocessor. In this static case, the table gives the correspondence between the static C language host variable types and the interface constants.

DESCRIBE

The following table indicates the values of the sqllen and sqltype structure members returned by the describe command for the various database types (both select list and bind variable describe statements). In the case of a user-defined database data type, the base type is described.

Your program can use the types and lengths returned from a DESCRIBE, or you may use another type. The database engine will perform type conversions between any two types. The memory pointed to by the sqldata field must correspond to the sqltype and sqllen fields.

Database field type Embedded SQL type returned Length returned on describe

CHAR(n) DT_FIXCHAR n

VARCHAR(n) DT_VARCHAR n

BINARY(n) DT_BINARY n

SMALLINT DT_SMALLINT 2

INT DT_INT 4

TINYINT DT_TINYINT 1

DECIMAL(p,s) DT_DECIMAL high byte of length field in SQLDA set to p and low byte set to s

REAL DT_FLOAT 4

FLOAT DT_FLOAT 4

DOUBLE DT_DOUBLE 8

DATE DT_DATE length of longest formatted string

TIME DT_TIME length of longest formatted string

TIMESTAMP DT_TIMESTAMP length of longest formatted string

LONG VARCHAR DT_VARCHAR 32767

LONG BINARY DT_BINARY 32767

Database field type	Embedded SQL type returned	Length returned on describe
CHAR(n)	DT_FIXCHAR	n
VARCHAR(n)	DT_VARCHAR	n
BINARY(n)	DT_BINARY	n
SMALLINT	DT_SMALLINT	2
INT	DT_INT	4
TINYINT	DT_TINYINT	1
DECIMAL(p,s)	DT_DECIMAL	high byte of length field in SQLDA set to p and low byte set to s
REAL	DT_FLOAT	4
FLOAT	DT_FLOAT	4
DOUBLE	DT_DOUBLE	8
DATE	DT_DATE	length of longest formatted string
TIME	DT_TIME	length of longest formatted string
TIMESTAMP	DT_TIMESTAMP	length of longest formatted string
LONG VARCHAR	DT_VARCHAR	32767
LONG BINARY	DT_BINARY	32767

Supplying a value

The following table indicates how lengths of values are specified when supplying data to the database engine in the SQLDA.

Only the data types shown in the table are allowed in this case. Note that the DT_DATE, DT_TIME and DT_TIMESTAMP types are treated the same as DT_STRING when supplying information to the database; the value must be a NULL-terminated character string in an appropriate date format.

Embedded SQL Data Type What program must do to set length when supplying data to the database

DT_STRING length determined by terminating '\0'

DT_VARCHAR(n) length taken from field in VARCHAR structure

DT_FIXCHAR(n) length field in SQLDA determines length of string

DT_BINARY(n) length taken from field in BINARY structure

DT_SMALLINT -

DT_INT -

DT_DECIMAL(p,s) high byte of length field in SQLDA set to p and low byte set to s

DT_FLOAT -

DT_DOUBLE -

DT_DATE length determined by terminating '\0'

DT_TIME length determined by terminating '\0'

DT_TIMESTAMP length determined by terminating '\0'

DT_TIMESTAMP_STRUCT -

DT_VARIABLE length determined by terminating '\0'

Embedded SQL Data Type	What program must do to set length when supplying data to the database
DT_STRING	length determined by terminating '\0'
DT_VARCHAR(n)	length taken from field in VARCHAR structure
DT_FIXCHAR(n)	length field in SQLDA determines length of string
DT_BINARY(n)	length taken from field in BINARY structure
DT_SMALLINT	-
DT_INT	-
DT_DECIMAL(p,s)	high byte of length field in SQLDA set to p and low byte set to s
DT_FLOAT	-
DT_DOUBLE	-
DT_DATE	length determined by terminating '\0'
DT_TIME	length determined by terminating '\0'
DT_TIMESTAMP	length determined by terminating '\0'
DT_TIMESTAMP_STRUCT	-
DT_VARIABLE	length determined by terminating '\0'

Retrieving a value

The following table indicates the values of the length field when retrieving data from the database using an SQLDA. The sqllen field is never modified when retrieving data.

Only the interface data types shown in the table are allowed in this case. Note again that the DT_DATE, DT_TIME and DT_TIMESTAMP data types are treated the same as DT_STRING when retrieving information from the database; the value will be formatted as a character string in the current date format.

Embedded SQL Data Type What program must set length field (sqllen) to when receiving How database returns length information results from the database after fetching a value

DT_STRING length of buffer '\0' at end of string

DT_VARCHAR(n) maximum length of VARCHAR structure (n+2) len field of VARCHAR structure set to actual length

DT_FIXCHAR(n) length of buffer padded with blanks to length of buffer

DT_BINARY(n) maximum length of BINARY structure (n+2) len field of BINARY structure set to actual length

DT_SMALLINT - -

DT_INT - -

DT_DECIMAL(p,s) high byte set to p and low byte set to s -

DT_FLOAT - -

DT_DOUBLE - -

DT_DATE length of buffer '\0' at end of string

DT_TIME length of buffer '\0' at end of string

DT_TIMESTAMP length of buffer '\0' at end of string

DT_TIMESTAMP_STRUCT - -

Embedded SQL Data Type	What program must set length field (sqllen) to when receiving	How database returns length information results from the database after fetching a value
DT_STRING	length of buffer	'\0' at end of string
DT_VARCHAR(n)	maximum length of VARCHAR structure (n+2)	len field of VARCHAR structure set to actual length
DT_FIXCHAR(n)	length of buffer	padded with blanks to length of buffer
DT_BINARY(n)	maximum length of BINARY structure (n+2)	len field of BINARY structure set to actual length
DT_SMALLINT	-	-
DT_INT	-	-
DT_DECIMAL(p,s)	high byte set to p and low byte set to s	-
DT_FLOAT	-	-
DT_DOUBLE	-	-
DT_DATE	length of buffer	'\0' at end of string
DT_TIME	length of buffer	'\0' at end of string
DT_TIMESTAMP	length of buffer	'\0' at end of string
DT_TIMESTAMP_STRUCT	-	-