Mimer SQL Documentation TOC PREV NEXT INDEX

Mimer SQL Developer Site


Accessing Data


This section explains how embedded SQL applications retrieve data.

Retrieving Data Using Cursors

Data is retrieved from database tables with the FETCH statement, which fetches the values from an individual row in a result set into host variables.

The result set is defined by a SELECT construction or a result set procedure CALL, see Manipulating Data, used in a cursor declaration. A cursor may be thought of as a pointer which moves through the rows of the result set as successive FETCH statements are issued.

An exception is raised to indicate when the FETCH has reached the end of the result set.

Data retrieval involves several steps in the application program code, which are as follows:

General Framework

The steps in the previous section are built into the application program as shown in the general frameworks below (only SQL statements are shown in the frameworks).

For a SELECT:

 EXEC SQL BEGIN DECLARE SECTION;
    ... VAR1, VAR2, ... VARn ...
 EXEC SQL END DECLARE SECTION;
 
 EXEC SQL DECLARE cursor-name CURSOR FOR select-statement;
 
 EXEC SQL OPEN cursor-name;
 
 loop as required
    EXEC SQL FETCH cursor-name
                 INTO :VAR1, :VAR2, ..., :VARn;
 end loop;
 
 EXEC SQL CLOSE cursor-name;
 

For a result set procedure CALL:

 EXEC SQL BEGIN DECLARE SECTION;
    ... VAR1, VAR2, ... VARn ...
 EXEC SQL END DECLARE SECTION;
 
 EXEC SQL DECLARE cursor-name CURSOR FOR CALL routine-invocation;
 
 EXEC SQL OPEN cursor-name;
 
 loop as required
     EXEC SQL FETCH cursor-name
                  INTO :VAR1, :VAR2, ..., :VARn;
 end loop;
 
 EXEC SQL CLOSE cursor-name;

Declaring Host Variables

All host variables used to hold data fetched from the database and used in selection conditions or as result set procedure parameters must be declared within an SQL DECLARE SECTION, see Communicating with the Application Program.

Indicator variables for columns that may contain NULL values must also be declared.

The same indicator variable may be associated with different main variables at different times, but declaration of a dedicated indicator variable for each main variable is recommended for clarity.

Declaring the Cursor

A cursor operates as a row pointer associated with a result set.

A cursor is defined by the DECLARE CURSOR statement and the set of rows addressed by the cursor is defined by the SELECT statement in the cursor declaration.

Cursors are local to the program in which they are declared. A cursor is given an identifying name when it is declared.

DECLARE CURSOR is a declarative statement that does not result in any implicit connection to a database, see Idents and Privileges for details on connecting to a database.

Preprocessing the statement generates a series of parameters used by the SQL compiler but does not generate any executable code; the select-expression or result set procedure call in the cursor declaration is not executed until the cursor is opened.

Holdable cursors can be declared using the WITH HOLD clause. An open cursor declared WITH HOLD remain open after COMMIT.

Cursors should normally be declared WITHOUT HOLD (default), because WITH HOLD cursors require more internal resources then ordinary cursors. In addition, long lasting WITH HOLD cursors can have negative performance effects just like long lasting transactions.

If the cursor declaration contains a CALL to a result set procedure, it is FETCH that actually executes the procedure.

The RETURN statement is used from within the result set procedure to return a row of the result set.

Each FETCH causes statements in the result set procedure to execute until a RETURN statement is executed, which will return the row data defined by it. Execution of the procedure is suspended at that point until the next FETCH.

If, during execution, the end of the procedure is encountered instead of a RETURN statement, the FETCH result is end-of-set. Result Set Procedures for a detailed description of result set procedures.

Note: It is advisable always to use an explicit list of items in the SELECT statement of the cursor declaration. The shorthand notations SELECT * and SELECT table.* are useful in interactive SQL, but can cause conflicts in the variable lists of FETCH statements if the table definition is changed.

Host Variables

The cursor declaration can use host variables in the WHERE or HAVING clause of the SELECT statement.

The result set addressed by the cursor is then determined by the values of these host variables at the time when the cursor is opened.

The same cursor declaration can thus address different result sets depending on when the cursor is opened, for example:

 EXEC SQL DECLARE C1 CURSOR..; -- cursor with host variables
 set variables
 EXEC SQL OPEN C1;             -- open one result set
 ...
 EXEC SQL CLOSE C1;
 set variables
 EXEC SQL OPEN C1;             -- open different result set
 

