ASSOCIATE LOCATORS

Gets the result set locator value for each result set returned by a SQL CLR stored procedure.
Restriction: This topic applies to Windows environments (local development) only.

Syntax:

              .-RESULT SET-.                 
>>-ASSOCIATE--+------------+--+-LOCATORS--+---------------------->
                              '-LOCATOR--'   

      .-,-------------------.      
      V                     |      
>--(----rs-locator-variable-+--)-------------------------------->

>--WITH PROCEDURE--+-procedure-name-+--------------------------><
                   '-hvar-----------'   

Parameter:

rs_locator_variable A result set locator host variable that has been declared in the application program.
procedure-name The name of a SQL CLR stored procedure.
hvar A host variable that contains the name of a SQL CLR stored procedure.

Comments:

You can create SQL CLR stored procedures using our Enterprise Developer for Visual Studio product.

Supported for client applications that call SQL CLR stored procedures that return result sets and are compiled with DBMAN=ODBC and DIALECT=MAINFRAME.

Example:

  working-storage section.
       exec sql include sqlca end-exec.
       01  mfsqlmessagetext    pic x(200).
       01  hv-country  pic n(15) national.
       01  CustomerID  pic n(5) national.
       01  Company     pic n(40) national.
       01  City        pic n(15) national.
       01  CustomerID2 pic n(5) national.
       01  Company2    pic n(40) national.
       01  City2       pic n(15) national.
       01  rsl         sql type result-set-locator varying.
       01  rs2         sql type result-set-locator varying.
       01  rs3         sql type result-set-locator varying.
       procedure division.
           exec sql connect to Northwind end-exec

           move N"UK" to hv-country   
  
           *> Test DB2 result set locators
           *> This proc opens 3 cursors. closes 2 and re-opens on
           *> So should return 2 result sets
           exec sql call TestProc1(:hv-country) end-exec
           
           exec sql
               associate locators (:rsl, :rs2, :rs3) 
               with procedure TestProc1
           end-exec
           
           exec sql
               allocate c1 cursor 
               for result set :rsl
           end-exec
           if sqlcode not = 0
               display "Open 1st locator failed"
           end-if
       
           exec sql
               allocate c2 cursor 
               for result set :rs2
           end-exec
           if sqlcode not = 0
               display "Open 2nd locator failed"
           end-if
       
           exec sql
               allocate c3 cursor 
               for result set :rs3
           end-exec
           if sqlcode = 0
               display "FAIL: Open 3rd locator succeeded"
           end-if