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