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.
- Access control statements:
- Data definition statements:
- Security control statements:
- Transaction control statements:
- Data manipulation statements:
- System administration statements:
Statements may be submitted to dynamic SQL applications in two forms:
- Fully defined statements, written exactly as they would be submitted to interactive SQL. For example:GRANT SELECT ON mimer_store_book.details TO mimer_admin_group SELECT code, country FROM mimer_store.countries
- Statements with parameter markers, which identify positions where the value of a host variable will be inserted when the statement is executed or the cursor is opened. A parameter marker is represented by a question mark
?. For example: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:
Allocate extended cursor. Allocate SQL descriptor area. Close an open cursor. Deallocate SQL descriptor area. Deallocate prepared SQL statement. Declare a cursor for a statement which will be dynamically submitted. Examine the object form of the statement and assign values to the appropriate parameters in the SQL descriptor area. Execute a prepared statement (except result set generating statements). 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 rows for a dynamic cursor. Get values from the SQL descriptor area. Open a prepared cursor. Compile an SQL source statement into an internal object form. Set values in the SQL descriptor area.
All statements submitted to dynamic SQL programs must be prepared.
All prepared statements and singleton
SELECTstatements, where the result set contains only one row, are executed with the
SELECTstatements and calls to result set procedures are executed using
FETCHfor a cursor declared with the prepared statement.
The declaration of a cursor for a statement,
DECLARE CURSOR, must always precede the
PREPAREoperation 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
An SQL descriptor area is allocated with the ESQL statement
ALLOCATE DESCRIPTORand 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:
SELECTstatements and calls to result set procedures
CALLstatements using parameter markers
The following statement types do not use SQL descriptor areas:
- all data definition statements, security control statements, access control statements (except
ENTER) and transaction control statements
CALLstatements using only constant expressions.
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_COUNTis the number of parameters in the descriptor and
COUNTis 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
TOP_LEVEL_COUNTwill be the same.
The individual fields of the item descriptor area can be accessed with the
SET DESCRIPTORstatements. Each descriptor item contains fields for data type, size and scale. The complete list of fields can be seen at the
DESCRIPTORstatements 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_COUNTvalue of 3, and the value of
COUNTwill 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
TOP_LEVEL_COUNTwill have the same value of 3.
When using the
GET DESCRIPTORstatement to get information about different items from an SQL descriptor, an increased value of
LEVELindicates that an item is an attribute for a structured user-defined type. If iterating through all item descriptors for this example the values of
LEVELwould 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
All statements submitted to dynamic SQL programs must be prepared. The simplest form of the operation uses a
PREPAREstatement, see the Mimer SQL Reference Manual, PREPARE, for the syntax description. The operation may also be combined with
EXECUTEas a simple statement in the shorthand form
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 SQLnor 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 CURSORstatement, 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 PREPAREstatement, see the Mimer SQL Reference Manual, DEALLOCATE PREPARE, for the syntax description.
DEALLOCATE PREPAREalso 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
Both forms of
DESCRIBEuse 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'; ...
DESCRIBEplaces 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 DESCRIPTORstatement and updated with the
Describing Output Variables
The items in the result set for a statement are described with the
DESCRIBE OUTPUTstatement. The keyword
OUTPUTmay be omitted.
DESCRIBE OUTPUTstatement shows:
- whether the statement returns a result set or not. This is indicated by the value of the
COUNTfield of the SQL descriptor area which is set to zero for statements that do not return a result set. Statements that return a result set are calls to result set procedures, see Result Set Procedures, and select-expressions (refer to the Mimer SQL Reference Manual, SELECT).
- dynamic SQL programs must test for this after each
DESCRIBEoperation because the treatment of statements that return result sets differs from the treatment of those that do not, see Handling Prepared Statements. If the statement returns a result set, the
DESCRIBEstatement will place information about the items in the result set in the fields of the descriptor area.
- whether the current descriptor area allocation is sufficient or not. Insufficient area is indicated by the
SQLSTATEvariable set to a warning state and a value of
COUNT(required number of items) greater than that specified in the
WITH MAX ...clause of the
ALLOCATE DESCRIPTORstatement, or greater than 100 if no
WITH MAX ...clause was specified. If the area is insufficient, no items are described.
Describing Input Variables
DESCRIBE INPUTstatement is used to describe parameter markers.
The value of the
COUNTfield 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
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
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 are executed using the
EXECUTEstatement, with the object (prepared) form of the submitted statement as the argument.
- Result set statements, a cursor is used for these statements, associated with the object form of the prepared statement and are executed with
Executable statements are identified by a value of zero in the
COUNTfield of the SQL descriptor area after a
DESCRIBE OUTPUTstatement. 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
OUTparameters when a call statement is prepared dynamically.
The descriptor areas referenced in the
EXECUTEstatement 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
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.
The shorthand form
EXECUTE IMMEDIATEcombines the functions of
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 IMMEDIATEcan therefore be useful for application programs designed specifically to handle database definition statements).
Examplesprintf(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
COUNTfield of the SQL descriptor area after
SELECTstatements 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
OPENstatement 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
OPENstatement 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
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
FETCHis 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 Information Technology AB
Voice: +46 18 780 92 00
Fax: +46 18 780 92 40