Programming Interfaces Guide
   CHAPTER 2. The Embedded SQL Interface     

Using host variables

Host variables are C variables that are identified to the SQL preprocessor. Host variables can be used to send values to the database server or receive values from the database server.

Host variables are quite easy to use, but they have some restrictions. Dynamic SQL is a more general way of passing information to and from the database server using a structure known as the SQL Descriptor Area (SQLDA). Dynamic SQL is discussed in Static and dynamic SQL.

  Declaring host variables

Host variables are defined by putting them into a declaration section. According to the IBM SAA and ANSI Embedded SQL standards, host variables are defined by surrounding the normal C variable declarations with the following:

EXEC SQL BEGIN DECLARE SECTION;
/* C variable declarations */
EXEC SQL END DECLARE SECTION;

These host variables can then be used in place of value constants in any SQL statement. When the database server executes the command, the value of the host variable is used. Note that host variables cannot be used in place of table or column names; dynamic SQL is required for this. The variable name is prefixed with a colon (:) in a SQL statement to distinguish it from other identifiers allowed in the statement.

A standard SQL preprocessor does not scan C language code except inside a DECLARE SECTION. Thus, TYPEDEF types and structures are not allowed. Initializers on the variables are allowed inside a DECLARE SECTION.

Example 

• The following sample code illustrates the use of host variables on an INSERT command. The variables are filled in by the program and then inserted into the database:

  EXEC SQL BEGIN DECLARE SECTION;
  long employee_number;
  char employee_name[50];
  char employee_initials[8];
  char employee_phone[15];
  EXEC SQL END DECLARE SECTION;
  /* program fills in variables with appropriate values
  */
  EXEC SQL INSERT INTO Employee
  VALUES (:employee_number, :employee_name,
  :employee_initials, :employee_phone );

       For a more extensive example, see Static cursor example.

  C host variable types

Only a limited number of C data types are supported as host variables. Also, certain host variable types do not have a corresponding C type.

There are macros defined in the sqlca.h header file that can be used to declare a host variable of these types: VARCHAR, FIXCHAR, BINARY, PACKED DECIMAL, or SQLDATETIME structure. They are used as follows:

EXEC SQL BEGIN DECLARE SECTION;
DECL_VARCHAR( 10 ) v_varchar;
DECL_FIXCHAR( 10 ) v_fixchar;
DECL_BINARY( 4000 ) v_binary;
DECL_DECIMAL( 10, 2 ) v_packed_decimal;
DECL_DATETIME v_datetime;
EXEC SQL END DECLARE SECTION;

The preprocessor recognizes these macros within a declaration section and will treat the variable as the appropriate type.

The following table lists the C variable types that are allowed for host variables, and their corresponding Embedded SQL interface data types.

C Data Type	Embedded SQL Interface Type	Description
short i; short int i; unsigned short int i;	DT_SMALLINT	16 bit, signed integer
long l; long int l; unsigned long int l;	DT_INT	32 bit, signed integer
float f;	DT_FLOAT	4 byte floating point
double d;	DT_DOUBLE	8 byte floating point
DECL_DECIMAL(p,s)	DT_DECIMAL(p,s)	Packed decimal
char a; /*n=1*/ DECL_FIXCHAR(n) a; DECL_FIXCHAR a[n];	DT_FIXCHAR(n)	Fixed length character string blank padded
char a[n]; /*n>=1*/	DT_STRING(n)	NULL-terminated blank-padded string
char *a;	DT_STRING(32767)	NULL-terminated string
DECL_VARCHAR(n) a;	DT_VARCHAR(n)	Varying length character string with 2 byte length field. Not blank padded.
DECL_BINARY(n) a;	DT_BINARY(n)	Varying length binary data with 2 byte length field
DECL_DATETIME a;	DT_TIMESTAMP_STRUCT	SQLDATETIME structure

Pointers to char 

A host variable declared as a pointer to char (char *a) is considered by the database interface to be 32,767 bytes long. Any host variable of type pointer to char used to retrieve information from the database must point to a buffer large enough to hold any value that could possibly come back from the database.

This is potentially quite dangerous, because somebody could change the definition of the column in the database to be larger than it was when the program was written. This could cause random memory corruption problems. If you are using a 16-bit compiler, requiring 32,767 bytes could make the program stack overflow. It is better to use a declared array, even as a parameter to a function where it is passed as a pointer to char. This lets the PREPARE statements know the size of the array.

  Host variable usage

Host variables can be used in the following circumstances:

• SELECT, INSERT, UPDATE and DELETE statements in any place where a number or string constant is allowed.

• The INTO clause of SELECT and FETCH statements.

