http://developer.mimer.com

Example Framework for Dynamic SQL Programs

---

This section gives a general framework (in pseudo code) for dynamic SQL programs designed to handle any valid SQL statement as input. The framework is largely a synthesis of the example fragments given earlier in this chapter.

The framework is written as a single sequential module to emphasize the order of operations.

Host variable declarations are omitted. Handling of values returned by FETCH is also omitted.

Example Framework

 -- Allocate two descriptor areas
 EXEC SQL ALLOCATE DESCRIPTOR 'SQLA1' WITH MAX 50;
 EXEC SQL ALLOCATE DESCRIPTOR 'SQLA2' WITH MAX 50;
 
 -- read statement from terminal
 read INPUT into SOURCE;
 
 -- prepare statement
 EXEC SQL PREPARE 'OBJECT' FROM :SOURCE;
 
 -- describe statement and set type/parameter usage flags
 EXEC SQL DESCRIBE OUTPUT 'OBJECT' USING SQL DESCRIPTOR 'SQLA1';
 EXEC SQL GET DESCRIPTOR 'SQLA1' :NO_OUT = COUNT;
 if NO_OUT = 0 then
    RESULT_SET := FALSE;
 else
    -- allocate cursor for SELECT statements
    EXEC SQL ALLOCATE 'C1' CURSOR FOR 'OBJECT';
    RESULT_SET:= TRUE;
 end if;
 
 EXEC SQL DESCRIBE INPUT 'OBJECT' USING SQL DESCRIPTOR 'SQLA2';
 EXEC SQL GET DESCRIPTOR 'SQLA2' :NO_IN = COUNT;
 -- execute statement or open cursor and fetch after assigning
 -- values to input variables
 if RESULT_SET then
    EXEC SQL OPEN 'C1' USING SQL DESCRIPTOR 'SQLA2';
    loop
       EXEC SQL FETCH 'C1' INTO SQL DESCRIPTOR 'SQLA1';
       exit when NO_MORE_REQUIRED or SQLSTATE = "02000";
       ... -- process results of FETCH
    end loop;
 
    EXEC SQL CLOSE 'C1';
 else
    EXEC SQL EXECUTE 'OBJECT' USING SQL DESCRIPTOR 'SQLA2';
 end if;
 
 EXEC SQL DEALLOCATE PREPARE 'OBJECT';
 

Note: Features that are specific to real host languages are described in Host Language Dependent Aspects.

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