Informix Guide to SQL: Tutorial Programming with SQL

The set of rows that a SELECT statement returns is its active set. A singleton SELECT statement returns a single row. 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 cursor to retrieve 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 you can write the embedded SELECT statement 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/98'));

The INTO clause is the only detail that distinguishes this statement from any example in Chapter 2 or Chapter 5. 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.

The following ESQL/C 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. The declaration 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 that is used in a statement is noted and passed to the database server with the statement. The database server does its best to convert column data into the form that the receiving variables use. Almost any conversion is allowed, although some conversions cause a precision loss. The results of the preceding example differ, depending on the data type of the receiving host variable, as the following table shows.

Working with Null Data

What if the program retrieves a null value? Null values can be stored in the database, but the data types that programming languages support do not recognize a null state. A program must have some way to recognize 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 the variable is null, 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 with a host variable differs with the language you are using, but the principle is the same in all languages.

Although the database server handles conversion between data types automatically, several things still can go wrong with a SELECT statement. In SQL programming, as in any kind of programming, you must anticipate errors and provide for them at every point.

One common event is that no rows satisfy a query. This event is signalled by an SQLSTATE code of 02000 and by a code of 100 in SQLCODE after a SELECT statement. This code indicates an error or a normal event, depending entirely on your application. If you are sure a row or rows should satisfy the query (for example, if you are reading a row using a key value that you just read from a row of another table), then the end-of-data code represents a serious failure in the logic of the program. On the other hand, if you select a row based on a key that a user supplies or some other source supplies that is less reliable than a program, a lack of data can be a normal event.

If your database is not ANSI compliant, the end-of-data return code, 100, is set in SQLCODE following SELECT statements only. In addition, the SQLSTATE value is set to 02000. (Other statements, such as INSERT, UPDATE, and DELETE, set the third element of SQLERRD to show how many rows they affected; Chapter 8 covers this topic.)

Errors that set SQLCODE to a negative value or SQLSTATE to a value that begins with anything other than 00, 01, or 02 are usually serious. Programs that you have developed and that are in production should rarely report these errors. Nevertheless, it is difficult to anticipate every problematic situation, so your program must be able to deal with these errors.

For example, a query can return error -206, which means that a table specified in the query is not in the database. This condition occurs if someone dropped the table after the program was written, or if the program opened the wrong database through some error of logic or mistake in input.

A SELECT statement that uses an aggregate function such as SUM, MIN, or AVG always succeeds in returning at least one row of data, even when no rows satisfy the WHERE clause. An aggregate value based on an empty set of rows is null, but it exists nonetheless.

However, an aggregate value is also null if it is based on one or more rows that all contain null values. If you must be able to detect the difference between an aggregate value that is based on no rows and one that is based on some rows that are all null, you must include a COUNT function in the statement and an indicator variable on the aggregate value. You can then work out the following cases.

You can handle these inevitable errors in many ways. In some applications, more lines of code are used to handle errors than to execute functionality. In the examples in this section, however, one of the simplest solutions, the default value, should work, as the following example shows:

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.