Start of change

ASSOCIATE LOCATORS

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

Invocation

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.

Authorization

None required.

Syntax

Read syntax diagramSkip visual syntax diagram
                                               .-,-------------------.      
              .-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-'   

Read syntax diagramSkip visual syntax diagram
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-----------------------------------------------|

Description

rs-locator-variable
Identifies a result set locator variable that has been declared according to the rules for declaring result set locator variables.
WITH PROCEDURE procedure-name or variable
Identifies the procedure that returned one or more result sets. When the ASSOCIATE LOCATORS statement is executed, the procedure name must identify a procedure that the requester has already invoked using the SQL CALL statement.
PROCEDURE or SPECIFIC PROCEDURE
Identifies the procedure. The procedure-name must identify a procedure that exists at the current server.
PROCEDURE procedure-name
Identifies the procedure by its name. The procedure-name must identify exactly one procedure. The procedure may have any number of parameters defined for it. If there is more than one procedure of the specified name in the specified or implicit schema, an error is returned.
PROCEDURE procedure-name (parameter-type, ...)
Identifies the procedure by its procedure signature, which uniquely identifies the procedure. The procedure-name (parameter-type, ...) must identify a procedure with the specified procedure signature. The specified parameters must match the data types in the corresponding position that were specified when the procedure was created. The number of data types, and the logical concatenation of the data types is used to identify the specific procedure instance which is to be labeled on. Synonyms for data types are considered a match. Start of changeParameters that have defaults must be included in this signature.End of change

If procedure-name () is specified, the procedure identified must have zero parameters.

procedure-name
Identifies the name of the procedure.
(parameter-type, ...)
Identifies the parameters of the procedure.

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:

  • Empty parentheses indicate that the database manager ignores the attribute when determining whether the data types match. For example, DEC() will be considered a match for a parameter of a procedure defined with a data type of DEC(7,2). However, FLOAT cannot be specified with empty parenthesis because its parameter value indicates a specific data type (REAL or DOUBLE).
  • If a specific value for a length, precision, or scale attribute is specified, the value must exactly match the value that was specified (implicitly or explicitly) in the CREATE PROCEDURE statement. If the data type is FLOAT, the precision does not have to exactly match the value that was specified because matching is based on the data type (REAL or DOUBLE).
  • If length, precision, or scale is not explicitly specified, and empty parentheses are not specified, the default attributes of the data type are implied. The implicit length must exactly match the value that was specified (implicitly or explicitly) in the CREATE PROCEDURE statement.

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.

AS LOCATOR
Specifies that the procedure is defined to receive a locator for this parameter. If AS LOCATOR is specified, the data type must be a LOB or XML or a distinct type based on a LOB or XML. If AS LOCATOR is specified, FOR SBCS DATA or FOR MIXED DATA must not be specified.
SPECIFIC PROCEDURE specific-name
Identifies the procedure by its specific name. The specific-name must identify a specific procedure that exists at the current server.
variable
Start of changeSpecifies a variable that contains a procedure or specific name. If variable is specified:
  • It must be a character-string variable or Unicode graphic-string variable. It cannot be a global variable.
  • It must not be followed by an indicator variable.
  • Start of changeThe name that is contained within the variable must be left-justified and must be padded on the right with blanks if its length is less than that of the variable.End of change
  • Start of changeThe name must be in uppercase unless it is a delimited name.End of change

Start of changeIf only one procedure with this name has been invoked using the CALL statement, the variable is used as a procedure name. If multiple procedures with this name have been invoked, the variable is used as a specific name.End of change

End of change

Notes

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;

More than one locator can be associated with a result set. You can issue multiple ASSOCIATE LOCATORS statements for the same procedure with different result set locator variables to associate multiple locators with each result set.
  • If the number of result set locator variables specified in the ASSOCIATE LOCATORS statement is less than the number of result sets returned by the procedure, all locator variables specified in the statement are assigned a value and a warning is issued. For example, assume procedure P1 exists and returns four result sets. Each of the following ASSOCIATE LOCATORS statements returns information on the first result set along with a warning that not enough locators were provided to obtain information about all the result sets.

    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

  • 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 locator variables are assigned a value of 0.

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; 

Start of changeRETURN TO CLIENT procedures: A result set of a RETURN TO CLIENT procedure becomes associated with the highest procedure on the invocation stack. To associate a locator with such a result set, the procedure name of the highest procedure on the invocation stack must be specified.End of change

Example

Allocate result set locators for procedure P1 which returns 3 result sets

    ASSOCIATE RESULT SET LOCATORS (:loc11, :loc2, :loc3) WITH PROCEDURE P1;
End of change