Home > IT Answers > AS/400 > cursors left open in embedded SQL
Chance
105 pts.
 cursors left open in embedded SQL
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?

Software/Hardware used:
ASKED: February 21, 2006  5:32 PM
UPDATED: November 14, 2010  11:07 AM
RELATED QUESTIONS
Answer Wiki:
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.
Last Wiki Answer Submitted:  February 21, 2006  9:03 pm  by  DaddyCOZ   0 pts.
All Answer Wiki Contributors:  DaddyCOZ   0 pts.
To see all answers submitted to the Answer Wiki: View Answer History.


RSS - Discussions Help
Discuss This Question:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _

 

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.

Vatchy
 1,410 pts.
 

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?

Chance
 105 pts.
 

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.

Vatchy
 1,410 pts.
 

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).

Chance
 105 pts.
 

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

TomLiotta
 107,715 pts.