Mimer SQL Documentation TOC PREV NEXT INDEX

Mimer SQL Developer Site


Dynamic SQL


This section discusses the principles of dynamic SQL, processing dynamic SQL, the descriptor area, preparing statements, extended dynamic cursors and prepared statements.

Principles of Dynamic SQL

Dynamic SQL enables you to execute SQL statements placed in a string variable instead of explicitly writing the statements inside a program. This allows SQL statements to be constructed within an application program. These facilities are typically used in interactive environments, where SQL statements are submitted to the application program from the terminal.

An example of when dynamic SQL is needed would be a program for interactive SQL, where any correct SQL statement may be entered at the terminal and processed by the application. Limited dynamic facilities may however be provided by relatively simple application programs.

SQL Statements and Dynamic SQL

The following classes of SQL statements may be submitted to programs using dynamic SQL. Statements excluded from dynamic applications are declarations, diagnostic statements and dynamic SQL statements themselves.

Submitting Statements

Statements may be submitted to dynamic SQL applications in two forms:

 GRANT SELECT ON mimer_store_book.details TO mimer_admin_group
 
 SELECT code, country
    FROM mimer_store.countries
 
 UPDATE mimer_store.currencies
    SET exchange_rate = ?
    WHERE code = ?
 
 DELETE FROM countries
    WHERE code = ?
 
 SELECT currency, exchange_rate * ?
    FROM mimer_store.currencies
    WHERE code IN (SELECT currency_code
                      FROM mimer_store.countries
                      WHERE code LIKE '%' || ? || '%')
 

Statements submitted with parameter markers are equivalent to normal embedded statements using host variables, except that the statements are defined at run-time.

General Summary of Dynamic SQL Processing

The following statements are used when SQL statements are dynamically submitted:

Statement
Description
 ALLOCATE CURSOR
Allocate extended cursor.
 ALLOCATE DESCRIPTOR
Allocate SQL descriptor area.
 CLOSE
Close an open cursor.
 DEALLOCATE DESCRIPTOR
Deallocate SQL descriptor area.
 DEALLOCATE PREPARE
Deallocate prepared SQL statement.
 DECLARE CURSOR
Declare a cursor for a statement which will be dynamically submitted.
 DESCRIBE
Examine the object form of the statement and assign values to the appropriate parameters in the SQL descriptor area.
 EXECUTE
Execute a prepared statement (except result set generating statements).
 EXECUTE IMMEDIATE
Shorthand form for PREPARE followed by EXECUTE. This form can only be used for fully-defined non-result set statements with no parameter markers.
 FETCH
Fetch rows for a dynamic cursor.
 GET DESCRIPTOR
Get values from the SQL descriptor area.
 OPEN
Open a prepared cursor.
 PREPARE
Compile an SQL source statement into an internal object form.
 SET DESCRIPTOR
Set values in the SQL descriptor area.

All statements submitted to dynamic SQL programs must be prepared.

All prepared statements and singleton SELECT statements, where the result set contains only one row, are executed with the EXECUTE statement.

All other SELECT statements and calls to result set procedures are executed using OPEN and FETCH for a cursor declared with the prepared statement.

The declaration of a cursor for a statement, DECLARE CURSOR, must always precede the PREPARE operation for the same statement in an application using dynamic SQL.

SQL Descriptor Area

The SQL descriptor area is used for managing input and output data in dynamically submitted SQL statements containing parameter markers, and for managing result sets (e.g. returned by a SELECT statement.)

An SQL descriptor area is allocated with the ESQL statement ALLOCATE DESCRIPTOR and deallocated with DEALLOCATE DESCRIPTOR. See the Mimer SQL Reference Manual, SQL Statement Descriptions, for more information.

A program may allocate several separate descriptor areas, identified by different descriptor names. Normally one descriptor is used for input data and one for output data. The describe statement is used to populate an SQL descriptor. Describe can be used with or without nesting. If with nesting is specified, information about attributes for structured user-defined types will be included in the description.

