View previous topic :: View next topic
|
Author |
Message |
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? |
|
Back to top |
|
|
sergeyken
Senior Member
Joined: 29 Apr 2008 Posts: 2140 Location: USA
|
|
|
|
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?!" |
|
Back to top |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010 Posts: 3076 Location: NYC,USA
|
|
|
|
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 ? |
|
Back to top |
|
|
|