Re: Strange issue in embedded sql

On 31 Jan 2013 01:07, Nisha Ramesh wrote:

I have a program which accepts the SQL statements user enters and
these statements are passed onto a SQLRPGLE program which declare a
scroll cursor and prepares the SQL from the string which holds input
parameter. It then issues a fetch first statement into a field which
will hold the query result. I am checking SQL code to determine
whether the entered query is valid or not. I close the cursors after
this. If first time I entered a valid SQL I get the result..for
second time if some irrelevant data is entered still the fetch
statement returns previous SQL's output and SQLCOD also is zero as
if I entered correct SQL. Pls help me to figure out the problem
here.

I have read the above several times, and the replies... and I finally think I understand what is written in words instead of shown in [pseudo-] code. Note that often times explaining what code does is best described as [pseudo]code; logic is much less susceptible to misinterpretation than are sentences of words. FWiW the biggest difficulty I had understanding was "some irrelevant data is entered" which might have best been explained showing input from the successful request and then the input from the failing request. Anyhow...

I believe the problem might be that the code needs to examine the SQLSTATE and SQLCODE from each SQL processing prior to the FETCH, i.e. the PREPARE, DECLARE, and OPEN statements, but the code is failing to stop processing due to an error in that prior processing. Also if the SQL is performed in a *NEW activation group and returns, then the old cursor would not be available to the next invocation and the FETCH would fail as well.