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 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 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 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