SQL/MX Programming Manual for C and COBOL (G06.24+, H06.03+)

Embedded SQL Statements

HP NonStop SQL/MX Programming Manual for C and COBOL—523627-004

2-14

CONTROL, Flow Control Scope, and Dynamic SQL

programs

scope and influence that CONTROL statement directives have on dynamic and static

SQL statements.

Guidelines on the Behavior and Use of CONTROL

Statements

•

Place CONTROL statements anywhere an executable statement is allowed in a C,

C++, or COBOL program.

•

A subsequent CONTROL statement can modify the values set by a prior

CONTROL statement.

•

The CONTROL statements that are within scope stay in effect for the entire

duration of the SQL statements they influence. For example, a CONTROL TABLE

statement stays in effect until the current process terminates or until the execution

of another CONTROL statement overrides it.

•

It is good practice to issue a CONTROL QUERY SHAPE OFF statement

immediately after the execution of a query with a forced plan because the next

query might not fit the forced plan and result in an optimizer error. For more

information, see the SQL/MX Reference Manual.

Prior to entering the loop, the static statement EXEC SQL UPDATE is affected by the

preceding static CONTROL statement.

Now, consider dynamic SQL statements. The first time through the loop, the user

enters:

UPDATE sales SET SALARY = SALARY * 0.05;

Note. As a rule, dynamic CONTROL statements apply to dynamic SQL statements that are

prepared after the CONTROL statement is executed. If your program uses a PREPARE once

and EXECUTE many strategy, the program must execute the CONTROL statement prior to the

PREPARE statement. CONTROL statements that are executed after a PREPARE statement

do not apply to the prepared statements.

Example 2-1. Static and Dynamic SQL and CONTROL Scope

...

EXEC SQL CONTROL QUERY DEFAULT TABLELOCK 'ON';

EXEC SQL UPDATE sales SET SALARY = SALARY * 0.05;

...

for (i = 1; i <= 3; i++)

{

printf("Enter SQL statement: " );

scanf("%s", &sql_string);

EXEC SQL PREPARE stmt FROM :sql_string;

EXEC SQL EXECUTE stmt;

}