SQLCODE=-311 in Cobol SP-DB2.

Vikram.JV · New User Joined: 14 Jul 2024 Posts: 1 Location: India

The dynamic SQL should be built in a Cobol SP-DB2, which doesn't have fixed number of host variable in WHERE clause and also moreover SQL is dynamic for each SP call.

For example, the below SQL returns multiple rows. My requirement is to use parameter marker.

SQL: select * from employee where emp_no = &var1 and &var1 not in ('')

Length of emp-no variable is 15. Length of its attribute/value is 9.

Below steps followed:
1) Replacing &var1 in the SQL with the parameter marker ?.
2) DECLARE CURSOR
3) PREPARE
4) DESCRIBE INPUT sqlstmt INTO SQLDA
4) Set the address of EMP-NO to SQLDATA (1) and SQLDATA (2)
5) OPEN cursor USING DESCRIPTOR :SQLDA.

Here OPEN statement failing with SQLCODE=-311. While checking the DESCRIBE INPUT, the second &var1 is identified with SQLTYPE (2) as '+0449' (variable character) and with SQLLEN (2) as '+0000', Where as the first &var1 is identified with SQLTYPE (1) as '+0453' and with SQLLEN (1) as '+0015'.

I have tried populating the SQLLEN (2) for the second &var1 in the application program with length =+0009 (which has the emp no with length of the value as 9), and also with length = +0015, but that doesn't resolve.

How to resolve this issue in the COBOL SP-DB2 program?

sergeyken · Posted: Thu Jul 18, 2024 5:27 pm

You need to present THE DETAILED CODE (within Code tags), plus ALL AVAILABLE intermediate results, AFTER EACH of your described steps.

Theoretical discussion here is useless.
It is equivalent to: "I do everything right, but my results are wrong. Why?!"

Rohit Umarjikar · Posted: Fri Jul 19, 2024 1:14 pm

Welcome !

The terms used are not accurate one ..

When you say about dynamic sql then it innvolvew prepare and execute statements followed after open ..

Also it’s best to store your dynamic where condition and / or select in another db2 table tagging against unique id .

Frontend/ caller will pass the unique id based on what’s selected and then you would query the table for that id to prepare your query to execute .

Is that what’s done ?