ALLOCATE CURSOR

Restriction: This topic applies to Windows environments (local development) only.

Syntax:

>>-EXEC SQL--ALLOCATE cursor_name FOR RESULT SET :rs_locator_variable->
>-----END-EXEC-----<>

Parameters:

`cursor_name`	A previously declared and opened cursor.
`:rs_locator_variable`	A result set locator host variable that has been declared in the application program.

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