Progress
Embedded SQL-92
Guide and Reference


Using Indicator Variables with OUTPUT Host Variables

To use indicator variables to determine if a returned value is a NULL value, you associate an indicator variable with an output host variable. For example, you can use an indicator variable in the INTO clause of a SELECT statement.

These are the general steps for using indicator variables with OUTPUT host variables:

  1. DECLARE host variables and an indicator variable in the DECLARE SECTION.
  2. Execute the embedded SQL SELECT statement with an INTO clause using an OUTPUT host variable and its associated indicator variable.
  3. Examine the indicator variable email_i to determine if the fetched value is NULL.
  4. Report the result of the fetch operation.

Table 3–3 describes how you might use indicator variables and INPUT and OUTPUT host variables in the INTO clause of a SELECT statement.

Table 3–3: Host Variables in an OUTPUT Example 
Variable Name
Type of Variable
Use in Example
last_name_v
INPUT host variable
C processing determines a value. The SELECT statement WHERE clause uses the value to match last_name column.
email_addr_v
OUTPUT host variable
Target variable for SELECT INTO (OUTPUT) operation. Associated with indicator variable email_i.
email_i
indicator variable
Associated with host variable email_addr_v. C processing evaluates indicator variable for output value, NULL, or truncation.

EXAMPLE

The code fragment in the following example shows how to declare and use indicator variables with the INTO clause of a SELECT statement:

/*
**  1. Define host and indicator variables in the DECLARE SECTION
*/
EXEC SQL BEGIN DECLARE SECTION ;
     char last_name_v[20]; 
     char email_addr_v[20] ;
     long email_i ;
EXEC SQL END DECLARE SECTION ;
/*
**  C Language statements here, to set a value for the INOUT host 
**  variable last_name_v. Typically, accept a string from a user 
**  interface.
*/
/*
**  2. Execute the SELECT statement with an INTO clause.
*/
EXEC SQL
     SELECT email_addr
     INTO :email_addr_v INDICATOR:email_i
     FROM PERSONNEL
     WHERE last_name = :last_name_v ;
/*
**  Evaluate the SQLCODE and take appropriate program action.
*/
if (SQLCA.SQLCODE == 0)
/*
**  Successful SELECT   3. Evaluate the indicator variable
**                      4. Take appropriate program action
*/
{
     if (email_i == 0)
          printf ("Email address for %s is: %s\n",last_name_v,
                  email_addr_v) ;
     elseif (email_i == -1)
          printf ("No email address on record for %s\n", last_name_v) ;
     else          printf ("Email address for %s too long to process\n",
                   last_name_v) ;
}
else
/*
** SQL_NOT_FOUND and Error Handling Routines here
*/ 


Copyright © 2004 Progress Software Corporation
www.progress.com
Voice: (781) 280-4000
Fax: (781) 280-4095