Scrollable cursors can be declared using the SCROLL keyword. When a cursor is declared as scrollable, records can be fetched using an orientation specification. This makes it possible to scroll through the result set with the cursor.

Cursors which are to be used only for retrieving data may be declared with a FOR READ ONLY clause in the SELECT statement. This can improve performance slightly in comparison with cursors that permit update and delete operations.

Opening the Cursor

A declared cursor must be opened with the OPEN statement before data can be retrieved from the database. The OPEN statement evaluates the cursor declaration in terms of

When the OPEN statement has been executed, the cursor is positioned before the first row in the result set.

Retrieving Data

Once a cursor has been opened, data may be retrieved from the result set with FETCH statements, see the Mimer SQL Reference Manual, FETCH, for the syntax description.

Host variables in the variable list correspond in order to the column names specified in the SELECT clause of the cursor declaration. The number of variables in the FETCH statement may not be more than the number of columns selected. The number of variables may be less than the number of columns selected, but a success with warning -code is then returned in SQLSTATE.

A suitably declared record structure may be used in place of a variable list in host languages where this is supported, see Host Language Dependent Aspects.

Each FETCH statement moves the cursor to the specified row in the result set before retrieving data. In strict relational algebra, the ordering of tuples in a relation (the formal equivalent of rows in a table) is undefined. The SELECT statement in the cursor declaration may include an ORDER BY clause if the ordering of rows in the result set is important to the application.

Note: A cursor declared with an ORDER BY clause cannot be used for updating table contents.

If no ORDER BY clause is specified, the ordering of rows in the result set is unpredictable.

Note: The variables into which data is fetched are specified in the FETCH statement, not in the cursor declaration. In other words, data from different rows in the result set may be fetched into different variables.

When there are no more rows to fetch, the exception condition NOT FOUND will be raised.

The following construction thus fetches rows successively until the result set is exhausted:

 EXEC SQL DECLARE C1 CURSOR FOR select-statement;
 EXEC SQL OPEN C1;
 
 EXEC SQL WHENEVER NOT FOUND GOTO done;
 LOOP
     EXEC SQL FETCH C1 INTO :var1,:var2,...,:varn;
 END LOOP
 
 done:
 EXEC SQL CLOSE C1;
Access Rights

The access rights for a user are checked when the cursor is opened and they remain unchanged for that cursor until the cursor is closed.

For example, if an application program declares and opens a cursor, then SELECT access on the table is revoked from the user running the program, data can still be fetched from the result set as long as the cursor remains open. Any subsequent attempt to open the same cursor will, however, fail.

Block Fetching

The Embedded SQL interface tries whenever possible to fetch rows in blocks to minimize server communications. The first fetch would normally issue a request to the server for a number of rows at once. In most situations, this will improve application performance.

In some situations, this is not the desired behavior. One such situation is queries searching through a huge number of rows without the help of indexes. The database server may only be able to return one row a second, and the entire query may take minutes, but the user is happy as long as he sees the first rows on screen. If this is important to the application, set the fetch size manually. An appropriate fetch size is the number of rows displayed at once. See Mimer SQL Reference Manual, SET SESSION FETCH SIZE for more information.

Closing a Cursor

An opened cursor remains open until it is closed with one of the statements CLOSE, COMMIT, ROLLBACK or DISCONNECT. CLOSE closes the specified cursor. ROLLBACK and DISCONNECT close all open cursors for the connection. COMMIT closes all open cursors, except cursors declared WITH HOLD, for the connection. Once a cursor is closed, the result set is no longer accessible. The cursor declaration remains valid, however, and a new cursor may be opened with the same declaration.

Note: The result set addressed by the new cursor may not be the same if the contents of the database or the values of variables used in the declaration have changed.

Normally, resources used by the cursor remain allocated when the cursor is closed and will be used again if the cursor is re-opened. The optional form CLOSE cursor-name RELEASE deallocates cursor resources. Use of CLOSE with the RELEASE option is recommended in application programs which open a large number of cursors, particularly where system resources are limited.

Note: The use of CLOSE with the RELEASE option may slow down performance if there is a following OPEN, since it requires that new resources are allocated at the next OPEN for that cursor. For this reason it should only be used when necessary.

Cursors are local to a connection and remain open but dormant when the connection is made dormant. The state of dormant cursors is fully restored (including result set addressed and position in the result set) when the connection is reactivated. Cursors are, however, closed and cursor resources are deallocated, when a connection is disconnected.

