SQL-J Language Reference
Page 47 of 121

SELECT statement

A SELECT statement consists of a query with an optional ORDER BY clause, an optional FOR UPDATE clause, and an optional AT ISOLATION clause. The SELECT statement is so named because the typical first word of the query construct is SELECT. (Query includes the VALUES expression and UNION expressions as well as SELECT expressions).

The ORDER BY clause guarantees the ordering of the ResultSet. The FOR UPDATE clause makes the result an updatable cursor. The AT ISOLATION clause allows the user to specify an isolation level for a specific query without committing the current transaction.

Syntax

Query
[ ORDER BY clause]
[ FOR UPDATE clause]
[ AT ISOLATION clause]

Example

-- a query with an ORDER BY clause
SELECT city.getDistanceFrom(
    findCity(getCurrentConnection(), 35))
    AS distance_from_paris, city.getName() AS name
FROM Cities
ORDER BY distance_from_paris

-- creating an updatable cursor with a FOR UPDATE clause
SELECT hotel_id, booking_date, rooms_taken
FROM HotelAvailability WHERE hotel_id =3 AND
EXTRACT(MONTH FROM booking_date) = 9
FOR UPDATE OF rooms_taken

-- set the isolation level to SERIALIZABLE
SELECT *
FROM FlightAvailability
WHERE flight_id BETWEEN 'AA1000' AND 'AA1200'
AT ISOLATION SERIALIZABLE

A SELECT statement returns a ResultSet. A cursor is a pointer to a specific row in ResultSet. In Java applications, all ResultSets are cursors. A cursor is updatable--that is, you can update or delete rows as you step through the ResultSet--if the SELECT statement that generated it and its underlying query meet cursor updatability requirements, as detailed below. You use a FOR UPDATE clause when you want to generate an updatable cursor.

NOTE: The ORDER BY clause allows you to order the results of the SELECT. Without the ORDER BY clause, the results are returned in random order. No order is guaranteed without the ORDER BY clause. ORDER BY is not permitted with non-orderable Java data types.

If a SELECT statement meets the requirements listed below, cursors are updatable only if you specify FOR UPDATE in the FOR clause (see FOR UPDATE clause).

Requirements for Updatable Cursors

Only simple, single-table SELECT cursors can be made updatable. To generate updatable cursors:

  • The SELECT statement must not include an ORDER BY clause.
  • The underlying Query must be a SelectExpression.
  • The SelectExpression in the underlying Query must not include:
    • DISTINCT
    • Aggregates
    • GROUP BY clause
    • HAVING clause
  • The FROM clause in the underlying Query must not have:
    • more than one table in its FROM clause
    • anything other than one table name
    • SelectExpressions
    • subqueries

There is no SQL-J language statement to assign a name to a cursor. Instead, you use the JDBC API to assign names to cursors or retrieve system-generated names. For more information, see Naming or Accessing the Name of a Cursor in the Cloudscape Developer's Guide.

Cursors are read-only by default. For a cursor to be updatable, you must specify FOR UPDATE in the FOR clause (see FOR UPDATE clause).

Dependency System

The SELECT depends on all the tables and views named in the query and the conglomerates (units of storage such as heaps and indexes) chosen for access paths on those tables. CREATE INDEX does not invalidate a prepared SELECT statement. A DROP INDEX statement invalidates a prepared SELECT statement if the index is an access path in the statement. If the SELECT includes views, it also depends on the dictionary objects on which the view itself depends (see CREATE VIEW statement).

Any prepared UPDATE WHERE CURRENT or DELETE WHERE CURRENT statement against a cursor of a SELECT depends on the SELECT. Removing a SELECT through a java.sql.Statement.close request invalidates the UPDATE WHERE CURRENT or DELETE WHERE CURRENT.

The SELECT depends on all aliases used in the query. Dropping an alias invalidates a prepared SELECT statement if the statement uses the alias.

SET Statements