• Host variables can also be used in place of a statement name, a cursor name, or an option name in commands specific to Embedded SQL.

• For CONNECT, DISCONNECT, and SET CONNECT, a host variable can be used in place of a user ID, password, connection name, or database environment name.

• For SET OPTION and GET OPTION, a host variable can be used in place of a user ID, option name or option value.

• Host variables cannot be used in place of a table name or a column name in any statement.

Examples 

• The following is a valid program:

  INCLUDE SQLCA; 
  long SQLCODE;
  sub1() {
     char SQLSTATE[6];
     exec sql CREATE TABLE ...
  }

• The following is an invalid program:

  INCLUDE SQLCA;
  sub1() {
     char SQLSTATE[6];
     exec sql CREATE TABLE...
  }
  sub2() {
     exec sql DROP TABLE...
     // No SQLSTATE in scope of this statement
  }

• The case of SQLSTATE and SQLCODE is important, and the ISO/ANSI standard requires that their definitions be exactly as follows:

  long SQLCODE;
  char SQLSTATE[6];

  Indicator variables

Indicator variables are C variables that hold supplementary information when you are fetching or putting data. There are several distinct uses for indicator variables:

• NULL values     To enable applications to handle NULL values.

• String truncation     To enable applications to handle cases when fetched values must be truncated to fit into host variables.

• Conversion errors     To hold error information.

An indicator variable is a host variable of type short int that is placed immediately following a regular host variable in a SQL statement. For example, in the following INSERT statement, :ind_phone is an indicator variable:

EXEC SQL INSERT INTO Employee
   VALUES (:employee_number, :employee_name,
   :employee_initials, :employee_phone:ind_phone );

  Using indicator variables to handle NULL

In SQL data, NULL represents either an unknown attribute or inapplicable information. The SQL concept of NULL is not to be confused with the C language constant by the same name (NULL). The C constant is used to represent a non-initialized or invalid pointer.

When NULL is used in the Adaptive Server Anywhere documentation, it refers to the SQL database meaning given above. The C language constant is referred to as the null pointer (lower case).

NULL is not the same as any value of the column's defined type. Thus, in order to pass NULL values to the database or receive NULL results back, something extra is required beyond regular host variables. Indicator variables are used for this purpose.

Using indicator variables when inserting NULL 

An INSERT statement could include an indicator variable as follows:

EXEC SQL BEGIN DECLARE SECTION;
short int employee_number;
char employee_name[50];
char employee_initials[6];
char employee_phone[15];
short int ind_phone;
EXEC SQL END DECLARE SECTION;

/*
program fills in empnum, empname,
initials and homephone
*/
if( /* phone number is unknown */ ) {
   ind_phone = -1;
} else {
   ind_phone = 0;
}
EXEC SQL INSERT INTO Employee
   VALUES (:employee_number, :employee_name,
   :employee_initials, :employee_phone:ind_phone );

If the indicator variable has a value of -1, a NULL is written. If it has a value of 0, the actual value of employee_phone is written.

Using indicator variables when fetching NULL 

Indicator variables are also used when receiving data from the database. They are used to indicate that a NULL value was fetched (indicator is negative). If a NULL value is fetched from the database and an indicator variable is not supplied, an error is generated (SQLE_NO_INDICATOR). Errors are explained in the next section.

  Using indicator variables for truncated values

Indicator variables indicate whether any fetched values were truncated to fit into a host variable. This enables applications to handle truncation appropriately.

If a value is truncated on fetching, the indicator variable is set to a positive value, containing the actual length of the database value before truncation. If the length of the value is greater than 32,767, then the indicator variable contains 32,767.

  Using indicator values for conversion errors

By default, the CONVERSION_ERROR database option is set to ON, and any data type conversion failure leads to an error, with no row returned.

You can use indicator variables to tell which column produced a data type conversion failure. If you set the database option CONVERSION_ERROR to OFF, any data type conversion failure gives a warning, rather than an error. If the column that suffered the conversion error has an indicator variable, that variable is set to a value of -2.

If you set the CONVERSION_ERROR option to OFF when inserting data into the database, a value of NULL is inserted when a conversion failure occurs.

  Summary of indicator variable values

The following table provides a summary of indicator variable usage.

Indicator Value	Supplying Value to database	Receiving value from database
> 0	Host variable value	Retrieved value was truncated — actual length in indicator variable
0	Host variable value	Fetch successful, or CONVERSION_ERROR set to ON.
-1	NULL value	NULL result
-2	NULL value	Conversion error (when CONVERSION_ERROR is set to OFF only). SQLCODE will indicate a conversion error.
< -2	NULL value	NULL result

     For more information on retrieving long values, see GET DATA statement.