
The ASSOCIATE LOCATORS statement gets the result set locator value for each result set returned by a procedure.
This statement can be embedded in an application program. It is an executable statement that can be dynamically prepared. It cannot be issued interactively. It must not be specified in REXX.
None required.
.-,-------------------. .-RESULT SET-. V | >>-ASSOCIATE -+------------+--+-LOCATOR--+--(----rs-locator-variable-+--)--> '-LOCATORS-' >--WITH--+-+-+-PROCEDURE-+--procedure-name--+------------------------------+-+-+->< | | '-ROUTINE---' '-(--+--------------------+--)-' | | | | | .-,--------------. | | | | | | V | | | | | | '---parameter-type-+-' | | | '-SPECIFIC--+-PROCEDURE-+--specific-name--------------------------' | | '-ROUTINE---' | '-PROCEDURE--variable-------------------------------------------------' parameter-type |----data-type--+------------+----------------------------------| '-AS LOCATOR-' data-type |--+-built-in-type------+---------------------------------------| '-distinct-type-name-'
built-in-type |--+-+---SMALLINT---+----------------------------------------------------------------------+--| | +-+-INTEGER-+--+ | | | '-INT-----' | | | '---BIGINT-----' | | .-(5,0)------------------------. | +-+-+-DECIMAL-+-+--+------------------------------+-------------------------------------+ | | '-DEC-----' | | .-,0--------. | | | '-+-NUMERIC-+-' '-(--integer--+-----------+--)-' | | '-NUM-----' '-, integer-' | | .-(--52--)------. | +-+-FLOAT--+---------------+-+----------------------------------------------------------+ | | '-(--integer--)-' | | | +-REAL---------------------+ | | | .-PRECISION-. | | | '-DOUBLE--+-----------+----' | | .-(--34--)-. | +---DECFLOAT--+----------+--------------------------------------------------------------+ | '-(--16--)-' | | .-(--1--)-------. | +-+-+-+-CHARACTER-+--+---------------+----------+--+----------------+-----------------+-+ | | | '-CHAR------' '-(--integer--)-' | +-FOR BIT DATA---+ | | | | '-+-+-CHARACTER-+--VARYING-+--(--integer--)-' +-FOR SBCS DATA--+ | | | | | '-CHAR------' | +-FOR MIXED DATA-+ | | | | '-VARCHAR----------------' '-ccsid-clause---' | | | | .-(--1M--)-------------. | | | '---+-+-CHARACTER-+--LARGE OBJECT-+----+----------------------+--+----------------+-' | | | '-CHAR------' | '-(--integer--+---+--)-' +-FOR SBCS DATA--+ | | '-CLOB------------------------' +-K-+ +-FOR MIXED DATA-+ | | +-M-+ '-ccsid-clause---' | | '-G-' | | .-(--1--)-------. | +-+---GRAPHIC----+---------------+-------+--+--------------+----------------------------+ | | '-(--integer--)-' | '-ccsid-clause-' | | +-+-GRAPHIC VARYING-+--(--integer--)---+ | | | '-VARGRAPHIC------' | | | | .-(--1M--)-------------. | | | '---DBCLOB----+----------------------+-' | | '-(--integer--+---+--)-' | | +-K-+ | | +-M-+ | | '-G-' | | .-(--1--)-------. | +-+-+-+-NATIONAL CHARACTER-+--+---------------+----------+---------------------+--------+ | | | +-NATIONAL CHAR------+ '-(--integer--)-' | | | | | | '-NCHAR--------------' | | | | | '-+-+-NATIONAL CHARACTER-+--VARYING-+--(--integer--)-' | | | | | +-NATIONAL CHAR------+ | | | | | | '-NCHAR--------------' | | | | | '-NVARCHAR------------------------' | | | | .-(--1M--)-------------. | | | '-----+-+-NATIONAL CHARACTER-+--LARGE OBJECT-+------+----------------------+-' | | | '-NCHAR--------------' | '-(--integer--+---+--)-' | | '-NCLOB--------------------------------' +-K-+ | | +-M-+ | | '-G-' | | .-(--1--)-------. | +-+-+-BINARY--+---------------+---------+-----------------+-----------------------------+ | | | '-(--integer--)-' | | | | | '-+-BINARY VARYING-+--(--integer--)-' | | | | '-VARBINARY------' | | | | .-(--1M--)-------------. | | | '---+-BLOB----------------+----+----------------------+-' | | '-BINARY LARGE OBJECT-' '-(--integer--+---+--)-' | | +-K-+ | | +-M-+ | | '-G-' | +-+-DATE-------------------+------------------------------------------------------------+ | | .-(--0--)-. | | | +-TIME--+---------+------+ | | | .-(--6--)-. | | | '-TIMESTAMP--+---------+-' | | .-(--200--)-----. | +---DATALINK--+---------------+--+--------------+---------------------------------------+ | '-(--integer--)-' '-ccsid-clause-' | +---ROWID-------------------------------------------------------------------------------+ '---XML---------------------------------------------------------------------------------' ccsid-clause |--CCSID--integer-----------------------------------------------|
If procedure-name () is specified, the procedure identified must have zero parameters.
If an unqualified distinct type or array type name is specified, the database manager searches the SQL path to resolve the schema name for the distinct type or array type.
For data types that have a length, precision, or scale attribute, use one of the following:
Specifying the FOR DATA clause or CCSID clause is optional. Omission of either clause indicates that the database manager ignores the attribute when determining whether the data types match. If either clause is specified, it must match the value that was implicitly or explicitly specified in the CREATE PROCEDURE statement.
Assignment of locator values. If a SET RESULT SETS statement was executed in the procedure, the SET RESULT SETS statement identifies the result sets. The locator values are assigned to the items in the descriptor area or the SQLVAR entries in the SQLDA in the order specified on the SET RESULT SETS statement. If a SET RESULT SETS statement was not executed in the procedure, locator values are assigned to the locator variables in the order that the associated cursors are opened at runtime. Locator values are assigned to the locator variables in the same order that they would be placed in the entries in the SQL descriptor area or the SQLDA as a result of a DESCRIBE PROCEDURE statement.
Locator values are not provided for cursors that are closed when control is returned to the invoking application. If a cursor was closed and later re-opened before returning to the invoking application, the most recently executed OPEN CURSOR statement for the cursor is used to determine the order in which the locator values are returned for the procedure result sets. For example, assume procedure P1 opens three cursors A, B, C, closes cursor B, and then issues another OPEN CURSOR statement for cursor B before returning to the invoking application. The locator values assigned for the following ASSOCIATE LOCATORS statement will be in the order A, C, B.
ASSOCIATE RESULT SET LOCATORS (:loc1, :loc2, :loc3) WITH PROCEDURE P1;
CALL P1;
ASSOCIATE RESULT SET LOCATORS (:loc1) WITH PROCEDURE P1; -> loc1 is assigned a value for first result set, and a warning is returned
ASSOCIATE RESULT SET LOCATORS (:loc2) WITH PROCEDURE P1; -> loc2 is assigned a value for first result set, and a warning is returned
ASSOCIATE RESULT SET LOCATORS (:loc3) WITH PROCEDURE P1; -> loc3 is assigned a value for first result set, and a warning is returned
ASSOCIATE RESULT SET LOCATORS (:loc4) WITH PROCEDURE P1; -> loc4 is assigned a value for first result set, and a warning is returned
Multiple calls to the same procedure: When multiple calls to the same procedure are made from the same program, the result sets of earlier invocations are lost unless an ASSOCIATE LOCATOR statement is executed prior to a subsequent CALL to the procedure. The ASSOCIATE LOCATORS statement will refer to the most recent CALL
EXEC SQL CALL P1; /* Returns 2 result sets */ EXEC SQL CALL P1; /* Returns 2 result sets, result sets from first invocation are closed */ EXEC SQL ASSOCIATE RESULT SET LOCATORS (:a, :b) WITH PROCEDURE P1; /* Refers to second call */ EXEC SQL CALL P1; /* Returns 2 result sets */ EXEC SQL ASSOCIATE RESULT SET LOCATORS (:c, :d) WITH PROCEDURE P1; /* Refers to third call */ /* The following statements process the result sets from the second call */ EXEC SQL ALLOCATE C1 CURSOR FOR RESULT SET :a; EXEC SQL ALLOCATE C2 CURSOR FOR RESULT SET :b; EXEC SQL FETCH C1 INTO :h1; EXEC SQL CLOSE C1; EXEC SQL FETCH C2 INTO :h2; EXEC SQL CLOSE C2; /* The following statements process the result sets from the third call */ EXEC SQL ALLOCATE C3 CURSOR FOR RESULT SET :c; EXEC SQL ALLOCATE C4 CURSOR FOR RESULT SET :d; EXEC SQL FETCH C3 INTO :h1; EXEC SQL CLOSE C3; EXEC SQL FETCH C4 INTO :h2; EXEC SQL CLOSE C4;
Allocate result set locators for procedure P1 which returns 3 result sets
ASSOCIATE RESULT SET LOCATORS (:loc11, :loc2, :loc3) WITH PROCEDURE P1;