The following statement types can use information from SQL descriptor areas:

The following statement types do not use SQL descriptor areas:

In practice, programs using dynamically submitted SQL statements are usually written as though all submitted statements use SQL descriptor areas (since the nature of the submitted statement is not known until run-time).

SQL descriptor areas can be left out of a program only if it is known in advance that they will not be needed (for instance in an application program which will handle only submitted data definition statements).

The Structure of the SQL Descriptor Area

The SQL descriptor area is a storage area holding information about the described statement. It is allocated and maintained with ESQL statements.

It consists of a descriptor header and one or more item descriptor areas. The descriptor header contains two fields, TOP_LEVEL_COUNT and COUNT.

TOP_LEVEL_COUNT is the number of parameters in the descriptor and COUNT is the number of item areas. Parameters that are defined as user defined structured types will have multiple item descriptors, one for the actual type and one for each attribute. If there are no user-defined types present the values of COUNT and TOP_LEVEL_COUNT will be the same.

The individual fields of the item descriptor area can be accessed with the GET DESCRIPTOR and SET DESCRIPTOR statements. Each descriptor item contains fields for data type, size and scale. The complete list of fields can be seen at the GET and SET DESCRIPTOR statements in the SQL Reference Manual.

Using structured user-defined types in dynamic SQL

In this example the following definitions are assumed:

 create type address as(street nchar varying(20), number int,
                        zipCode char(8), city nchar varying(30));
 
 create type person as (firstName nchar varying(30),
                        lastName nchar varying(30),
                        address address,
                        dateOfBirth date);
 
 create table persons (ssn int primary key, person person);
 

If a statement such as:

 SELECT ssn, person, (current_date - person.dateOfBirth()) year(3) as age
 FROM persons;
 

is prepared and described using the with nesting option, the output SQL descriptor will have a TOP_LEVEL_COUNT value of 3, and the value of COUNT will be 11. If the describe statement is without nesting only the top level items will be included in the SQL descriptor. In that case the values of COUNT and TOP_LEVEL_COUNT will have the same value of 3.

When using the GET DESCRIPTOR statement to get information about different items from an SQL descriptor, an increased value of LEVEL indicates that an item is an attribute for a structured user-defined type. If iterating through all item descriptors for this example the values of TYPE and LEVEL would be:

 ITEM  NAME                    TYPE                    LEVEL
 1     ssn                     4 (int)                 1
 2     person                  17 (user-defined)       1
 3     firstName               -9 (nchar varying)      2
 4     lastName                -9 (nchar varying)      2
 5     adress                  17 (user-defined)       2
 6     street                  -9 (nchar varying)      3
 7     number                  4 (int)                 3
 8     zipCode                 1 (char)                3
 9     city                    -9 (nchar varying)      3
 10    dateOfBirth             9 (datetime)            2
 11    age                     9 (datetime)            1

Preparing Statements

All statements submitted to dynamic SQL programs must be prepared. The simplest form of the operation uses a PREPARE statement, see the Mimer SQL Reference Manual, PREPARE, for the syntax description. The operation may also be combined with EXECUTE as a simple statement in the shorthand form EXECUTE IMMEDIATE.

The source form of the statement must be contained in a host variable, containing the statement string. (The statement string itself is not preceded by EXEC SQL nor terminated by the language-specific embedded delimiter.)

The prepared form of the statement is named by an SQL-identifier or a host variable, for extended statements, see Extended Dynamic Cursors.

In the following example the source form of the statement is given as a string constant for illustrative purposes, however, the statement would usually be read from some input source, e.g. the terminal, at run-time:

 ...
 EXEC SQL BEGIN DECLARE SECTION;
    string SQL_TXT(255);
    ...
 EXEC SQL END DECLARE SECTION;
 ...
 
 SQL_TXT := "CREATE INDEX pdt_product_search
                ON products(product_search)";
 EXEC SQL PREPARE OBJECT FROM :SQL_TXT;
 ...

Extended Dynamic Cursors

