Skip to main content

Hello

I am running Visual Cobol for Eclipse V 7 on a windows computer. The database is on Linux.

I am trying to call a DB2 (v11.5) stored procedure that returns a result set (returning an open cursor) but I can't use the ASSOCIATE and ALLOCATE commands in my Cobol program.

The compile error, SQL4911N, suggests that SQL Directive BEHAVIOR should be used but I can't seem to find this setting in my Visual Cobol for Eclipse.

My attempt in Cobol prog calling a DB2 SP called CFS: 

01 LOC-CFS SQL TYPE IS RESULT-SET-LOCATOR VARYING.

exec sql
    call cfs(:cfs-in)
end-exec

exec sql ASSOCIATE result set LOCATOR (:LOC-CFS) WITH PROCEDURE CFS end-exec

exec sql ALLOCATE C1 CURSOR FOR RESULT SET :LOC-CFS end-exec

Fetch and loop until end of cursor

I can call DB2 stored procedures with just input and output parameters but not handling a result set.

Any suggestions?

regards

/Peter

Hello

I am running Visual Cobol for Eclipse V 7 on a windows computer. The database is on Linux.

I am trying to call a DB2 (v11.5) stored procedure that returns a result set (returning an open cursor) but I can't use the ASSOCIATE and ALLOCATE commands in my Cobol program.

The compile error, SQL4911N, suggests that SQL Directive BEHAVIOR should be used but I can't seem to find this setting in my Visual Cobol for Eclipse.

My attempt in Cobol prog calling a DB2 SP called CFS: 

01 LOC-CFS SQL TYPE IS RESULT-SET-LOCATOR VARYING.

exec sql
    call cfs(:cfs-in)
end-exec

exec sql ASSOCIATE result set LOCATOR (:LOC-CFS) WITH PROCEDURE CFS end-exec

exec sql ALLOCATE C1 CURSOR FOR RESULT SET :LOC-CFS end-exec

Fetch and loop until end of cursor

I can call DB2 stored procedures with just input and output parameters but not handling a result set.

Any suggestions?

regards

/Peter

The compiler error that you are receiving, SQL4911N is not a Visual COBOL error code. It is an IBM error code indicating that the host variable data type is not valid. Using the DB2 prep command outside of COBOL or using the Visual COBOL DB2 directive both invoke the IBM DB2 preprocessor.

There are limitations when using the IBM preprocessor on Windows/Linux. The documentation for handling stored procedures in this environment can be found in the Visual COBOL documentation here and the limitations regarding result sets can be found here, but your real source of information on the limitations should come from the IBM manuals.

I am not a DB2 expert but perhaps somebody else on this forum can provide some more guidance?

The compiler error that you are receiving, SQL4911N is not a Visual COBOL error code. It is an IBM error code indicating that the host variable data type is not valid. Using the DB2 prep command outside of COBOL or using the Visual COBOL DB2 directive both invoke the IBM DB2 preprocessor.

There are limitations when using the IBM preprocessor on Windows/Linux. The documentation for handling stored procedures in this environment can be found in the Visual COBOL documentation here and the limitations regarding result sets can be found here, but your real source of information on the limitations should come from the IBM manuals.

I am not a DB2 expert but perhaps somebody else on this forum can provide some more guidance?

Thank you for your answer Chris.

I have come up with some sort of  a workaround;

the SQL Stored Procedure inserts the information into a temporary table and the cobol program, that makes the call of the SP, starts a cursor and fetch the information from the temporary table.

As long as it is in the same transaction it seems to work.

But I would rather have used the Associate and Allocate commands and read an open cursor from the SP directly in the Cobol program. 

Terms & ConditionsAccessibility statement