According to the standard definition of SQL, the statements 'select' (declare cursor), 'update', and 'delete' always apply to sets of table rows. ADABAS offers additional variants of these statements as an extension of the SQL language. These so-called single row statements always address one table row. Therefore single row statements generally make higher concurrency in multi-user mode possible (see chapters Transactions and Locks). The present section describes the main features of set and single row statements. For more details, see the Reference manual.
Single Row Selects
ADABAS provides the option of performing a single row select by specifying a key qualification. For a single row select (select ordered statements), the contents of a single table row are transferred to host variables. This is either the first or last table row (select first/select last), a row with a known key value (select direct), or, in relation to a known key position, the preceding or following row (select prev/select next). For single row selects, the column values of the result row must be transferred directly into host variables using the into part of the statement (see the example with 'Select Direct' at the end of this section).
Modifying Single Rows
ADABAS allows the usage of the key qualification also for update and delete statements. As for single row selects, for tables with key columns, the key value is specified by values for all key columns. For tables without key columns, ADABAS provides an internal key column called 'syskey (char(8) byte)'; its value can be transferred into the program variables by a select statement and can be used as a key qualification for subsequent single row selects.
Set Requests
The result of a select request without single row qualification is buffered in the database in a so-called result table. The third element of sqlerrd indicates the number of result table rows for some select statements. It has the value -1 if the number of results is unknown at the select point in time.
Result tables can be named or unnamed. In the sqlmode ANSI, result tables must be deleted before their names can be used for another result table, whereas in the sqlmodes ADABAS and ORACLE, the result tables are automatically overwritten by the next select statement that creates a result table with the same name.
In sqlmode ANSI, result tables are deleted at the end of a transaction. In the sqlmode ADABAS, result tables are deleted which were generated within a transaction that was concluded by a rollback statement. In sqlmode ORACLE, result tables created via select statements outlast the current transaction and are implicitly deleted at the end of the database session.
Result tables are normally processed sequentially via repeated fetch statements.When doing so, each fetch statement transfers the values of a single result table row into the program variables. As long as there are still result rows available, the return code of the fetch statement has the value 0; after the last row has been processed, the code is +100.
Furthermore, it is possible in sqlmode ADABAS to fetch the first row of the result table with 'fetch first' and the last row of the result table with 'fetch last'. 'Fetch next' and 'fetch prev' access the next or previous result table row. If required, it is also possible to position within the result table and to process it repeatedly.
According to the standard definition of SQL, a result table is specified for database set requests in programming languages by the statement 'declare <result table name> cursor for <select expression>' and it is read with 'open', 'fetch', and 'close'. ADABAS offers this possibility, too.
Examples:
ADABAS SQL Request with an Unnamed Result table:
ADABAS SQL Request with a Named Result table:
Standard SQL Request:
Example with Select Direct:
Example with Update:
Example with Delete:
