MVSFORUMS.com

[jcr]

I am new to stored procs, as is the company I am working for. We will
be writing stored procs which may return any number of result sets.
After referring to the DB2 manuals etc, I have no trouble understanding
how this is supposed to be handled. But I AM having trouble making it
work when using a COBOL calling program.

As the manuals state, you use the DESCRIBE PROCEDURE xxxx INTO :SQLDA
to determine the required info. The num of result sets will be in the
SQLD field (no problem), and the result set locator values are returned
in the array element SQLDATA which is format POINTER. Then the result
set locators need to be linked to the result sets. The DB2 Application
Programming and SQL guide states, on page 602:

'You can copy the values from the SQLDATA fields to the result set
locators manually, or you can execute the ASSOCIATE LOCATORS statement
to do it for you'.

How do you copy them manually, given that the locators must be USAGE
SQL TYPE IS RESULT-SET-LOCATOR VARYING? I can't find any method by
which COBOL will let me copy the value of SQLDATA(SQLD) to a locator
variable.

We don't want to assume a max number of result sets and 'hard code' to
this maximum, ie. if we know there'll be no more than 6 result sets, we
don't want to define our locator vars this way (in reality we may need
to handle 84 result sets):
01 LOC1 USAGE SQL TYPE IS RESULT-SET-LOCATOR VARYING.
01 LOC2 USAGE SQL TYPE IS RESULT-SET-LOCATOR VARYING.
01 LOC3 USAGE SQL TYPE IS RESULT-SET-LOCATOR VARYING.
01 LOC4 USAGE SQL TYPE IS RESULT-SET-LOCATOR VARYING.
01 LOC5 USAGE SQL TYPE IS RESULT-SET-LOCATOR VARYING.
01 LOC6 USAGE SQL TYPE IS RESULT-SET-LOCATOR VARYING.
and then ASSOCIATE LOCATORS (:LOC1, :LOC2, :LOC3, :LOC4, :LOC5, :LOC6).

We must be able to do it in a more dynamic way. But I can't find any
documentation which actually shows you how to do this properly - it
just tell you what the SQLDA contains. And Cobol doesn't seem to like
it when I try defining the locators in an array:

01 WS-LOCATORS.
05 WS-LOCS OCCURS 6.
07 WS-LOC USAGE SQL TYPE IS RESULT-SET-LOCATOR VARYING.

And the DB2 pre compiler doesn't like:
EXEC SQL
ASSOCIATE LOCATOR (:WS-LOC(WS-SUB)) WITH PROCEDURE xxxxxxx
END-EXEC
or
EXEC SQL
ASSOCIATE LOCATORS (:WS-LOCATORS) WITH PROCEDURE xxxxxxx
END-EXEC
or
EXEC SQL
ALLOCATE xxxxxxxxx CURSOR FOR
RESULT SET :WS-LOC (WS-SUB)
END-EXEC

We really need to be able to do 'PERFORM HANDLE-RESULT-SET SQLD times'
type logic.

Is anybody able to help? Thanks.

[Jolly]

Hi,

This is how it will work:

1. In the calling program, declare

01 LOCVAR SQL TYPE IS RESULT-SET-LOCATOR VARYING.

2. Call stored procedure using CALL statement.

EXEC SQL
CALL proc-name(:parms)
END-EXEC

3. Associate locator with Procedure

EXEC SQL
ASSOCIATE LOCATOR(:LOCVAR)
WITH PROCEDURE proc-name
END-EXEC

4. Allocate cursor for locator

EXEC SQL
ALLOCATE C1 CURSOR
FOR RESULT SET :LOCVAR
END-EXEC

5. Fetch cursor until SQLCODE 100.

EXEC SQL
FETCH C1 INTO :host-var
END-EXEC

To read n number of rows, you need only one locator declared and loop through the cursor n number of times.

Hope this helps!

[jcr]

[Bithead]

I have never tried this in COBOL but I have in Visual Basic so it may work. I would try passing back the number of cursors in a PARM variable then, for this number of times, try issuing the DESCRIBE, ASSOCIATE and then a FREE LOCATOR the number of times as specified in the PARM.

Alternativley, loop through the process until you don't get a +494. This may mean you don't need a PARM value.

[jcr]

Thanks, but we can already determine the number of result sets returned. The DESCRIBE CURSOR statement populates the SQLDA with this information. SQLD contains the number of result sets. My issue is how to do all this dynamically with Cobol sysntax, and SQL, the way it is. ie how do you define a varying number of locator vars (SQL can't have an array element as the host var in an ASSOCIATE or ALLOCATE statement), and how do you have a varying cursor name, etc?
We don't want to have a hard coded set of statements for each different result set, or cursor.
This is because:
1) At the time of coding, we do not know how many will be returned from the SP, so how many do you code?
2) What if there's 84 possible result sets returned?

This is why we need to code 'PERFORM HANDLE-RESULT-SET SQLD times' type logic, but this is proving a problem syntactically.

[Bithead]

Try issuing the DESCRIBE, ASSOCIATE and then a FREE LOCATOR for one locator variable until you don't get a +494. As I said, I have never attempted this but it is worth a try.