Mimer SQL Programmer's Manual TOC PREV NEXT INDEX

Mimer Developer Site

www.mimer.com/developer


Retrieving Data from Multiple Tables


Data can be retrieved from multiple tables in embedded SQL 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 C1 CURSOR FOR SELECT ... FROM A, B
          WHERE A.X=B.Y;
 exec sql OPEN C1;
 ...
 

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 C1 CURSOR FOR SELECT X FROM A;
 exec sql DECLARE C2 CURSOR FOR SELECT ... FROM B WHERE Y=:hostx;
 
 exec sql OPEN C1;
 exec sql FETCH C1 INTO :hostx;
 exec sql CLOSE C1;
 
 exec sql OPEN C2;
 exec sql FETCH C2;
 ...
 

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 CTREE 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 CTREE INTO :PARENT,:CHILD;
             if SQLSTATE = "02000" then   -- no more children
                 exec sql CLOSE CTREE;    -- 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 CTREE;     -- 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
Mimer SQL Programmer's Manual TOC PREV NEXT INDEX