http://developer.mimer.com

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.

For example:

 EXEC SQL DECLARE c_1 CURSOR FOR SELECT ... 
                                    FROM a, b
                                    WHERE 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 SQL optimizer gets the full information about the query that it is supposed to return a result set for. In this way the optimizer can make more use of statistical information and it can thereby optimize the query to execute in a more efficient way.
• The application will require less resources in the form of open cursors.
• If the application is run in a client/server environment, the second alternative will cause more communication over the network, since it will send data over the net which is only used to determine which data from the second cursor that will be selected and is of no real interest to the application.
• The application will be more compact as well as easier to understand and maintain.

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.

Upright Database Technology AB Voice: +46 18 780 92 00 Fax: +46 18 780 92 40 dbtechnology@upright.se