2. Host Variables and Indicator Variables#

Host Variables#

Overview#

Host variables are responsible for data exchange between an application written in a host language and a database server. In other words, host variables store data that have been read from table columns, data that are to be inserted into table columns, etc.

Declaring Host Variables#

The host variable declaration method is as follows:

If an attempt is made to use a variable in an embedded SQL statement, and the variable was not first declared in either the host variable declaration section or the function argument declaration section, an error saying "The host variable [variable_name] is unknown." will be raised during the precompile operation.
For more information about the host variable declaration section and the function argument declaration section, please refer to Chapter3: Host Variable Declaration Section.
The syntax for declaring host variables is as follows
datatype variable_name;
This is the same as when declaring variables in a C or C++ program. For detailed information about the data types that host variables can have, please refer to Chapter 5: Host Variable Data Types.
Host variables can also be declared as arrays. For the CHAR and VARCHAR types, it is possible to declare one- or two-dimensional arrays, whereas for the other types, it is only possible to declare one-dimensional arrays. For more information about using arrays with embedded SQL statements, please refer to Chapter9 : Using Arrays in Embedded SQL Statements.
APRE can use the CHAR and VARCHAR type host variables to process text data in any of the national character sets supported by Altibase. When handling data in the national character set, use the reserved word shown below:
character set [is] nchar_cs
Note that if the -nchar_var command-line option is used when precompiling the source code, it is not necessary to use the reserved word shown above.
The names of host variables must start with an alphabetic character (a ~ z, A ~ Z), the underscore character ('_'), or a dollar sign ("$"), and must not be longer than 50 bytes.

Using Host Variables in Embedded SQL Statements#

A host variable can be used anywhere in an embedded SQL statement where the use of a scalar expression would be allowed.

Host variables must be distinguished from the other elements in embedded SQL statements. This is accomplished by prepending the colon (":") character to the names of host variables whenever they appear in embedded SQL statements.

Example#

In the following example, the host variables s_dno, s_dname, and s_dep_location are declared:

< Sample Program : select.sc >

EXEC SQL BEGIN DECLARE SECTION;
short      s_dno;
char       s_dname[30+1];
char       s_dep_location[9+1];
EXEC SQL END DECLARE SECTION;

EXEC SQL SELECT DNAME, DEP_LOCATION
INTO :s_dname, :s_dep_location
FROM DEPARTMENTS
WHERE DNO = :s_dno;

Classifying Host Variables#

Host variables are classified as either input host variables or output host variables depending on whether they are used to input data into a database server or extract data from a database server

Output Host Variables#

An output host variable is used in an INTO clause of a SELECT or FETCH statement to store query results. An output host variable thus plays the same role as a variable used in the ODBC SQLBindCol() function.

Example

The following is an example of the use of output host variables.

In this example, s_dname and s_dep_location are host variables. The values in the DNAME and DEP_LOCATION columns for the records that satisfy the condition in the WHERE clause are stored in the host variables s_dname and s_dep_location, respectively.

< Sample Program : select.sc >

EXEC SQL BEGIN DECLARE SECTION;
short      s_dno;
char       s_dname[30+1];
char       s_dep_location[9+1];
EXEC SQL END DECLARE SECTION;

s_dno = 1001;
EXEC SQL SELECT DNAME, DEP_LOCATION
INTO :s_dname, :s_dep_location
FROM DEPARTMENTS
WHERE DNO = :s_dno;

Input Host Variables#

Input host variables are used wherever output host variables are not used. Their primary role is to specify data to be used in SQL statements. For example, an input host variable can be used in the WHERE clause of a SELECT statement to specify a value that is part of a condition, or in the VALUES clause of an INSERT statement to specify a value to be inserted into a particular column of a record.

An input variable can be used anywhere in an embedded SQL statement where the use of a scalar expression would be allowed. Note however that in order to use a host variable in the select list or the GROUP BY or ORDER BY clause of a SELECT statement, its type must be specified using the CAST operator in the SQL statement. If the CAST operator is used in the GROUP BY clause to specify the host variable type, this expression cannot be used in the TARGET clause.

