www.mimer.com/developer

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.

See Application Program Examples for a complete example of how to use dynamic SQL in the C language.

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
    RESULT_SET:= TRUE;
    --
    -- allocate cursor for SELECT statements
    --
    exec sql ALLOCATE 'C1' CURSOR FOR 'OBJECT';
 end if;
 --
 exec sql DESCRIBE INPUT 'OBJECT' USING SQL DESCRIPTOR 'SQLA2';
 exec sql GET DESCRIPTOR 'SQLA2' :NO_IN = COUNT;
 if NO_IN = 0 then
    USERVAR := FALSE;
 else
    USERVAR := TRUE;
 end if;
 -- execute statement or open cursor and fetch after assigning
 -- values to input variables
 --
 if RESULT_SET then
    if USERVAR then
       exec sql OPEN 'C1' USING SQL DESCRIPTOR 'SQLA2';
    else
       exec sql OPEN 'C1';
    end if;
    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
    if USERVAR then
       exec sql EXECUTE 'OBJECT' USING SQL DESCRIPTOR 'SQLA2';
    else
       exec sql EXECUTE 'OBJECT';
    end if;
 end if;
 exec sql DEALLOCATE PREPARE 'OBJECT';
 

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

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