cursors left open in embedded SQL

105 pts.
Tags:
RPGLE
I have a problem with SQL cursors being left open, even after the "close cursor" instruction has been executed successfully (SQLSTT = '00000') from within an RPGIV program. There are 2 cursors within the program: 1 cursor is used for fetching the count of selected records, the other is a dynamic scroll cursor for fetching the actual detail information. The SQL is working fine; I am getting the results I expect. However, when the program ends, the queried file is sometimes still open when I do a DSPJOB, and there may be several occurrences of it under open files. I have gone so far as to put extra "close cursor" instructions for each of the 2 cursors when the program ends, but the file still shows as open. Any ideas what I am missing?
ASKED: February 21, 2006  5:32 PM
UPDATED: November 14, 2010  11:07 AM

Answer Wiki

Thanks. We'll let you know when a new response is added.

curson close options are available in the CRTSQLRPGI step. The default is to close the cursor when the activation group is ended. So, ending the program even with INLR = *on has no effect. You probably want to change the compile option to close the cursors when the module ends.

Discuss This Question: 5  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
  • Vatchy
    In addition, you don't need a cursor in order to get a record count. Just run the SQL Select statement without declaring it in a cursor.
    1,410 pointsBadges:
    report
  • Chance
    First of all, thank you DaddyCOZ. The change in the compile option worked. Your help is greatly appreciated. Now I have a question for Vatchy concerning not having to declare a cursor to obtain a record count. My situation is that the "where" part of the select statement will be dynamic. The only examples I have found which involved a dynamic select stmt always used a cursor. In view of the dynamic nature of the select, is there a way to obtain the count without a cursor?
    105 pointsBadges:
    report
  • Vatchy
    Use the prepare statement. Here is an example that I keep lying around: /Free w@Cmd = 'Create Alias QTEMP/ALIAS1 For ' + %Trim(s1OrigLib) + '/' + %Trim(s1OrigFile) + '(' + %Trim(s1Member) + ')'; /End-Free C/Exec SQL C+ Prepare w@Statement From :w@Cmd C/End-Exec C/Exec SQL C+ Execute w@Statement C/End-Exec Obviously the SQL statement in w@Cmd isn't relevant to what you are doing but you should get the idea on how to do it for your problem.
    1,410 pointsBadges:
    report
  • Chance
    Thanks for your response Vatchy. The portion of your response "Execute w@Statement" pointed me to the right topic in a manual that we have. Unfortunately, the "prepare"/"execute" method will not work with a Select statement. I thought I'd give it a try anyway, and got SQLSTT of 42618 (host variable not permitted here). It looks like the cursor is the way I will have to go to get the record count fetched into a host variable (since I have a dynamic "where" clause).
    105 pointsBadges:
    report
  • TomLiotta
    There's no need for a cursor for a count even with a variable WHERE clause:
         Hdatedit(*ymd) nomain
         DgetVI            pr             5i 0
         D viKey                         10    const
    
         PgetVI            b                   export
         DgetVI            pi             5i 0
         D viKey                         10    const
    
         D viCount         s              5i 0
    
         C/exec sql values ( select count(*) from accpth
         c+                  where apfile = :viKey ) into :viCount
         C/end-exec
    
         C                   return    viCount
         P                 e
    That returns a COUNT() for any variable viKey passed into that WHERE clause. But perhaps you mean something different. An example that shows what you need might be useful. 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