An input host variable can be used in a WHERE clause. However, be aware that when using a host variable in a join predicate in a WHERE clause, the query optimizer will be unaware of its data type, and thus can only use the NL join method when creating an execution plan. To overcome this limitation and allow the optimizer to choose a more efficient joining method, use the CAST operator in the SQL statement to let the optimizer know the type of the host variable.

Examples

The following examples illustrate the use of input host variables in various ways.

[Example 1] The following example shows the use of the input host variables s_gno, s_gname, s_goods_location, s_stock, and s_price in an INSERT statement. The values stored in the input host variables are inserted into respective table columns.

< Sample Program: insert.sc >

EXEC SQL BEGIN DECLARE SECTION;
char    s_gno[10+1];
char    s_gname[20+1];
char    s_goods_location[9+1];
int     s_stock;
double  s_price;
EXEC SQL END DECLARE SECTION;

strcpy(s_gno, "F111100002");
strcpy(s_gname, "XX-101");
strcpy(s_goods_location, "FD0003");
s_stock = 5000;
s_price = 9980.21;

EXEC SQL INSERT INTO GOODS 
VALUES (:s_gno, :s_gname, :s_goods_location, 
:s_stock, :s_price);

[Example 2] The following example shows the use of the input host variables s_dno, s_emp_job, and s_eno in an UPDATE statement. The values in the DNO and EMP_JOB columns of the records that satisfy the condition in the WHERE clause are updated with the values of s_dno and s_emp_job, respectively.

< Sample Program : update.sc >

EXEC SQL BEGIN DECLARE SECTION;
int      s_eno;
short    s_dno;
varchar  s_emp_job[15+1];
EXEC SQL END DECLARE SECTION;

s_eno = 2;
s_dno = 1001;
strcpy(s_emp_job.arr, "ENGINEER");
s_emp_job.len = strlen(s_emp_job.arr);

EXEC SQL UPDATE EMPLOYEES 
SET DNO = :s_dno,
                          EMP_JOB = :s_emp_job
            WHERE ENO = :s_eno;

[Example 3] The following example shows the use of the input host variables s_eno and s_dno in a DELETE statement. The values of the host variables are used in the WHERE clause to determine which records are to be deleted.

< Sample Program : delete.sc >

EXEC SQL BEGIN DECLARE SECTION;
int      s_eno;
short    s_dno;
EXEC SQL END DECLARE SECTION;

s_eno = 5;
s_dno = 1000;

EXEC SQL DELETE FROM EMPLOYEES 
WHERE ENO > :s_eno AND 
DNO > :s_dno AND 
EMP_JOB LIKE 'P%';

[Example 4] The following example shows the use of the input host variable s_dno in a SELECT statement. The value of s_dno is used in the WHERE clause to determine which records to retrieve.

< Sample Program : select.sc >

EXEC SQL BEGIN DECLARE SECTION;
short      s_dno;
char       s_dname[30+1];
char       s_dep_location[9+1];
EXEC SQL END DECLARE SECTION;

s_dno = 1001;
EXEC SQL SELECT DNAME, DEP_LOCATION
INTO :s_dname, :s_dep_location
FROM DEPARTMENTS
WHERE DNO = :s_dno;

[Example 5] The following example shows the use of the input host variable s_call in the select list of a SELECT statement.

< Sample Program : host_target.sc >

EXEC SQL BEGIN DECLARE SECTION;
double s_call;
EXEC SQL END DECLARE SECTION;

s_call = 0.045;

EXEC SQL SELECT principal sum * ( 1 - CAST( :s_call AS DOUBLE ) ) FROM 계좌;

[Example 6] The following example shows the use of the input host variable s_period in the GROUP BY clause of a SELECT statement.

< Sample Program : host_group.sc >

int s_period;
EXEC SQL END DECLARE SECTION;

s_period = 1;    /* 1(month), 3(quarter year), 6(half year) */

EXEC SQL SELECT SUM(sale) FROM sales 
                 GROUP BY FLOOR( month / CAST( :s_period AS INTEGER ) );

[Example 7] The following example shows the use of the input host variable s_diff in the join predicate of a WHERE clause.

< Sample Program : host_join.sc >

EXEC SQL BEGIN DECLARE SECTION;
int s_diff;
EXEC SQL END DECLARE SECTION;

s_diff = 1;

