I am implementing dynemic sql cursor in my interface program.If the source program return one table name then the this interface program is working fine.but if the source program return more than one table name then i am getting -313 sql code.
let me explan clearly,Earlier we have one huge table,as per the new requirement they created meny tables under the same schema.
In the dynemic sql the query will be looks like this,
(SELECT * FROM TABLENAME1
WHERE SEND_RT_NO = ?
AND RECV_RT_NO = ?
AND RESEND_IND = ?
AND FILE_RECV_CNT = ?
AND CREA_DTE = ?
AND CREA_TME = ?
AND CASH_LET_BUS_DTE = ?
AND CASH_LET_ID = ?
AND BUNDLE_ID = ?
AND CNTL_REC_IND = ?)
UNION
(SELECT * FROM TABLENAME2
WHERE SEND_RT_NO = ?
AND RECV_RT_NO = ?
AND RESEND_IND = ?
AND FILE_RECV_CNT = ?
AND CREA_DTE = ?
AND CREA_TME = ?
AND CASH_LET_BUS_DTE = ?
AND CASH_LET_ID = ?
AND BUNDLE_ID = ?
AND CNTL_REC_IND = ?)
EXEC SQL
OPEN ARW_X9_CURSOR USING :WS-DBL-SEND-ABA,
:WS-DBL-RECV-ABA,
:WS-DBL-RESEND-IND,
:WS-DBL-FILE-RECV-IND,
:WS-DBL-CREATE-DATE,
:WS-DBL-CREATE-TIME,
:WS-DBL-BUSINESS-DATE,
:WS-DBL-CASH-LET-ID,
:WS-DBL-BUNDLE-ID,
:WS-DBL-HEADER
END-EXEC.
If the query contain more than one table i am getting -313 error,but i saw the reason for -313
SQL Code -313
THE NUMBER OF HOST VARIABLES SPECIFIED IS NOT EQUAL TO THE NUMBER OF PARAMETER MARKERS
Explanation: The number of host variables specified in the EXECUTE or OPEN statement is not the same as the number of parameter markers (question marks) appearing in the prepared SQL statement.
System Action: The statement cannot be executed.
Programmer Response: Correct the application program so that the number of host variables specified in the EXECUTE or OPEN statement is the same as the number of parameter markers appearing in the prepared SQL statement.
SQLSTATE: 53017
But the number of host variables specified in the OPEN statement is equal as the number of parameter markers (question marks) appearing in the prepared SQL statement.Even i am getting -313 abend,please let me know why i am getting this abend and how to resolve this?
Thanks Dick Brenholtz...It is working now.
But i don't know how meny tables return from source pgm to this interface pgm bcz no of tables depends on user input.
Joined: 20 Oct 2006 Posts: 6966 Location: porcelain throne
babu_hi,
i just guessed based on the counts of markers in your sql and the info given in the -313 writeup.
That my suggestion is a solution leads me to believe that you would build the OPEN dynamically as you do the SELECT.
For every additional table, you would need an additional set of host variables in the OPEN.
Keep in mind, because you can visually see that they are repeated (the same for each set)
does not mean that DB2 will recognize that they are the same
or even attempt to make such a resolution.
Your sql could very well have different host variables for each of the different union segments.
