Informix Guide to SQL: Tutorial

Informix Guide to SQL: Tutorial
Chapter 5: Programming with SQL

Home Contents Index Master Index New Book

Retrieving Single Rows

You can use embedded SELECT statements to retrieve single rows from the database into host variables. When a SELECT statement returns more than one row of data, however, a program must use a more complicated method to fetch the rows one at a time. Multiple-row select operations are discussed in "Retrieving Multiple Rows".

To retrieve a single row of data, simply embed a SELECT statement in your program. The following example shows how the embedded SELECT statement can be written using INFORMIX-ESQL/C:

EXEC SQL select avg (total_price)

	into :avg_price

	from items

	where order_num in

		(select order_num from orders 

		where order_date < date('6/1/94'));

The INTO clause is the only detail that distinguishes this statement from any example in Chapter 2, "Composing Simple SELECT Statements," or Chapter 3, "Composing Advanced SELECT Statements." This clause specifies the host variables that are to receive the data that is produced.

When the program executes an embedded SELECT statement, the database server performs the query. The example statement selects an aggregate value, so that it produces exactly one row of data. The row has only a single column, and its value is deposited in the host variable named avg_price. Subsequent lines of the program can use that variable.

You can use statements of this kind to retrieve single rows of data into host variables. The single row can have as many columns as desired. If a query produces more than one row of data, the database server cannot return any data. It returns an error code instead.

You should list as many host variables in the INTO clause as there are items in the select list. If, by accident, these lists are of different lengths, the database server returns as many values as it can and sets the warning flag in the fourth field of SQLWARN.

Data Type Conversion

The following example retrieves the average of a DECIMAL column, which is itself a DECIMAL value. However, the host variable into which the average of the DECIMAL column is placed is not required to have that data type.

EXEC SQL select avg (total_price) into :avg_price

	from items;

The declaration of the receiving variable avg_price in the previous example of ESQL/C code is not shown. It could be any one of the following definitions:

	int avg_price;

	double avg_price;

	char avg_price[16];

	dec_t avg_price; /* typedef of decimal number structure */

The data type of each host variable used in a statement is noted and passed to the database server along with the statement. The database server does its best to convert column data into the form used by the receiving variables. Almost any conversion is allowed, although some conversions cause a loss of precision. The results of the preceding example differ, depending on the data type of the receiving host variable, as described in the following list:


FLOAT	The database server converts the decimal result to FLOAT, possibly truncating some fractional digits. If the magnitude of a decimal exceeds the maximum magnitude of the FLOAT format, an error is returned.

INTEGER	The database server converts the result to INTEGER, truncating fractional digits if necessary. If the integer part of the converted number does not fit the receiving variable, an error occurs.

CHARACTER	The database server converts the decimal value to a CHARACTER string. If the string is too long for the receiving variable, it is truncated. The second field of SQLWARN is set to `W`, and the value in the SQLSTATE variable is `01004`.

Working with Null Data

What if the program retrieves a null value? Null values can be stored in the database, but the data types supported by programming languages do not recognize a null state. A program must have some way of recognizing a null item to avoid processing it as data.

Indicator variables meet this need in SQL APIs. An indicator variable is an additional variable that is associated with a host variable that might receive a null item. When the database server puts data in the main variable, it also puts a special value in the indicator variable to show whether the data is null. In the following INFORMIX-ESQL/C example, a single row is selected, and a single value is retrieved into the host variable op_date:

EXEC SQL select paid_date

		into :op_date:op_d_ind

		from orders

		where order_num = $the_order;

if (op_d_ind < 0) /* data was null */

	rstrdate ('01/01/1900', :op_date);

Because the value might be null, an indicator variable named op_d_ind is associated with the host variable. (It must be declared as a short integer elsewhere in the program.)

Following execution of the SELECT statement, the program tests the indicator variable for a negative value. A negative number (usually -1) means that the value retrieved into the main variable is null. If that is the case, this program uses an ESQL/C library function to assign a default value to the host variable. (The function rstrdate is part of the INFORMIX-ESQL/C product.)

The syntax that you use to associate an indicator variable differs with the language you are using, but the principle is the same in all languages.

avg_price = 0; /* set default for errors */

EXEC SQL select avg (total_price)

		into :avg_price:null_flag

		from items;

if (null_flag < 0) /* probably no rows */

	avg_price = 0; /* set default for 0 rows */

The previous example deals with the following considerations:

If the query selects some non-null rows, the correct value is returned and used. This result is the expected and most frequent one.
If the query selects no rows, or in the much less likely event that it selects only rows that have null values in the total_price column (a column that should never be null), the indicator variable is set, and the default value is assigned.
If any serious error occurs, the host variable is left unchanged; it contains the default value initially set. At this point in the program, the programmer sees no need to trap such errors and report them.

Informix Guide to SQL: TutorialChapter 5: Programming with SQL Home Contents Index Master Index New Book

Retrieving Single Rows

Informix Guide to SQL: Tutorial
Chapter 5: Programming with SQL

Home Contents Index Master Index New Book