EXEC SQL SELECT * FROM t1, t2
                 WHERE t1.i1 = t2.i1 + CAST( :s_diff AS INTEGER );

Indicator Variables#

Overview#

Because NULL table column values cannot be expressed in the host language, a method of handling them separately is required.

To enable APRE to process NULL values, the use of so-called "indicator variables" is supported.

Indicator variables are used alongside host variables in embedded SQL statements to process NULL values.

Why use indicator variables?#

For Handling Null Values:

Indicator variables can be used to provide information on the basis of which a programmer can judge whether or not a column value is NULL.
If an input indicator variable is set to -1 (SQL_NULL_DATA), the corresponding host variable will be processed as NULL. If the value of an output indicator variable is -1 (SQL_NULL_DATA), For example, an indicator variable can be used to indicate whether the value of a host variable to be used in an INSERT statement is NULL, or whether a column value returned by a SELECT statement is NULL.

For Managing the Length of Data:

Indicator variables can also be used to specify the length of an input value or store the length of a column value returned by a SELECT statement. Indicator variables can be used to manage data length only for character or binary type host variables. To specify the length of an input value, an input indicator variable would be used, whereas an output indicator variable would be used to store the length of a returned column value. If a host variable is a character type variable, and the value to be input or the returned column value is terminated with a null terminator ("\0") and is known not to be NULL, there is no need to use an indicator variable. When dealing with a binary type host variable, it is essential to use an indicator variable, even when the input value or the returned column value is known not to be NULL. This is because the binary type is not terminated with a NULL character, and the database needs a way of knowing the length of the input value, while the application needs a way of knowing the length of the returned column.) For more information about the use of binary type host variables, please refer to Chapter 5: Host Variable Data Types.

Declaring Indicator Variables#

Indicator variables are declared as follows:

Indicator variables are declared in the host variable declaration section or the function argument declaration section. If an attempt is made to use an indicator variable in an embedded SQL statement, and the indicator variable was not previously declared in the host variable declaration section or the function argument declaration section, an error saying "The host variable [variable_name] is unknown." will be raised during the precompile operation. For more information about the host variable declaration section and the function argument declaration section, please refer to Chapter 3: Host Variable Declaration Section.

The syntax for declaring indicator variables is as follows:

datatype indicator_variable_name;
The data type of an indicator variable must be int or SQLLEN (a predefined type in ODBC). It can also be a data structure, as long as it consists of only the int and SQLLEN types.
The names of indicator variables must start with an alphabetic character (a ~ z, A ~ Z), the underscore character ("_"), or a dollar sign ("$"), and must not be longer than 50 bytes.

Syntax#

The syntax for using indicator variables within embedded SQL statements is as follows:

<:host_variable> [INDICATOR] <:indicator_variable>

The keyword "INDICATOR" can be omitted.

If the host variable is not a structure, the indicator variable must not be a structure either. However, if the host variable is a structure, the indicator variable must be a structure too.

When is it necessary to use indicator variables?#

Indicator variables must be used in the following cases:

When an input value is NULL
When inputting a NULL value, an indicator variable must be used, and its value must be set to -1 (SQL_NULL_DATA).
When querying a column that does not have a NOT NULL constraint
If the value of a selected or fetched column is NULL and an indicator variable is not being used, the result of execution of the embedded SQL statement (sqlca.sqlcode) will be SQL_SUCCESS_WITH_INFO, and a warning message will be returned in the variable sqlca.sqlerrm.sqlerrmc.
When the type of an input or output host variable is APRE_BINARY, APRE_BLOB or APRE_BYTES
Because binary types are not NULL-terminated, the database needs a way of knowing the length of an input value. Therefore, the length of the input data must be specified using the indicator variable. In the same way, when dealing with output host variables, the length of returned column values must be stored in indicator variables. For more information about the APRE_BINARY, APRE_BLOB and APRE_BYTES data types, please refer to Chapter 5: Host Variable Data Types.
When using an APRE_NIBBLE type output host variable
An indicator variable must be used when entering a NULL value in a NIBBLE type column or reading a NULL value from a NIBBLE type column. For more information about the use of the APRE_NIBBLE data type, please refer to Chapter 5: Host Variable Data Types

Considerations#