Note: Cursors opened in a program ident context are closed and resources deallocated when LEAVE is executed within the same connection, unless LEAVE RETAIN is specified.

Retrieving Single Rows

If the result of a SELECT statement is known to be a single row, the SELECT INTO statement may be used as an alternative to fetching data through a cursor.

This is a much simpler programming construction, since cursors are not used and the only requirement is that host variables used in the SELECT INTO statement are declared in the DECLARE SECTION.

However, there are two disadvantages associated with SELECT INTO:

Use of a SELECT INTO statement is justified when the result set may contain several rows, but it is a condition for continued execution of the application program that the result row is unique. With a cursor, this would require a construction that checked that one and only one FETCH operation could be performed (alternatively, use a separate SELECT COUNT with the same search condition as the cursor). In such a case, a SELECT INTO statement with a check on the return code, see Handling Errors and Exceptions, is probably the preferred solution.

A CALL statement can be used to return information to the one or more host variables associated with the output parameter(s) of the procedure.

A SET statement can be used with a function or method invocation to return information to one host variable.

Retrieving Data from Multiple Tables

Data can be retrieved from multiple tables in ESQL by addressing several tables in the SELECT statement of the cursor declaration, in the same way as in interactive SQL. The preprocessor generates a SELECT statement addressing multiple tables, which is optimized by the SQL compiler when the cursor is opened.

Example
 EXEC SQL DECLARE c_1 CURSOR FOR SELECT ...
                                    FROM a JOIN b
                                    ON a.x = b.y;
 EXEC SQL OPEN c_1;
 ...
 

An alternative way to link information between tables could be to define the search condition for one cursor in terms of a variable fetched through another cursor:

 EXEC SQL DECLARE c_1 CURSOR FOR SELECT x
                                    FROM a;
 EXEC SQL DECLARE c_2 CURSOR FOR SELECT ...
                                    FROM b
                                    WHERE y = :HOSTX;
 
 EXEC SQL OPEN c_1;
 EXEC SQL FETCH c_1
             INTO :HOSTX;
 EXEC SQL CLOSE c_1;
 
 EXEC SQL OPEN c_2;
 EXEC SQL FETCH c_2;
 ...
 

When considering the two alternatives, the first one is preferred. The reason for this is:

The 'Parts explosion' Problem

A special case of data retrieval from multiple tables is the use of stacked cursors to fetch data from logical copies of the same table, in a manner that provides a solution to the so called "Parts explosion" problem.

A cursor can be defined as REOPENABLE and the same cursor may be opened several times in succession in the same application program, each previous instance of the cursor being saved on a stack and restored when the following instance is closed. A FETCH statement refers to the most recently opened instance of a cursor. Each instance of the cursor addresses an independent result set and the position of each cursor in its own result set is saved on the stack.

Note: Result sets addressed by different instances of a cursor may differ according to the conditions prevailing when the cursor instance was opened.

The state of the cursor stack needs to be controlled by the application. A counter can be used to indicate if there are more instances of the cursor remaining on the stack. See the example that follows.

Stacked cursors are typically used in application programs which traverse a tree structure stored in the database.

For example (this is a simplified variant of the "parts explosion" problem), traverse a tree structure and print out the leaf nodes:



 procedure TRAVERSE;
 integer CSTACK, LASTC;
 EXEC SQL BEGIN DECLARE SECTION;
    integer PARENT, CHILD;
    string SQLSTATE(5);
 EXEC SQL END DECLARE SECTION;
 begin
    EXEC SQL DECLARE c_tree REOPENABLE CURSOR FOR
                            SELECT parent, child
                               FROM tree
                               WHERE parent = :PARENT;
 
    CSTACK := 1;
    LASTC := 1;
    PARENT := 1;                      -- Start at root node
 
    EXEC SQL OPEN CTREE;
 
    loop
       EXEC SQL FETCH c_tree
                   INTO :PARENT, :CHILD;
 
       if SQLSTATE = "02000" then     -- No more children
          EXEC SQL CLOSE c_tree;      -- Pop the parent
          CSTACK := CSTACK - 1;
          exit when CSTACK = 0;
          if CSTACK >= LASTC then
             print(PARENT);           -- Write leaf node
          end if;
          LASTC := CSTACK;
       else                           -- Step to next level
          PARENT := CHILD;
          EXEC SQL OPEN c_tree;       -- Stack the current parent
                                         and open new level
          CSTACK := CSTACK + 1;
       end if;
    end loop;
 end TRAVERSE;
 

