Programming Interfaces Guide
   CHAPTER 2. 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 regarding host variables and SELECT statement results to and from the database. 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. For descriptions, see SQLDA management functions.

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

  SQLDA fields and their meanings

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 you are looking at memory contents.
sqldabc	A long integer containing the length of the SQLDA structure.
sqln	The number of variable descriptors in the sqlvar array.
sqld	The number of variable descriptors which are valid (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 server.
sqlvar	An array of descriptors of type struct sqlvar, each describing a host variable.

  Host variable descriptions in the SQLDA

Each sqlvar structure in the SQLDA describes a host variable. The fields of the sqlvar structure have the following meanings:

• sqltype     The type of the variable that is described by this descriptor (see Embedded SQL 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.

  This field is filled by the DESCRIBE statement. You can set this field to any type, when supplying data to the database server or retrieving data from the database server. Any necessary type conversion is 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. The scale is the number of digits that appear after the decimal point.

       For more information on the length field, see Length field values.

• sqldata     A four-byte pointer to the memory occupied by this variable. This memory must correspond to the sqltype and sqllen fields.

       For storage formats, see Embedded SQL data types.

  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 a null pointer.

  If the DESCRIBE statement uses LONG NAMES, this field holds the long name of the result set column. If, in addition, the DESCRIBE statement is a DESCRIBE USER TYPES statement, then this field holds the long name of the user-defined data type, instead of the column. If the type is a base type, the field is empty.

• sqlind     A 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.

       For more information, see Indicator variables.

  If the sqlind pointer is the null pointer, no indicator variable pertains to this host variable.

  The sqlind field is also used by the DESCRIBE statement to indicate parameter types. If the type is a user-defined data type, this field is set to DT_HAS_USERTYPE_INFO. In such a case, you may wish to carry out a DESCRIBE USER TYPES to obtain information on the user-defined data types.

• sqlname     A VARCHAR structure that contains a length and character buffer. It is filled by a DESCRIBE statement and is not otherwise used. This field has a different meaning for the two formats of the DESCRIBE statement:

  • 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.

  On a DESCRIBE SELECT LIST command, any indicator variables 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.

  If the DESCRIBE statement is a DESCRIBE USER TYPES statement, then this field holds the long name of the user-defined data type, instead of the column. If the type is a base type, the field is empty.

  Length field values

The sqllen field length of the sqlvar structure in a SQLDA is used in three different kinds of interactions with the database server. 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 data types.

In static SQL, a SQLDA is still used but it is generated and completely filled in by the SQL preprocessor. In this static case, the tables give 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 server 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

Supplying a value 

The following table indicates how you specify lengths of values when you supply data to the database server in the SQLDA.

Only the data types shown in the table are allowed in this case. 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 the program must do to set the 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	No action required
DT_INT	No action required
DT_DECIMAL(p,s)	high byte of length field in SQLDA set to p, and low byte set to s
DT_FLOAT	No action required
DT_DOUBLE	No action required
DT_DATE	length determined by terminating \0
DT_TIME	length determined by terminating \0
DT_TIMESTAMP	length determined by terminating \0
DT_TIMESTAMP_STRUCT	No action required
DT_VARIABLE	length determined by terminating \0

Retrieving a value 

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

Only the interface data types shown in the table are allowed in this case. The DT_DATE, DT_TIME and DT_TIMESTAMP data types are treated the same as DT_STRING when you retrieve information from the database. The value is formatted as a character string in the current date format.

Embedded SQL Data Type	What the program must set length field to when receiving	How the database returns length information 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	No action required	No action required
DT_INT	No action required	No action required
DT_DECIMAL(p,s)	high byte set to p and low byte set to s	No action required
DT_FLOAT	No action required	No action required
DT_DOUBLE	No action required	No action required
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	No action required	No action required