When a host variable is a structure, the corresponding indicator variable must also be a structure. The two structures must have the same number of elements

Example)

EXEC SQL BEGIN DECLARE SECTION;
struct tag1 { int i1; int i2; } var1;
struct tag2 { int i1_ind; int i2_ind; } var1_ind1;
struct tag3 { int i1_ind; int i2_ind; 
int i3_ind; } var1_ind2; 
EXEC SQL END DECLARE SECTION;

EXEC SQL INSERT INTO T1(I1, I2) 
VALUES (:var1 :var1_ind1);  (O)
EXEC SQL INSERT INTO T1(I1, I2) 
VALUES (:var1 :var1_ind2);  (X)

An indicator variable cannot be used with a host variable that is an array of structures.

Example)

EXEC SQL BEGIN DECLARE SECTION;
struct tag1 { int i1; int i2; char i3[11]; } var1[10];
struct tag2 { int i1_ind; int i2_ind; int i3_ind; } var1_ind1[10];
EXEC SQL END DECLARE SECTION;

EXEC SQL INSERT INTO T1(I1, I2, I3) 
VALUES (:var1 :var1_ind1);  (X)

When dealing with a VARCHAR type host variable, if an indicator variable is specified for use with the host variable, it will be used as the indicator variable, whereas if no indicator variable is specified, the len variable, which is an element of the VARCHAR type, will automatically be used as the indicator variable. In this case it is acceptable to use the value of len as the indicator variable.

Example)

EXEC SQL BEGIN DECLARE SECTION;
varchar var1;
int var1_ind;
EXEC SQL END DECLARE SECTION;

/* Inserting 'TEST' in column I1 of table T1
when var1.len is used as an indicator variable  */
strcpy(var1.arr, "TEST");
var1.len = strlen(var1.arr);
EXEC SQL INSERT INTO T1(I1) 
VALUES (:var1);

/*  Inserting NULL in column I1 of table T1
when var1.len is used as an indicator variable */
var1.len = -1;
EXEC SQL INSERT INTO T1(I1) 
VALUES (:var1);

/*  Inserting 'TEST' in column I1 of table T1
when var1_ind is used as an indicator variable */
strcpy(var1.arr, "TEST");
var1_ind = strlen(var1.arr);
EXEC SQL INSERT INTO T1(I1) 
VALUES (:var1 :var1_ind);

Examples#

In the following example, s_goods_location_ind is used as the indicator variable for the s_goods_location host variable, and s_price_ind is used as the indicator variable for the s_price host variable. Because the value of both indicator variables is SQL_NULL_DATA, NULL will be inserted in the corresponding columns, even though the values of the s_goods_location and s_price host variables are not NULL.

< Sample Program : indicator.sc >

/* declare host variables */
EXEC SQL BEGIN DECLARE SECTION;
char    s_gno[10+1];
char    s_gname[20+1];
char    s_goods_location[9+1];
int     s_stock;
double  s_price;

/* declare indicator variables */
int      s_goods_location_ind;
int      s_price_ind;
EXEC SQL END DECLARE SECTION;

/* set host variables */
strcpy(s_gno, "X111100002");
strcpy(s_gname, "XX-101");
strcpy(s_goods_location, "FD0003");
s_stock = 5000;
s_price = 9980.21;

/* set indicator variables */
s_goods_location_ind = SQL_NULL_DATA;
s_price_ind          = SQL_NULL_DATA;

EXEC SQL INSERT INTO GOODS 
VALUES (:s_gno, 
:s_gname,
:s_goods_location :s_goods_location_ind,
:s_stock,
:s_price :s_price_ind);

Classifying Indicator Variables#

Indicator variables are classified as either input indicator variables or output indicator variables depending on whether they are used with output host variables or input host variables.

Output Indicator Variables#

If the column corresponding to an output host variable does not have a NOT NULL constraint, it is essential that an indicator variable be used for the host variable. The reason for this is that when the value of a selected or fetched column is NULL and an indicator variable is not being used, the result of execution of the embedded SQL statement (sqlca.sqlcode) will be SQL_SUCCESS_WITH_INFO and a warning message will be returned in the variable sqlca.sqlerrm.sqlerrmc.

