SQL/MX Programming Manual for Java

SQL/MX Programming Considerations
HP NonStop SQL/MX Programming Manual for Java—523726-003
4-10
Placement of the CONTROL Statements
Placement of the CONTROL Statements
Place CONTROL statements anywhere an executable statement is allowed in an SQLJ
program. You cannot place these statements at the top-level scope of a program where
you code class declarations. If you associate a connection context with a CONTROL
statement, place the CONTROL statement after the instantiation of the connection
context object.
To anticipate either static or dynamic behavior, use these approaches for coding
CONTROL statements:
•
Place all CONTROL statements immediately before the associated statements
without intervening control flow logic.
•
Enclose each SQL statement that requires specific settings with the appropriate
CONTROL statements. For example:
try {
try {
#sql [ctx] {CONTROL QUERY DEFAULT
attribute 'ON'};
#sql [ctx] {CONTROL QUERY SHAPE
shape};
#sql [ctx] {CONTROL TABLE
table control-table-option};
#sql [ctx] { /* SQL statement */ };
System.out.println("Successful operation");
}
finally {
#sql [ctx] {CONTROL QUERY DEFAULT * RESET};
#sql [ctx] {CONTROL QUERY SHAPE OFF};
#sql [ctx] {CONTROL TABLE * RESET};
}
}
catch (SQLException se) {
System.out.println("Exception: " + se.getMessage());
}
By putting the second set of CONTROL statements in the finally block of the inner
try block, you ensure that these CONTROL statements, which reset and turn off the
settings of the previous CONTROL statements, will be executed even if an exception is
thrown from the inner block. That way, the CONTROL settings are restored for
subsequent SQL statements, including ones that execute dynamically.
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.