A typical cursor is identified by an SQL identifier. An extended cursor makes it possible to represent a dynamic cursor by a host variable or a literal. An extended cursor is allocated by the application with the ALLOCATE CURSOR statement, see the Mimer SQL Reference Manual, ALLOCATE CURSOR, for the syntax description.

When the application is finished with the processing of the SQL statement, the prepared statement may be destroyed by executing the DEALLOCATE PREPARE statement, see the Mimer SQL Reference Manual, DEALLOCATE PREPARE, for the syntax description. DEALLOCATE PREPARE also destroys any extended cursor that was associated with the statement.

Example of how extended cursors are used:

 ...
 EXEC SQL BEGIN DECLARE SECTION;
    string SQL_TXT(255);
    string C1(128);
    string STM1(128);
    integer HOSTVAR1;
    string HOSTVAR2(10);
    ...
 EXEC SQL END DECLARE SECTION;
 ...
 
 SQL_TXT := "SELECT col1, col2
                FROM tab1";
 STM1 := "STMT_1";
 EXEC SQL PREPARE :STM1 FROM :SQL_TXT;
 
 C1 := "CUR_1";
 EXEC SQL ALLOCATE :C1 CURSOR FOR :STM1;
 ...
 
 EXEC SQL ALLOCATE DESCRIPTOR 'RESDESC' WITH MAX 50;
 EXEC SQL DESCRIBE OUTPUT :STM1 USING SQL DESCRIPTOR 'RESDESC';
 ...
 EXEC SQL OPEN :C1;
 EXEC SQL WHENEVER NOT FOUND GOTO done;
 
 loop
    EXEC SQL FETCH :C1
                INTO SQL DESCRIPTOR 'RESDESC';
    EXEC SQL GET DESCRIPTOR 'RESDESC' VALUE 1 :HOSTVAR1 = DATA;
    EXEC SQL GET DESCRIPTOR 'RESDESC' VALUE 2 :HOSTVAR2 = DATA;
    ...
    display HOSTVAR1, HOSTVAR2, ...;
 end loop;
 
 done:
 EXEC SQL CLOSE :C1;
 EXEC SQL DEALLOCATE DESCRIPTOR 'RESDESC';
 EXEC SQL DEALLOCATE PREPARE :STM1;
 ...

Describing Prepared Statements

Statements returning a result set and statements containing parameter markers can be described to obtain information about the number and data types of the parameters.

There are two forms of DESCRIBE:

Both forms of DESCRIBE use the object (prepared) form of the statement as an argument. The same statement may be described in both senses if necessary.

For example:

 EXEC SQL BEGIN DECLARE SECTION;
    string SQLA1(128);
    integer MAXOCC;
    string SOURCE(255);
 EXEC SQL END DECLARE SECTION;
 ...
 
 MAXOCC := 15;
 SQLA1 := "SQL_AREA_1";
 EXEC SQL ALLOCATE DESCRIPTOR :SQLA1 WITH MAX 20;
 EXEC SQL ALLOCATE DESCRIPTOR 'SQLA2' WITH MAX :MAXOCC;
 ...
 
 EXEC SQL PREPARE 'OBJECT' FROM :SOURCE;
 EXEC SQL DESCRIBE OUTPUT 'OBJECT' USING SQL DESCRIPTOR :SQLA1;
 EXEC SQL DESCRIBE INPUT 'OBJECT' USING SQL DESCRIPTOR 'SQLA2';
 ...
 

DESCRIBE places information about the prepared statement in the SQL descriptor areas. See SQL Descriptor Area for a description of the SQL descriptor area.

The contents of the SQL descriptor area is read with the GET DESCRIPTOR statement and updated with the SET DESCRIPTOR statement.

Describing Output Variables

The items in the result set for a statement are described with the DESCRIBE OUTPUT statement. The keyword OUTPUT may be omitted.

The DESCRIBE OUTPUT statement shows:

Describing Input Variables

The DESCRIBE INPUT statement is used to describe parameter markers.

