SQL Reference

ASSOCIATE LOCATORS Statement

The ASSOCIATE LOCATORS statement gets the result set locator value for each result set returned by a stored procedure.

Syntax

              .-RESULT SET--.
>>-ASSOCIATE--+-------------+---+-LOCATOR--+-------------------->
                                '-LOCATORS-'
 
        .-,----------------------.
        V                        |
>----(-----rs-locator-variable---+---)--WITH PROCEDURE--procedure-name->
 
>--------------------------------------------------------------><
 

Description

rs-locator-variable

Specifies a result set locator variable that has been declared in a compound statement.

WITH PROCEDURE

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

procedure-name

A procedure name is a qualified or unqualified name. Each part of the name must be composed of SBCS characters.

A fully qualified procedure name is a two-part name. The first part is an identifier that contains the schema name of the stored procedure. The last part is an identifier that contains the name of the stored 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 is 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 below.

When the ASSOCIATE LOCATORS statement is executed, the procedure name or specification must identify a stored 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.

Rules

• More than one locator can be assigned to a result set. You can issue the same ASSOCIATE LOCATORS statement more than once with different result set locator variables.
• 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 stored 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 stored procedure, the extra variables are assigned a value of 0.
• If a stored procedure is called more than once from the same caller, only the most recent result sets are accessible.

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 stored procedure P1. Assume that the stored 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 stored procedure to ensure that stored procedure P1 in schema MYSCHEMA is used.

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