Using Dynamic SQL › Dynamic SQL

Dynamic SQL

Depending on the processing requirement of the program and the capabilities of the programming language, you will need to implement dynamic SQL.

Dynamic SQL refers to an SQL statement that is not known to the program at precompile time and therefore is compiled dynamically when the program executes. CA IDMS provides dynamic SQL to allow the program to formulate, compile, and execute a DML statement at runtime.

To Insert, Update, or Delete

You implement dynamic SQL with a small set of SQL statements. For SQL DML other than SELECT or CALL, these statements are:

• EXECUTE IMMEDIATE—Dynamically compiles and executes the statement
• PREPARE—Dynamically compiles the statement
• EXECUTE—Executes a prepared statement

If the statement to be dynamically compiled could be issued more than once in the program, you should use the combination of PREPARE and EXECUTE statements.

To Select

To dynamically compile and execute a SELECT statement, you take these steps:

1. Formulate the statement
2. Prepare the statement and optionally describe the result table to CA IDMS
3. Declare or allocate a cursor using the dynamically compiled SELECT statement

To CALL an SQL Invoked Procedure

To dynamically compile and execute a CALL statement, you take these steps:

1. Formulate the statement
2. Prepare the statement and optionally describe the result table to CA IDMS
3. Declare or allocate a cursor using the dynamically compiled CALL statement

Host Language Dependency

If the number and type of columns in a dynamic SELECT or CALL are not known at compile time, the host language must provide explicit support for dynamic storage allocation because the variable storage requirements for the data to be retrieved can be derived only from information returned to the SQLDA when the SELECT statement is prepared.

No Host Variables, Local Variables, or Routine Parameters

A dynamic SQL statement that is prepared or executed using an EXECUTE IMMEDIATE statement cannot reference host variables, local variables, or routine parameters within the text of the statement. If you want to repeatedly execute a statement, such as an UPDATE, using different update values each time, you must use dynamic parameters in place of variables or parameters.

Note: For more information about dynamic parameters, see the CA IDMS SQL Reference Guide.

Precompiling with NOINSTALL

A program that consists entirely of dynamic SQL statements, session and transaction management statements, requires no RCM. Therefore, you may precompile such a program with the NOINSTALL option. This directs the precompiler to check syntax and not to store an RCM, thus eliminating the need for updating the dictionary. If SQL requests will be issued from more than one program within a single transaction, each such program must have its RCM included in the access module being used. This requirement holds, regardless of whether all of the statements within a program are dynamic or not. As general practice, you should avoid specifying the NOINSTALL option.