Micro Focus Enterprise Developer 3.0 for Eclipse > Programming > Mainframe Programming > SQL Option for DB2 > Reference > SQL Reference > SQL Statements > ASSOCIATE LOCATORS

ASSOCIATE LOCATORS

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

Restriction: This topic applies to Windows environments only.

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.

Authorization

No authorization is required for this statement.

Syntax

ASSOCIATE [RESULT SET] {LOCATOR | LOCATORS} 
    (rs-locator-variable ,...)
    WITH PROCEDURE {procedure-name | host-variable}

Parameters

RESULT SET

This optional item has no effect on the execution of the statement. Use it only to improve the readability of the statement.

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.

The ASSOCIATE LOCATORS statement assigns result set locator values from the SQLVAR sections of the SQLDA to result set locator variables. For languages other than REXX, the first SQLDATA field is assigned to the first locator variable, the second SQLDATA field to the second locator variable, and so on. For REXX, the first SQLLOCATOR field is assigned to the first locator variables, the second SQLLOCATOR field to the second locator variable, and so on.

If a stored procedure is called more than once with a one-part name at the same location, only the most recent result sets are accessible.

LOCATOR | LOCATORS

Specifies the use of a locator or locators.

rs-locator-variable

Identifies a result set locator variable that has been declared according to the rules for declaring result set locator variables. One result set locator variable is required for each result set that is returned by the stored procedure. If the stored procedure returns fewer result sets than the number of result set locator variables specified, the extra variables are assigned a value of 0.

WITH PROCEDURE

Identifies the stored procedure that returned result set locators by the specified procedure name or the procedure name contained in the host variable.

A procedure name is a qualified or unqualified name. Each part of the name is a long SQL identifier that must be composed of SBCS characters:

A fully qualified procedure name is a three-part name. The first part is a location name that identifies the DBMS where the procedure is stored. The next two parts identify the stored procedure. A period must separate each of the parts. Any or all of the parts can be a delimited identifier.
An implicitly qualified procedure name is a two-part name that is qualified by the location name of the current server. The two parts identify the schema name and the name of the stored procedure. A period must separate the two parts. The first part identifies the stored procedure at the server. The location name of the first part depends on the application server.
An unqualified procedure name is a one-part name with two implicit qualifiers. The first implicit qualifier is the location name of the current server. The second implicit qualifier identifies the stored procedure at the server. The meaning of the second implicit qualifier depends on the application server (for MVS, it is SYSPROC). The name and its implicit qualifiers identifies a stored procedure.
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.

If a host variable is used:

It must be a character string variable with a length attribute that is not greater than 254.
It must be preceded by a colon and must not be followed by an indicator variable.
The value of the host variable is a specification that depends on the application server. Regardless of the application server, the specification must:
- Be left justified within the host variable
- Not contain embedded blanks
- Be padded on the right with blanks if its length is less than that of the host variable

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. However, there is one condition under which the names do not have to match. If the CALL statement was made with a three-part name and the current server is the same as the location in the three-part name, you can omit the location name and specify a two-part name.

procedure-name

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

host-variable

Identifies the stored procedure that returned result set locators by the procedure name contained in the host variable.

Description

The ASSOCIATE LOCATORS statement assigns result set locator values from the SQLVAR sections of the SQLDA to result set locator variables so that the first SQLDATA field is assigned to the first locator variable, the second SQLDATA field to the second locator variable, and so on.

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 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 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.

When the ASSOCIATE LOCATORS statement is executed, the procedure name or specification must identify a stored procedure that the requestor has already invoked using the CALL statement.

Note: For this statement to be successful, the application must be currently connected to the site at which the stored procedure was executed.

Examples

The statements in the following examples are assumed to be in COBOL programs.

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 from current server SITE2.

 EXEC SQL CONNECT TO SITE2;  
 EXEC SQL CALL P1; 
 EXEC SQL ASSOCIATE RESULT SET LOCATORS (:LOC1, :LOC2) 
          WITH PROCEDURE P1;

Repeat the scenario above, 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.

 EXEC SQL CONNECT TO SITE2;  
 EXEC SQL CALL MYSCHEMA.P1; 
 EXEC SQL ASSOCIATE RESULT SET LOCATORS (:LOC1, :LOC2) 
          WITH PROCEDURE MYSCHEMA.P1;

Use result set locator variables LOC1 and LOC2 to get the result set locator values for the two result sets that are returned by the stored procedure named by host variable HV1. Assume that host variable HV1 contains the value SITE2.MYSCHEMA.P1 and the stored procedure is called with a three-part name.

 EXEC SQL CALL SITE2.MYSCHEMA.P1; 
 EXEC SQL ASSOCIATE LOCATORS (:LOC1, :LOC2) 
          WITH PROCEDURE :HV1;

The preceding example would be invalid if host variable HV1 had contained the value MYSCHEMA.P1, a two-part name. For the example to be valid with that two-part name in host variable HV1, the current server must be the same as the location name that is specified on the CALL statement as the following statements demonstrate. This is the only condition under which the names do not have to be specified the same way and a three-part name on the CALL statement can be used with a two-part name on the ASSOCIATE LOCATORS statement.

 EXEC SQL CONNECT TO SITE2; 
 EXEC SQL CALL SITE2.MYSCHEMA.P1; 
 EXEC SQL ASSOCIATE LOCATORS (:LOC1, :LOC2) 
          WITH PROCEDURE :HV1;