Cursor is returning previous values in SQLRPGLE

2505 pts.
Tags:
AS/400
SQLRPGLE
I have an SQLRPGLE program which accepts the SQL statement as an input parameter to the program from an RPGLE program. (The SQL is entered by user .) My intention is to validate the SQL and make a prepare statement out of it, and then initiates a Fetch Next from C1 into a :wSTR. Check for the SQLCOD to determine whether the SQL is valid. IF valid i will display the result to user by moving the wSTR value to a display file field(of length 350A). If I enter a correct SQL I will get the result, if for the second time I enter some irrelevant values, I am still getting the previous output. I am closing the cursor at the end and also at the beginning of the program. I am clearing the field wSTR also. In debug before the prepare statement I can see wSTR as blanks. But after the fetch, though the SQL is bad, it returns the previous output ans SQLCOD as Zeros. This looks very strange. Please help me to figure out the issue.

Software/Hardware used:
AS400

Answer Wiki

Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Discuss This Question: 11  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • TomLiotta
    We can't help until we see the program instructions. We can't tell what's wrong by reading a description of how it's supposed to work. Show the code that isn't working as you want. -- Tom
    125,585 pointsBadges:
    report
  • RamvishakRamesh
    C CLEAR W@SQLRTN ** Execute SQL Stmt c/exec sql c+ PREPARE S1 From :W@SQLSTR C/end-exec c/exec sql c+ DECLARE C1 SCROLL CURSOR FOR S1 C/end-exec c/exec sql c+ OPEN C1 C/end-exec c/exec sql C+ FETCH NEXT FROM C1 into :W@SQLRTN C/end-exec * C IF SQLCOD0 C and SQLCOD100 and SQLCOD-305 c/exec sql c+ CLOSE C1 C/end-exec * Return *ERROR if SQL script is invalid C EVAL P@RTNCOD='*ERROR' C DUMP C Else C EVAL P@RTNCOD= *Blanks C EndIf * c/exec sql c+ CLOSE C1 C/end-exec * * If instrument code is not present in FDBVAL, send the message back C Else C Eval DTLMSG = 'Instrument Code is Invalid' C ITER
    2,505 pointsBadges:
    report
  • TomLiotta
    I put your source into free-form to make it a little more readable, and I have a couple questions.   First, there is no error checking for the PREPARE, DECLARE CURSOR, OPEN and CLOSE statements. How do you know when any of them work or fail?   And second, I don't understand this statement:
         if SQLCOD0 and SQLCOD100 and SQLCOD-305 ;
    It looks like it's testing for three different SQLCOD values all at the same time, but that can never happen. It might be one of them at a time, but never more than one. So, what do the three names really represent?   I'd also suggest that you delete the first CLOSE C1 statement. Since you CLOSE C1 immediately after the IF-statement, there doesn't seem to be a good reason to close it inside the IF-statement. That would just cause an error when the second one is reached.   Tom
    125,585 pointsBadges:
    report
  • RamvishakRamesh
    Sorry, that is a typo It is IF        SQLCOD<>0                                         and SQLCOD<>100 and SQLCOD<>-305        I have removed the close c1 inside the loop. Still no luck.  
    2,505 pointsBadges:
    report
  • TomLiotta
    Ah, that makes sense. The not-= symbols could have special meaning in a browser. The CLOSE C1 shouldn't make a difference to your problem; it was just potential minor trouble. The biggest items would be checking for error codes on the other SQL statements. Each of those need to be eliminated as causes. -- Tom
    125,585 pointsBadges:
    report
  • philpl1jb
    IF        SQLCOD<>0    and SQLCOD<>100 and SQLCOD<>-305     100 No record found .. -305 Some field(s) are null You're code proceeds as good when no new row is found (100) and when a row contains a null value -305.  Is this what you want to do?    
    49,960 pointsBadges:
    report
  • philpl1jb
    When fetch fails with 100 or -305 array will still have previous values. You might want to clear W@SQLRTN before the fetch, Phil
    49,960 pointsBadges:
    report
  • philpl1jb
    Change the word array to data structure or field ???
    49,960 pointsBadges:
    report
  • TomLiotta
    Phil is right about the combination of tests.   The IF-statement essentially says "If there is some problem and the error is not a 'No Record' and the error also is not a 'Indicator Required', then we have an 'Error' for P@RTNCOD; else we have blanks."   So, if the error is a 'No Record' condition, then the program goes on as if a record was found and all is well. It assumes that a record was FETCHed. We can't tell what data it uses for W@SQLRTN since it was CLEARed.   I don't have code that tests this odd combination of error conditions. And since you do have the code, it'll be easy for you to test what happens when you have a good statement that returns a row followed by a second SQL statement that doesn't find a row. Just run your procedure through debug twice with two different SQL statements.   The other SQL statements still need error checking. A SQL statement that doesn't PREPARE or OPEN properly, for example, is still going to cause trouble.   Tom
    125,585 pointsBadges:
    report
  • philpl1jb
    I don't really get the fetch into :W@SQLRTNThis is either:a field which can receive one field of the same type from the sql ora data structure which can receive a series of fields provided that the fields in the structure are in the same general type and order of the incoming data from the SQLPhil
    49,960 pointsBadges:
    report
  • TomLiotta
    I don't quite understand the return variable either, but figured either it's a test version of the code or it's retrieving something like text description strings. But an array (i.e., structure) would work. I didn't think of an array because the code is simple. Handling an array requires some detail attention unless it's a series of similar elements. -- Tom
    125,585 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following