SQL Error SQL0051: Cursor Previously Declared

[Guest.Visitor]

Hello,

I'd like to "re-Declare" a SQL cursor in my SQLRPGLE program. Of course, the compiler won't let me do this, since it only allows a cursor to be Declared *once* in the program.

The compile error I get is "SQL0051: Cursor Previously Declared".

A cursor may be Closed and re-Opened any number of times, but can only be Declared *once*. Is there any way to get around this? I'd like the program to "forget" that the cursor was ever Delcared, so that I can re-Declare it (with a different Order clause) later in the program.

I'm sure other people have encountered this problem using embedded dynamic cursors. Any help would be greatly appreciated. Thanks.

Chris

[D.Abramowitz]

Simply DECLARE different cursors.

The following is from the same program:

c/exec sql
C+ DECLARE C3 CURSOR FOR stmt2
c/end-exec

c/exec sql
C+ DECLARE C1 CURSOR FOR stmt1
c/end-exec

c/exec sql
C+ DECLARE C2 CURSOR FOR stmt3
c/end-exec

Dave

[Guest.Visitor]

Wow, thanks for quick reply!

Well, you see, I'm using embedded SQL to load a subfile, and am allowing the user to select how it is sorted. And, as crazy as it sounds, the problem I'm up against is that there are potentially an *infinite number of sorts*.

The statement I am using is:

Select * From HIMPORTSP Where IMBATCH = ? And IMRSTAT = ? Order By Substring (IMDATA, ' + %CHAR(StrPos) + ', ' + %CHAR(Length) + ')'

But we can never know beforehand what StrPos and Length the user will choose to sort by. It could change with every Display Subfile screen. Of course, the problem would be easily solved if I could use question marks (placeholders) in the Order By clause as I did in the Where clause, but Substring, as a scalar function, will not allow it. Thus, unless I want to create who-knows-how-many cursors, I am led to try to "re-Declare" my cursor.

[Guest.Visitor]

Wow, thanks for quick reply! I am, however, having a little trouble getting my Posts/Replies to show up....

So, yes, that is the solution which logically presents itself. But I'm using embedded SQL to load a subfile and allowing the user to select how it is sorted. And, as crazy as it sounds, the problem I'm up against is that there are potentially *an infinite number of sorts*.

The statement I am using is
Select * From HIMPORTSP Where IMBATCH = ? And IMRSTAT = ? Order By Substring (IMDATA, ' + %CHAR(StrPos) + ', ' + %CHAR(Length) + ')'

so one never knows what values the user will enter for StrPos and Length. It could change on every screen. Of course, the problem would be easily solved if I could use question marks (placeholders) in the Order By clause as I did in the Where clause, but Substring, as a scalar function, won't allow it.

Thus, if I don't want to create who-knows-how-many cursors, I am led to try the "re-Declare" my (single) cursor.....

[DaveZ]

Have you tried using dynamic SQL?

Created your SQL statement in a character variable, then prepare and execute the prepared statement. Hope this make sense & helps.

// Build your statement
sqlstmt = 'SELECT * FROM HIMPORTSP WHERE IMBATCH = ' + somevalue + ' ORDER BY SUBSTR(IMDATA, ' + %CHAR(StrPos) + ', ' + %CHAR(Length) + ')';

// Declare the cursor
EXEC SQL
DECLARE theCursor CURSOR FOR theStatement;

// Prepare the statement
EXEC SQL
PREPARE theStatement FROM :sqlstmt;
// Check the SQLSTATE or SQLCODE and handle any error

// Open the cursor
EXEC SQL
OPEN theCursor;

// Process the results

// Close the cursor

Dave

[Guest.Visitor]

Hi Dave,

Yes, I am using Dynamic SQL, and that is causing the problem. You see, every time the user selects a different subfile sort/filter option, I want to re-build sqlstmt (above) and then "re-Declare" it so it can be used. The problem, again, is that the compiler won't let me compile a SQLRPGLE program where it sees that a SQL cursor is being Declared more than once.

The good news is that I discovered the solution to the problem! One simply needs to put the Declare cursor statement in a *subroutine*. Even though the Declare statement will be executed many times in the program, the compiler will see it only *once* and thus let the program compile successfully.

The takeaway is that we can rebuild and re-Declare the same cursor over and over again in the same program, with a different SQL statement each time!

The program now works great. Thanks to all those who offered help!