The counters CSTACK and LASTC keep track of the number of stacked cursor levels and the latest level in the tree hierarchy respectively.

Entering Data into Tables

The following sections explain how to perform cursor-independent operations and update and delete using cursors.

Cursor-independent Operations

The SQL statements CALL, INSERT, DELETE and UPDATE, as well user-defined function or method invocations, embedded in application programs operate on a set of rows in a table or view in exactly the same way as in interactive SQL.

Host variables may be used in the statements to supply values or set search conditions. Host variables may be used as routine parameters.

Examples:

 EXEC SQL INSERT INTO mimer_store.items(item_id,
                                        product_id, format_id,
                                        release_date,
                                        price, stock, reorder_level,
                                        ean_code,
                                        producer_id)
                   VALUES (CURRENT VALUE FOR mimer_store.item_id_seq,
                           :product_id, :format_id,
                           mimer_store.cast_to_date(:book_release_date),
                           :book_price, :book_stock, :book_reorder_level,
                           (:ean * 10) + mimer_store.ean_check_digit(:ean),
                           producer_id);
 

From the standpoint of the application program, each statement is a single indivisible operation, regardless of how many columns and rows are affected.

Updating and Deleting Through Cursors

The UPDATE CURRENT and DELETE CURRENT statements, see the Mimer SQL Reference Manual, SQL Statement Descriptions, for the syntax description, allow update and delete operations respectively, to be controlled on a row-by-row basis from an application program. These statements operate through cursors, which are declared and opened as described above for FETCH.

These statements operate on the current row of the cursor referenced in the statement. If there is no current row, e.g. the cursor has been opened but not yet positioned with a FETCH statement, an error is raised.

UPDATE CURRENT changes the content of the current row according to the SET clause in the statement, but does not change the position of the cursor. Two consecutive UPDATE CURRENT statements will therefore update the same row twice.

DELETE CURRENT deletes the current row and does not move the cursor; after a DELETE CURRENT statement, the cursor is positioned between rows and there is no current row. The cursor must be moved to the next row with a FETCH statement before any other operation can be performed through the cursor.

For both UPDATE CURRENT and DELETE CURRENT statements, the table name as used in the statement must be exactly the same as the table name addressed in the cursor declaration. The cursor must also address an updatable result set.

If a FOR UPDATE OF clause is used to specify which fetched columns may be updated, only the columns specified may appear in the corresponding UPDATE statement.

UPDATE CURRENT and DELETE CURRENT changes for a particular cursor can be divided into several transactions if the cursor is a holdable cursor. A cursor declared WITH HOLD remains open when transactions are committed, which makes it possible to use the same cursor for fetch and update of additional rows after COMMIT. However, each row must still be fetched and updated (or deleted) in the same transaction.

Cursors are not updatable if the data retrieval statement in the cursor declaration contains any of the following features at the top level (i.e. not in a subselect) of the statement:

When to Use UPDATE CURRENT, DELETE CURRENT

UPDATE CURRENT and DELETE CURRENT statements are useful for manipulating single rows in interactive applications where rows are displayed, and the user decides which rows to delete or update.

The example below illustrates the program framework for such an operation (the construction is similar for a DELETE CURRENT operation):

 ...
 EXEC SQL DECLARE c_1 CURSOR FOR ... ;
 ...
 EXEC SQL OPEN c_1;
 EXEC SQL WHENEVER NOT FOUND GOTO done;
 loop
    EXEC SQL FETCH c_1
                INTO :VAR1, :VAR2, ..., :VARn;
 
    display VAR1, VAR2, ..., VARn;
    prompt "Update this row?";
    if ANSWER = "YES" then
       prompt "Give new values";
       EXEC SQL UPDATE tab
                   SET col1 = :NEWVAL1,
                       col2 = :NEWVAL2, ...
                   WHERE CURRENT OF c_1;
       display "Row updated";
    end if;
 
    prompt "Display next row?";
    exit when ANSWER = "NO";
 end loop;
 
 done:
 EXEC SQL CLOSE c_1;
 

In situations where there is no requirement to interactively choose rows and where all the rows to be updated or deleted can be specified completely in terms of a WHERE clause, it is more efficient to do so rather than use a cursor.

An operation completely specified as a WHERE clause is executed as a single statement, rather than a series of statements (i.e. one for each FETCH etc.).


Mimer
Mimer Information Technology AB
Voice: +46 18 780 92 00
Fax: +46 18 780 92 40
info@mimer.se
Mimer SQL Documentation TOC PREV NEXT INDEX