ASSOCIATE LOCATORS statement

Invocation

This statement can only be embedded in an SQL procedure. It is not an executable statement and cannot be dynamically prepared.

Authorization

None required.

Syntax

Description

rs-locator-variable

Specifies a result set locator variable that has been declared in a compound SQL (Procedure) statement.

WITH PROCEDURE

Identifies the procedure that returns result set locators by the specified procedure name.

procedure-name

A procedure name is a qualified or unqualified name.

A fully qualified procedure name is a two-part name. The first part is an identifier that contains the schema name of the procedure. The last part is an identifier that contains the name of the procedure. A period must separate each of the parts. Any or all of the parts can be a delimited identifier.

If the procedure name is unqualified, it has only one name because the implicit schema name is not added as a qualifier to the procedure name. Successful execution of the ASSOCIATE LOCATOR statement only requires that the unqualified procedure name in the statement be the same as the procedure name in the most recently executed CALL statement that was specified with an unqualified procedure name. The implicit schema name for the unqualified name in the CALL statement is not considered in the match. The rules for how the procedure name must be specified are described in the following paragraph.

When the ASSOCIATE LOCATORS statement is executed, the procedure name or specification must identify a procedure that the requester has already invoked using the CALL statement. The procedure name in the ASSOCIATE LOCATORS statement must be specified the same way that it was specified on the CALL statement. For example, if a two-part name was specified on the CALL statement, you must use a two-part name in the ASSOCIATE LOCATORS statement.

Notes

• If the number of result set locator variables that are listed in the ASSOCIATE LOCATORS statement is less than the number of locators returned by the procedure, all variables in the statement are assigned a value, and a warning is issued.
• If the number of result set locator variables that are listed in the ASSOCIATE LOCATORS statement is greater than the number of locators returned by the procedure, the extra variables are assigned a value of 0.
• If a procedure is called more than once from the same caller, only the most recent result sets are accessible.
• Result set locator values are available for a procedure that is called using an EXECUTE statement executing the CALL statement that was previously prepared by the PREPARE statement. Result set locator values, however, are not available for a procedure that is called using an EXECUTE IMMEDIATE statement.
• Module-procedure names referenced in an ASSOCIATE LOCATORS statement can only be 1-part or 2-part qualified name references. A 3-part name reference is not allowed (SQLSTATE 42601). Any CALL statement that references a module-procedure that was referenced in an ASSOCIATE LOCATORS statement, must specify the module-procedure with the same 1-part or 2-part qualified name used in the ASSOCIATE LOCATORS statement.

Examples

The statements in the following examples are assumed to be embedded in SQL Procedures.

• Example 1: Use result set locator variables LOC1 and LOC2 to get the result set locator values for the two result sets returned by procedure P1. Assume that the procedure is called with a one-part name.

     CALL P1;
     ASSOCIATE RESULT SET LOCATORS (LOC1, LOC2)
       WITH PROCEDURE P1; 

• Example 2: Repeat the scenario in Example 1, but use a two-part name to specify an explicit schema name for the procedure to ensure that procedure P1 in schema MYSCHEMA is used.

     CALL MYSCHEMA.P1;
     ASSOCIATE RESULT SET LOCATORS (LOC1, LOC2)
       WITH PROCEDURE MYSCHEMA.P1;