ElevateDB generates two types of query result set cursors depending upon the makeup of a given SELECT statement:


The following rules determine whether a result set cursor will be sensitive or insensitive.

Single-table queries

---

Queries that retrieve data from a single table will generate a sensitive result set provided that:

1) The user or developer requests a sensitive result set cursor. Please see the DECLARE statement for more information on requesting a a sensitive or insensitive result set cursors in SQL/PSM routines, and your product-specific manual for requesting sensitive or insensitive cursors in client applications.

2) There is no DISTINCT keyword in the SELECT statement.

3) All SELECT expressions are either a column reference or a computed column that does not contain any aggregate functions (MIN, MAX, SUM, etc.). Computed columns are read-only in the sensitive result set cursor and cannot be modified.

4) There is no GROUP BY clause in the SELECT statement.

5) There is no ORDER BY clause in the SELECT statement, or there is an ORDER BY clause that minimally matches the columns, and the collations defined for the columns, in an existing index in the source table.

6) There are no correlated sub-queries in the WHERE clause of the SELECT statement.


A query containing sub-queries in the SELECT column expressions can return a sensitive result set in ElevateDB. This means that a query like the following can return a sensitive result set:

SELECT CustNo,
(SELECT Company FROM Customer WHERE Customer.CustNo=Orders.CustNo) AS Company
FROM Orders

If the sensitive result set is updated and the CustNo column is changed, the "looked-up" Company value will automatically change as necessary. This is extremely useful for situations where, in the past, you would normally use a join and get an insensitive result set.

Multi-table queries

---

All queries that join two or more tables or merge two or more SELECT statements via the UNION/INTERSECT/EXCEPT operators will automatically produce an insensitive result set cursor, irrespective of the requested result set cursor type.

Temporary Tables

---

If a SELECT statement generates an insensitive result set cursor, then a temporary table will created in order to hold the rows that make up the result set. This temporary table is stored in a location specified by either the ElevateDB Server or the client application. By default, ElevateDB uses the local user temporary files path in the operating system for this setting. Please see your product-specific manual for more information on modifying the temporary tables path for either the ElevateDB Server or the client application.

Identifying the Result Set Cursor Type

---

You may use the SENSITIVE function to identity the type of a result set cursor in an SQL/PSM routine after it has been opened via the OPEN statement. Please see your product-specific manual for more information on determining the type of a result set cursor in a client application.