If the value of the indicator variable is -1 (SQL_NULL_DATA), this means that NULL will be returned from the column. Therefore, the value of the output host variable is not meaningful (i.e. a garbage value). If the value of the indicator variable is not -1 (SQL_NULL_DATA), this means that the value in the corresponding column is not NULL, and will be saved in the output host variable. For more detailed information about the value of the indicator variable in such cases, please refer to the next section "Meaning of Indicator Variables".

Examples

The following is an example of the use of an output indicator variable.

In this example, the variable s_good_ind is used as an indicator variable for the variable s_goods. Because s_goods is a structure, s_good_ind must also be declared as a structure. The two structures will have the same number of components. After the SELECT statement is executed, each of the members of s_good_ind will be checked to determine if the value is -1.

< Sample Program : hostvar.h >

EXEC SQL BEGIN DECLARE SECTION;
typedef struct goods
{
    char   gno[10+1];
    char   gname[20+1];
    char   goods_location[9+1];
    int    stock;
    double price;
} goods;

typedef struct good_ind
{
    int gno;
    int gname;
    int goods_location;
    int stock;
    int price;
} good_ind;
EXEC SQL END DECLARE SECTION;

< Sample Program : indicator.sc >

/* specify path of header file */
EXEC SQL OPTION (INCLUDE=./include);
/* include header file for precompile */
EXEC SQL INCLUDE hostvar.h;

EXEC SQL BEGIN DECLARE SECTION;
goods     s_goods;
good_ind s_good_ind;
EXEC SQL END DECLARE SECTION;

EXEC SQL SELECT * 
INTO :s_goods :s_good_ind 
FROM GOODS 
WHERE GNO = :s_gno;

/* Because the GNO and GNAME columns have the NOT NULL constraint, their indicator variables do not have to
be checked. */ 
if (sqlca.sqlcode == SQL_SUCCESS) 
{
        if (s_good_ind.goods_location == SQL_NULL_DATA)
        {
            strcpy(s_goods.goods_location, "NULL");
        }
        if (s_good_ind.stock == SQL_NULL_DATA)
        {
            s_goods.stock = -1;
        }
        if (s_good_ind.price == SQL_NULL_DATA)
        {
            s_goods.price = -1;
        }
}

Input Indicator Variables#

To specify NULL as an input value, it is necessary to use an input indicator variable. In such cases, the value of the indicator variable must be set to -1.

When specifying a non-NULL input value, there is no need to use the corresponding indicator variable, but when using the indicator variable, care must be taken to ensure that there is no possibility that a NULL value will be entered. The meaning of the value of the indicator variable differs depending on the type of the input host variable. For more information, please refer to the next section "Meaning of Indicator Variables".

Example

The following is an example of the use of an input indicator variable.

In this example, the variable s_goods_location_ind is used as an indicator variable for the variable s_goods_location, and the variable s_price_ind is used as an indicator variable for the variable s_price. The values of s_goods_location_ind and s_price_ind are set to SQL_NULL_DATA (-1) to insert NULL into the GOODS_LOCATION and PRICE columns, respectively.

< Sample Program : indicator.sc >

EXEC SQL BEGIN DECLARE SECTION;
/* declare host variables */
char    s_gno[10+1];
char    s_gname[20+1];
char    s_goods_location[9+1];
int     s_stock;
double  s_price;

/* declare indicator variables */
int      s_goods_location_ind;
int      s_price_ind;
EXEC SQL END DECLARE SECTION;

/* set host variables */
strcpy(s_gno, "X111100002");
strcpy(s_gname, "XX-101");
strcpy(s_goods_location, "FD0003");
s_stock = 5000;
s_price = 9980.21;

/* set indicator variables */
s_goods_location_ind = SQL_NULL_DATA;
s_price_ind            = SQL_NULL_DATA;

EXEC SQL INSERT INTO GOODS 
VALUES (:s_gno, 
                   :s_gname, 
                   :s_goods_location :s_goods_location_ind, 
                   :s_stock, 
:s_price :s_price_ind);

Meaning of Indicator Variables#

The following table describes the meanings of indicator variable values depending on the type of the host variable and on whether the indicator variable is an input indicator variable or an output indicator variable.