The value of the COUNT field of the SQL descriptor area indicates the number of parameter markers in the statement (a value of zero indicates no input parameters). A value greater than that specified in WITH MAX ... indicates that the allocated SQL descriptor area is too small and the describe operation will not be performed. This situation is handled as described above for DESCRIBE OUTPUT.

Note: If the prepared statement is a call to a stored procedure that uses parameter markers, these will be described by the DESCRIBE INPUT statement. This is regardless of how the formal parameter is specified in the procedure definition. Whether the parameter is IN, INOUT or OUT can be seen from the PARAMETER_MODE field in the descriptor area.

Handling Prepared Statements

After PREPARE and DESCRIBE, the way in which submitted statements are handled differs according to whether the statement is executable or whether it returns a result set.

Executable Statements

Executable statements are identified by a value of zero in the COUNT field of the SQL descriptor area after a DESCRIBE OUTPUT statement. If the statement does not contain any parameter markers, it may be executed directly.

If, on the other hand, the statement contains parameter markers, the statement must be executed with an SQL descriptor area for input and output values.

Note: All parameter markers used in a call statement are described with the DESCRIBE INPUT statement, regardless of the mode of the formal parameter.

Parameter markers must be used for all INOUT or OUT parameters when a call statement is prepared dynamically.

The descriptor areas referenced in the EXECUTE statement may be replaced by explicit lists of host variables, provided that the number and data types of the user variables in the source statement are known when the program is written (so that variables can be declared and the appropriate variable list written into the EXECUTE statement).

This facility is of limited use, since the occasions when the user constructs freely chosen SQL statements with a predetermined number of user variables are rare.

EXECUTE IMMEDIATE

The shorthand form EXECUTE IMMEDIATE combines the functions of PREPARE and EXECUTE. This form may only be used for executable statements known to have no parameter markers and is therefore of value only in contexts where the user is restricted to this type of statement. (Data definition and security control statements fall into this category, since user variables are not permitted in the syntax of these statements. EXECUTE IMMEDIATE can therefore be useful for application programs designed specifically to handle database definition statements).

Example
 sprintf(ddlstr, "drop ident %s cascade", str);
 exec sql EXECUTE IMMEDIATE :ddlstr;

Result Set Statements

Statements returning a result set are identified by a non-zero value in the COUNT field of the SQL descriptor area after DESCRIBE OUTPUT.

Dynamically submitted SELECT statements and calls to result set procedures are handled through cursors. Cursors are declared or allocated for the object (prepared) form of submitted result set returning statements.

Note: A DECLARE CURSOR statement must precede the PREPARE statement in the program code. If ALLOCATE CURSOR is used instead of DECLARE CURSOR, the statement must have been prepared before the cursor can be allocated. The SQL statement must also be prepared before the cursor is opened.

If the source form of the result set returning statement contains parameter markers, these must be described before the cursor is opened and the OPEN statement must reference the relevant descriptor area. In the rare case where the number and data type of the user variables are known when the program is first written, the OPEN statement may reference an explicit variable list instead of a descriptor area.

The descriptor area used for the submitted result set returning statement is referenced when data is retrieved with the FETCH statement.

Example:
 ...
 EXEC SQL ALLOCATE DESCRIPTOR 'SQLA1' WITH MAX 30;
 EXEC SQL ALLOCATE DESCRIPTOR 'SQLA2' WITH MAX 30;
 ...
 
 EXEC SQL PREPARE 'OBJECT' FROM :SOURCE;
 ...
 
 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
    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;
 ...
 
 if RESULT_SET then
    EXEC SQL OPEN 'C1' USING SQL DESCRIPTOR 'SQLA2';
    ...
    EXEC SQL FETCH 'C1' INTO SQL DESCRIPTOR 'SQLA1';
    ...
 else
    ...
 end if;

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 SQL 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 result set
    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.

Mimer
Mimer Information Technology AB
Voice: +46 18 780 92 00
Fax: +46 18 780 92 40
info@mimer.se
Mimer SQL Documentation TOC PREV NEXT INDEX