Previous Topic: Data Manipulation OperationsNext Topic: Adding Data


Retrieving Data

Using SELECT

In a program, you use the SELECT statement in one of these ways to retrieve data from the database:

When embedding a SELECT statement, specify each column even if you mean to select all columns. Using SELECT * to select all columns can cause a program error if, for example, a column is added to the table.

Single-row SELECT Statement

If the result of a SELECT statement will be one and only one row, you can issue a SELECT statement with an INTO clause.

A result table will contain only one row when:

Checking Single-row Select Status

If the number of rows returned by a SELECT statement with an INTO clause is greater than 1, the DBMS returns a cardinality violation error. No data is moved to the host variables named in the INTO clause.

If no row is found that matches the selection criteria, the DBMS returns a no rows found warning and moves 100 to SQLCODE.

Updating the Single Row

Under cursor stability if the program performs single-row select that specifies the primary key in the search condition, the DBMS locks the base row from which the resulting row is derived. This prevents any update by a concurrent database transaction. The lock is maintained until one of these events occurs:

Until one of these events occurs, the SQL transaction can update the row without a need to check whether a concurrent transaction has modified the row.

Note: For more information about updating rows, see Modifying Data.

Multiple-row SELECT

If the result table of a SELECT statement potentially has multiple rows, the program must declare a cursor or perform bulk processing to process retrieved data.

Note: For more information about retrieving multiple rows, see Data Manipulation with SQL.