Sqlvar Elements

After describing a statement, the program must analyze the contents of the sqlvar array. Each element of the sqlvar array describes one result column of the SELECT statement. Together, all the sqlvar elements describe one complete row of the result table.

The DESCRIBE statement sets the data type, length, and name of the result column (sqltype, sqllen and sqlname), and the program must use that information to supply the address of the result variable and result indicator variable (sqldata and sqlind). Your program must also allocate the space for these variables.

exec sql create table object
     (o_id            integer not null,
      o_desc          char(100) not null,
      o_price         money not null,
      o_sold          date);

sqld	4 (columns)
sqlvar(1)	sqltype	=	30 (integer)
	sqllen	=	4
	sqlname	=	'o_id'
sqlvar(2)	sqltype	=	20 (char)
	sqllen	=	100
	sqlname	=	'o_desc'
sqlvar(3)	sqltype	=	5 (money)
	sqllen	=	0
	sqlname	=	'o_price'
sqlvar(4)	sqltype	=	-3 (nullable date)
	sqllen	=	0
	sqlname	=	'o_sold'

Data Type	Contents of sqllen
char and varchar	Maximum length of the character string.
byte and byte varying	Maximum length of the binary data.
long varchar and long byte	Length of the string. If the length exceeds the maximum value of a 2-byte integer, sqllen is set to 0. Long varchar and long byte columns can contain up to 2 GB of data. To avoid buffer overflow, be sure to allocate a host language variable that is large enough to accommodate your data.
integer and float	Declared size of the numeric field.
date	0 (the program must use a 25-byte character string to retrieve or set date data).
money	0 (the program must use an 8-byte floating point variable to retrieve or set money data).
decimal	High byte contains precision, low byte contains scale.

After the statement is described, your program must analyze the values of sqltype and sqllen in each sqlvar element. If sqltype and sqllen do not correspond exactly with the types of variables used by the program to process the SELECT statement, modify sqltype and sqllen to be consistent with the program variables. After describing a SELECT statement, there is one sqlvar element for each expression in the select target list.

After processing the values of sqltype and sqllen, allocate storage for the variables that contain the values in the result columns of the SELECT statement by pointing sqldata at a host language variable that contain the result data. If the value of sqltype is negative, which indicates a nullable result column data type, allocate an indicator variable for the particular result column and set sqlind to point to the indicator variable. If sqltype is positive, indicating that the result column data type is not nullable, an indicator variable is not required. In this case, set sqlind to zero.

To omit the null indicator for a nullable result column (sqltype is negative if the column is nullable), set sqltype to its positive value and sqlind to zero. Conversely, if sqltype is positive and an indicator variable is allocated, set sqltype to its negative value, and set sqlind to point to the indicator variable.

In the preceding example, the program analyzes the results and modifies some of the types and lengths to correspond with the host language variables used by the program: the money data type is changed to float, and the date type to char. In addition, sqlind and sqldata are set to appropriate values. The values in the resulting sqlvar elements are:

sqlvar(1)	sqltype	=	30 (integer not nullable)
	sqllen	=	4
	sqldata	=	Address of 4-byte integer
	sqlind	=	0
	sqlname	=	'o_id'
sqlvar(2)	sqltype	=	20 (char not nullable)
	sqllen	=	100
	sqldata	=	Address of 100-byte character string
	sqlind	=	0
	sqlname	=	'o_desc'
sqlvar(3)	sqltype	=	31 (float not nullable, was money)
	sqllen	=	8 (was 0)
	sqldata	=	Address of 8-byte floating point
	sqlind	=	0
	sqlname	=	'o_price'
sqlvar(4)	sqltype	=	-20 (char nullable, was date)
	sqllen	=	25 (was 0)
	sqldata	=	Address of 25-byte character string
	sqlind	=	Address of 2-byte indicator variable
	sqlname	=	'o_sold'