An indicator variable value of -1 always signifies a NULL host variable value. The meaning of indicator variable values other than -1, however, differs depending on the type of the host variable and on whether the indicator variable is an input indicator variable or an output indicator variable. Therefore, it is important to understand the information in the following table, and to refer back to it when using indicator variables.

It is particularly important that the value of input indicator variables be set correctly, because these values are used internally by the precompiler and the database server

Host Variable Type	Value of Input Indicator Variables		Value of Output Indicator Variables
Host variable type / Indicator variable value	-1	-1 Values other than -1	-1	Values other than -1
Numeric types	Means the input value is NULL	Not internally used. Not meaningful.	Means the returned value is NULL. The actual value of the host variable does not mean anything. (Garbage value)	Contains the size of the host variable (sizeof).
Character types		Used to indicate the length of the input value (strlen). Must be set.		Contains the length of the returned value (strlen).
Date type		Not internally used. Not meaningful.		Contains the size of the host variable (sizeof).
APRE_BINARY		Used to indicate the length, in bytes, of the input value. Must be set.		Contains the length, in bytes, of the returned value.
APRE_BINARY2		Must specifies the length in bytes of the input.		The length (bytes) of the returned value is stored.
APRE_BLOB		Used to indicate the ength, in bytes, of the input value. Must be set.		Contains the length, in bytes, of the returned value.
APRE_CLOB		Used to indicate the length, in bytes, of the input value. Must be set.		The length (bytes) of the returned value is stored.
APRE_BYTES		Must specify the length in bytes of the input.		The length (bytes) of the returned value is stored.
APRE_NIBBLE		Not internally used. Not meaningful.		The length, in bytes, of the returned value is stored

Indicator variables are usually used for handling NULL values. However, as shown in the above table, input indicator variable values other than -1 can be meaningful, and are checked and used within the system. Therefore, when using an input indicator variable, it is important to set its value accurately, even when the value of the corresponding host variable is not NULL.

When the value of an input indicator variable that corresponds to a CHAR or BINARY type host variable is not -1, the database server will take the value of the indicator variable as the length of the input value, and process the value accordingly.

Sample Programs#

indicator.sc

This example can be found at $ALTIBASE_HOME/sample/APRE/indicator.sc

Result of Execution

$ is -f schema/schema.sql
$ make indicator
$ ./indicator
<INDICATOR VARIABLES>
-----------------------------------------------------------
[Scalar Indicator Variables]                                      
-----------------------------------------------------------
Success insert

-----------------------------------------------------------
[Structure Indicator Variables]                                   
-----------------------------------------------------------
GNO        GNAME                GOODS_LOCATION  STOCK  PRICE      
-----------------------------------------------------------
X111100002 XX-101               NULL            5000   -1.00

-----------------------------------------------------------
 [Scalar Array Indicator Variables]                                
-----------------------------------------------------------
3 rows updated
3 times update success

-----------------------------------------------------------
 [Arrays In Structure]                                             
-----------------------------------------------------------
3 rows inserted
3 times inserte success

-----------------------------------------------------------
 [Indicator Variable(.len) of VARCHAR With Output Host Variables]  
-----------------------------------------------------------
v_address.arr = [Pusan University]
v_address.len = 16

-----------------------------------------------------------
 [Indicator Variable(.len) of VARCHAR With Input Host Variables]   
-----------------------------------------------------------
Success update

-----------------------------------------------------------
 [Indicator Variable of DATE Type With Input Host Variables]       
-----------------------------------------------------------
Success update

-----------------------------------------------------------
 [Indicator Variable of DATE Type With Output Host Variables]      
-----------------------------------------------------------
d_arrival_date2 = NULL

Administrator

Reference

Developer

Tools

External Tools

2. Host Variables and Indicator Variables#

Host Variables#

Overview#

Declaring Host Variables#

Using Host Variables in Embedded SQL Statements#

Example#

Classifying Host Variables#

Output Host Variables#

Input Host Variables#

Indicator Variables#

Overview#

Why use indicator variables?#

Declaring Indicator Variables#

Syntax#

When is it necessary to use indicator variables?#

Considerations#

Examples#

Classifying Indicator Variables#

Output Indicator Variables#

Input Indicator Variables#

Meaning of Indicator Variables#

Sample Programs#